--- In [email protected], 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
