I have a small project with PgConnection, PgQuery, DataseProvider, ClientDataset components.
The ClientDataset's SQL text is the following:
- 'select d.DOCID, d.DOCDATE, DOCAHD.DUEDATE, d.DOCNO, DOCAHD.DOCVALUE, FZ.FZNAME, FZ.FZCODE,'+
'coalesce(sum(dlfin.DFLN1VAL),0) DOCPAID, DOCAHD.DOCVALUE-coalesce(sum(dlfin.DFLN1VAL),0) REST '+
'from DOCHD d '+
'join SOLDDOCAV s on d.DOCID=s.DOCID '+
'join DOCAHD DOCAHD on s.DOCID=DOCAHD.DOCID '+
'join FZ on DOCAHD.FZID=FZ.FZID '+
'join DEV dev on dev.DEVID=d.DEVID '+
'join TDOC t on t.TDOCID=d.TDOCID '+
'left outer join DOCFLN1 dlfin on dlfin.DREFIDFZ=d.DOCID and dlfin.DOCID in (select d1.DOCID from DOCHD d1 Where d1.DOCDATE<= :datastop ) '+
'left outer join DOCFHD dhfin on dhfin.DOCID=dlfin.DOCID '+
'where dev.ISDEFAULT=1 and s.YM= :datastart '+
'group by d.DOCID, d.DOCDATE, DOCAHD.DUEDATE, d.DOCNO, DOCAHD.DOCVALUE, FZ.FZNAME, FZ.FZCODE '+
'having abs(DOCAHD.DOCVALUE-(select coalesce(sum(DFLN1VAL),0) from DOCFLN1 Where DREFIDFZ=d.DOCID and DOCID in (select DOCID from DOCHD Where DOCDATE< :datastop )))>0.009';
Before opening the ClientDataset I assign values to the parameters:
Code: Select all
for i:=0 to Params.Count-1 do begin
Params[i].ParamType:=ptInput;
Params[i].DataType:=ftInteger;
if Params[i].Name='datastart' then Params[i].AsInteger:=20131201;
if Params[i].Name='datastop' then Params[i].AsInteger:=20131231;
end;
Strange is, if I remove the last parameter (the one from the having clause), it opens without any error.
Any help would be appreciated.
Thank you,
Radu B.