On Thu, 12 Apr 2012 09:16:06 -0000, "venussoftop" <venussoftop@...>
wrote:

> Thanks Mark and sorry about the logical error, here is the correction
> SELECT tSaleinvoice.*
> FROM tSaleInvoice
> WHERE tSaleInvoice.iID IN (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 cSaleInvIDs FROM tSaleInvoiceCvrLtr WHERE iID =

993))

> END)
>
> if coalesce() returns a blank it will LIST() the multiple rows, else it
> will try to use the list as stored in the field. In both the cases I

have

> tried the individual sub-selectes and they have return list as expected

but

> as a whole the query now does not return any rows nor does it raise any
> error

I repeat again:
1) The WHEN condition will always evaluate false, as
COALESCE('24315,24371', '') is never equals to ''
2) LIST() does not do what you think it does in combination with IN.
LIST() produce a single string value with comma-separated values. This
means that there is only ONE value in the IN-list, and that value is almost
certainly not equal to tSaleInvoice.iID

Furthermore if you get no results then the result of SELECT cSaleInvIDs
FROM tSaleInvoiceCvrLtr WHERE iID = 993 is apparently not equal to
tSaleInvoice.iID

Try something like:

WHERE tSaleInvoice.iID IN (
SELECT tSI.iID FROM tSaleInvoice tSI WHERE tSI.iBuyerID = 1583 AND
COALESCE('24315,24371', '') = ''
UNION
SELECT cSaleInvIDs FROM tSaleInvoiceCvrLtr WHERE iID = 993 AND
COALESCE('24315,24371', '') != ''
)

(BTW: replace COALESCE('24315,24371', '') = '' and COALESCE('24315,24371',
'') != '' with a valid condition that doesn't always evaluate to true or
false)

Here I do assume that cSaleInvIDs contains a single value, not a string of
comma-separated values.