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'); //...