Devart Forums

Posted: **Sun 18 Aug 2013 11:25**

Hi All
Don't work this sql script TMyquery in design-time
In Demo too don't work..
Work in navicat sql and sqlfiddle.com
http://sqlfiddle.com/#!2/9e2a3/3

Code: Select all

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;

Need Help

Posted: **Tue 20 Aug 2013 08:03**

Hello,

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/ ... ments.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:

Code: Select all

MyQuery.Open;
ShowMessage(MyQuery.Fields[0].AsString);

Devart Forums

Error 42000 select @sql TMyquery

Error 42000 select @sql TMyquery

Re: Error 42000 select @sql TMyquery