Hi Set

Thanks for your CTE I tried to run it but I get an error on the line
that starts with TMP2(..... Please advise.

Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 5, column 1
TMP2

Your updated code is below:
WITH TMP1 (CBK, INO) AS
(SELECT CAST('A' AS VarChar(5)), CAST(2 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

On 25-11-2013 03:19 pm, Svein Erling Tysvær wrote:
> 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