Need help Mydac TMyquery not work this script in navicat and sqlfiddle work but tmyquery not work
SET @@group_concat_max_len = 32000;
SELECT
group_concat(concat('sum(ifnull(if(s.id=',s.id,',m.qty,0),0))`',s.sizes,'`'))eval,
group_concat(concat('i.`',s.sizes,'`'))list
INTO @eval, @list
from(
SELECT DISTINCT s.id, s.sizes
FROM property p
JOIN size_goods s ON s.id=p.id_sizes
WHERE p.id_goods IN (6,7,8)
ORDER BY s.id
)s;
SELECT group_concat(p.id)
INTO @where
FROM property p
WHERE p.id_goods IN (6,7,8)
;
SET @sql=concat_ws(' ',
'select g.id, g.name, g.model,',
@list,',i.Total,i.price,i.cargo_payment,i.Cost from(select p.id_goods id,',@eval,
',sum(ifnull(m.qty,0))Total',
',ifnull(sum(price*qty)/sum(qty),0)price',
',ifnull(sum(cargo_payment*qty)/sum(qty),0)cargo_payment',
',sum(ifnull(m.qty*(m.price+m.cargo_payment),0))Cost',
'from property p',
'join size_goods s on s.id=p.id_sizes',
'left join (',
'select id_property, id_actions, qty*(3-2*id_actions)qty, price, cargo_payment from moves',
') m on m.id_property=p.id',
'where p.id in (',@where,')',
'group by p.id_goods',
')i left join goods g on g.id=i.id;'
);
SELECT @sql;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
error is 42000 sql syntax nearest
The point is that MySQL cannot prepare such SQL statement. You cannot use API calls for preparing the PREPARE, EXECUTE, or DEALLOCATE PREPARE statements. For more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html . To avoid the problem, you should call the TMyQuery.Open method without calling the TMyQuery.Prepare method. To get the result value, you can use the AsString property. Here is a code example: