On Thu, 12 Apr 2012 07:55:32 -0000, "venussoftop" <venussoftop@...>
wrote:

> Okay okay I used the LIST() function, the error is gone but I do not get
> any results returned
> SELECT tSaleinvoice.*
> FROM tSaleInvoice
> WHERE tSaleInvoice.iID IN (CASE WHEN COALESCE('24315,24371', '') = ''
> THEN ((SELECT cSaleInvIDs FROM tSaleInvoiceCvrLtr WHERE iID = 993))
> ELSE ((SELECT LIST(tSI.iID) FROM tSaleInvoice tSI WHERE

tSI.iBuyerID

> = 1583 AND CAST(tSI.tDt AS DATE) = CAST('2012-03-31' AS DATE)))

END)

LIST produces a single string value based on a list of values, not a list
of values as you seem to expect. The only reason IN would ever evaluate to
true is if tSaleInvoice.iID has the same string value as produced by LIST.

Mark