[SOLVED] Use SQL case expression in a adoquery of delphi

Use SQL case expression in a adoquery of delphi

I want to calculate a sum of a field using adoquery and case expression in Delphi.

The code I use is this:

DataModule2.ADOQuery1_630.sql.clear;
     DataModule2.ADOQuery1_630.sql.Add('select STATUT,case  when month(DATE_PAI)=1 then sum(MT) else 0 end  AS m');
     DataModule2.ADOQuery1_630.sql.Add('from table');
     DataModule2.ADOQuery1_630.sql.Add('where  STATUT in( :dd ,:df) and TYPE_QUIT = :l ');
     DataModule2.ADOQuery1_630.sql.Add('group by STATUT');
     DataModule2.ADOQuery1_630.Parameters.ParamByName('dd').Value:=pm;
     DataModule2.ADOQuery1_630.Parameters.ParamByName('df').Value:=pp;
     DataModule2.ADOQuery1_630.Parameters.ParamByName('l').Value:=typeQ;
     DataModule2.ADOQuery1_630.prepared := true;
     DataModule2.ADOQuery1_630.open;

But I get an error message:

syntax error operator absent in the expression case when month(DATE_PAI)=1 then sum(MT) else 0 end

Can anyone help me please

Solution

Try to do :

(group SELECT and FROM in the same DataModule2.ADOQuery1_630.sql.Add())

DataModule2.ADOQuery1_630.sql.clear;
DataModule2.ADOQuery1_630.sql.Add('SELECT STATUT, SUM(case when month(DATE_PAI)=1 then MT ELSE 0 END) AS m FROM table');
DataModule2.ADOQuery1_630.sql.Add('WHERE STATUT in(:dd,:df) and TYPE_QUIT=:l');
DataModule2.ADOQuery1_630.sql.Add('GROUP BY STATUT');
//...