Subject [firebird-support] RE: SQL error code = -104 Unexpected end of command, from Delphi App
Author Svein Erling Tysvær
I wrote:

>WITH MyParam(Param) as
>(SELECT CAST(:MyInput AS SMALLINT) FROM RDB$DATABASE),
>Tmp(PolNum, Transdate, Sum_Of_ArAmt) AS
>(SELECT PolNum, Transdate, sum(ArAmt)
> FROM AR
> WHERE ArAmt <> 0
> GROUP BY 1, 2)
>SELECT M.CARRIERNO, M.CARRIER, M.POLNUM, M.INVOICENO, M.TRANSNO, M.TRANSDATE,
> M.TRANSSOURCE, M.TRANSTYPE, M.PREMIUM_BEFORE, M.PREMIUM_AFTER, M.ARAMT,
> T.SUM_OF_ARAMT, M.DUEDATE, M .ACCTGPERIOD, M.ACCTGPERIODID, M.BANK, M.CHECKNO,
> M.CHECKDATE, M.ENTEREDBY, M.ENTEREDDATE, M.NOTES
>FROM AR M
>JOIN TMP T ON T.polnum = M.PolNum and T.transdate = M.transdate
>CROSS JOIN MyParam P
>WHERE (P.Param = 0)
> OR (P.Param = 1 AND T.Sum_Of_ArAmt <> 0 AND M.ARAMT <> 0)
> OR (P.Param = 2 AND T.Sum_Of_ArAmt = 0 AND M.ARAMT <> 0)
>ORDER BY M.DUEDATE, M.TRANSDATE, M.POLNUM, M.ARAMT

Sorry, I forgot that ItemIndex 0 should also include those with no ArAmt <> 0 for a particular day and Transdate, so you may want to change JOIN TMP to LEFT JOIN TMP.

Set