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
