Skip to content
Advertisement

use sql case statement in a adoquery of delphi

so i want to calculate a sum of a field using adoquery and case statement 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 gest an error message :

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

can any one help me please

Advertisement

Answer

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');
//...
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement