>Message received. I'll go away
Hi Homer, hope you haven’t gone completely away just yet. Your question (and
problem) may be off-topic for this list, but I'll try to keep my answer more or
less on-topic.
Your SQL puzzles me, I've never before seen 'select distinct Sum(' in a
subselect, and cannot help thinking that DISTINCT at best is irrelevant since
the subselect has to return 0 or 1 row.
I don't know your Firebird version, but if it is fairly recent, you could try
changing to something like:
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
(I've selected the parameter from RDB$DATABASE since RDB$DATABASE contains only
one row)
Then, you wouldn't have to modify your SQL when cxRGs ItemIndex changes, just
set the input parameter to the value of cxRG.ItemIndex and reopen your query,
no need to change the query (the only differences between this and your
original queries, should be that Sum_Of_Aramt is included also for the plain
select and that my query above will not work with versions prior to Firebird
2.0 or similar).
Now, I don't know whether this is of any help with your real problem or not.
With such a query and IBO, I would probably have had to specify either KeyLinks
or InsertSQL/EditSQL to make such a query insertable/updateable, but I've no
knowledge of IBDAC. Neither do I have any experience with queries complaining
when I try to close them.
One off-topic Delphi hint though: Sometimes I've found that deleting a
component and creating a new one fixes strange problems, I guess it is due to
some kind of property I've accidentally set and forgotten all about.
Set
