Subject Re: IN using a string from another table field
Author venussoftop
--- In, Norman Dunbar <Norman@...> wrote:
> Convert your id to a string with leading and trailing commas.
> Retrieve the rows required (where tSaleInvoiceCvrLtr.iID = 993).
> For each row, wrap the list of ids in a leading and trailing comma.
> Check if your string ",id," is contained within the retrieved string
> ",id,id,id,id,id,"

Thanks a million Norma for the idea, this is what I have now done
SELECT tSaleinvoice.*
FROM tSaleInvoice
WHERE (CASE WHEN COALESCE('24315,24371', '') = ''
THEN ((SELECT ',' || LIST(tSI.iID) || ',' FROM tSaleInvoice tSI WHERE tSI.iBuyerID = 1583 AND CAST(tSI.tDt AS DATE) = CAST('2012-03-31' AS DATE)))
ELSE ((SELECT ',' || TRIM(cSaleInvIDs) || ',' FROM tSaleInvoiceCvrLtr WHERE iID = 993)) END)
CONTAINING ',' || TRIM(CAST(tSaleInvoice.iID AS CHAR(10))) || ','

and it seems to return the values I am expecting!!!

Kind regards