--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> > POSITION(sep || soughtval || sep in sep || searchedvaluelist || sep) > 0
> >
> > Adding sep before and after each string makes sure you don't get false hits, like '12'
> > in '341256,234,567' where '12' appears inside '341256', but ',12,' doesn't.
>
> Good idea, Kjell, but then why not use CONTAINING as an alternative to POSITION?
>
> sep || searchedvaluelist || sep CONTAINING sep || soughtval || sep
>
> or in Bhavbhutis case:
>
> WITH TMP (cSaleInvIDs) AS
> (SELECT ',' || cSaleInvIDs || ',' FROM tSaleInvoiceCvrLtr WHERE iID = 993)
>
> SELECT tSaleinvoice.*
> FROM tSaleInvoice tSI
> WHERE (tSI.iID IN (24315,24371)
> AND EXISTS(SELECT * FROM tSaleInvoice tSI2
> WHERE tSI2.iBuyerID = 1583
> AND CAST(tSI2.tDt AS DATE) = CAST('2012-03-31' AS DATE)
> AND tSI.iID = tSI2.iID))
> OR (tSI.iID NOT IN (24315,24371)
> AND (SELECT TMP.cSaleInvIDs FROM TMP) CONTAINING ','|| tSI.iID || ',')
>
> (I've taken one of Bhavbhutis intermediate examples as a guide and assumed that iID is never NULL)
>
> Performancewise, it might actually turn out not all too bad, assuming tSaleInvoice.iBuyerID and tSaleInvoiceCvrLtr.iID are indexed, although I must admit having a list of integers in a (VAR)CHAR field complicates the SQL.
>
> HTH,
> Set
>

Thanks a lot Svein, taking your help from here and Norman on the other related thread this is my final query that I have implemented and it looks as follows

SELECT tSaleinvoice.*
FROM tSaleInvoice
WHERE (SELECT ',' || LIST(tSI.iID) || ',' FROM tSaleInvoice tSI JOIN tSaleInvoiceCvrLtr tSICL ON tSICL.iID = :vp_iID WHERE tSI.iBuyerID = tSICL.iBuyerID AND CAST(tSI.tDt AS DATE) = CAST(tSICL.tDt AS DATE) AND COALESCE(tSICL.cSaleInvIDs, '') = '')
CONTAINING ',' || TRIM(CAST(tSaleInvoice.iID AS CHAR(12))) || ','
UNION
SELECT tSaleinvoice.*
FROM tSaleInvoice
WHERE (SELECT ',' || TRIM(cSaleInvIDs) || ',' FROM tSaleInvoiceCvrLtr WHERE iID = :vp_iID)
CONTAINING ',' || TRIM(CAST(tSaleInvoice.iID AS CHAR(12))) || ','

Somehow the CASE WHEN COALESCE() was failing in Report Manager (but not in FlameRobin) and the iBuyerID + tDt logic always fired irrespective of cSaleInvIDs values but the above UNION and putting the COALESCE in the sub-selected helped me distinguish the required logic.

Thanks a lot

Kind regards
Bhavbhuti