> 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
