Hi Bhavbhuti!
You say 'logically IF vp_cBk <> '' OR vp_iNo <> 0' and then you use
'tCartridge.CBK = ?vp_cBk AND tCartridge.INO = ?vp_iNo', so I don't know
whether you want AND or OR (I guess you'll modify the query as appropriate).
IIF is a function that returns either out of two values and cannot be used to
return further refinements to your query! Rather, try something like:
WITH TMP1 (CBK, INO) AS
(SELECT CAST(?vp_cBk AS VarChar(<whatever>)), CAST(?vp_iNo AS Integer)
FROM RDB$DATABASE)
TMP2(dValueFrom, dValueTo) AS
(SELECT min(iif(US1.CKEY = 'From', US1.dValue, null)),
min(iif(US1.CKEY = 'To', US1.dValue, null))
FROM uSettings US1
JOIN uSettings US2
ON US2.iID = US1.iPID
WHERE US2.CKEY = 'Current Year'
AND US1.CKEY IN ('From', 'To')
SELECT c.IID, c.IPID, c.IRECTYPE, c.CBK, c.INO, c.TDT, c.CPARTY,
c.CCARTRIDGEMAKE,
c.CCARTRIDGENO, c.IMAXREFILLS, c.IEXTREFILLS
FROM tCartridge C
CROSS JOIN TMP1 T1
CROSS JOIN TMP2 T2
WHERE (c.CBK = T1.CBK
AND c.INO = T1.INO)
OR (T1.CBK IS NULL
AND T1.INO IS NULL
AND c.tDT BETWEEN T1.dValueFrom AND T1.dValueTo)
ORDER BY c.iNo, c.cBk, c.tDt
As Mark has already said, it may be slower than you'd ideally want - at least
if there are many records each year.
HTH,
Set
