|Subject||Re: Is such a CASE WHEN usage allowed?|
--- In email@example.com, Mark Rotteveel <mark@...> wrote:
>Thanks Mark I see the point now. Is there a function to convert the string as returned by LIST() or in my field, because both are strings, one automatically created and another manually created
> On Thu, 12 Apr 2012 07:55:32 -0000, "venussoftop" <venussoftop@...>
> > 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
> > = 1583 AND CAST(tSI.tDt AS DATE) = CAST('2012-03-31' AS DATE)))
> 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.