venussoftop skriver:

>
> 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)
>

But COALESCE('24315,24371', '') will ALWAYS evaluate to '24315,24371',
which is <> '', so the when expression will always be false, so the case
is completely irrelevant. You could just as well write this:

SELECT tSaleinvoice.*
FROM tSaleInvoice
WHERE tSaleInvoice.iID IN (SELECT cSaleInvIDs FROM tSaleInvoiceCvrLtr
WHERE iID = 993)

So what is your intention with COALESCE('24315,24371', '') = ''? It's
clearly worthless as it is.

>
> 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
>

The IN clause can be used with a multi-row sub select as follows (example):

WHERE tSaleInvoice.iID IN (SELECT tSI.iID FROM tSaleInvoice tSI WHERE
tSI.iBuyerID = 1583)

But the function LIST() that you're trying to use doe NOT return
multiple rows. Instead, it aggregates multiple rows into a single one
and returns a single string. The IN operator cannot look through a
string with values separated with comma (or anything else).

Does tSaleInvoiceCvrLtr.cSaleInvIDs contain a single id or a string of
multiple id:s separated with commas?

If it's a single ID, then try this:

SELECT tSaleinvoice.*
FROM tSaleInvoice
WHERE (<yourcondition>
AND tSaleInvoice.iID IN (
SELECT tSI.iID
FROM tSaleInvoice tSI
WHERE tSI.iBuyerID = 1583
AND CAST(tSI.tDt AS DATE) = CAST('2012-03-31' AS DATE)
)
)
OR (NOT <yourcondition>
AND tSaleInvoice.iID = (SELECT cSaleInvIDs FROM tSaleInvoiceCvrLtr
WHERE iID = 993)
)

If tSaleInvoiceCvrLtr.cSaleInvIDs is a string with a format like this:
123,456,78,9012,3456
Then you can't use the IN operator, nor the = operator. You need to use
string functions, e.g. position (assuming no spaces, just commas as
separator):

SELECT tSaleinvoice.*
FROM tSaleInvoice
WHERE (<yourcondition>
AND tSaleInvoice.iID IN (
SELECT tSI.iID
FROM tSaleInvoice tSI
WHERE tSI.iBuyerID = 1583
AND CAST(tSI.tDt AS DATE) = CAST('2012-03-31' AS DATE)
)
)
OR (NOT <yourcondition>
AND position(',' || tSaleInvoice.iID || ',' in
',' || (SELECT cSaleInvIDs FROM tSaleInvoiceCvrLtr WHERE iID =
993) || ',') > 0
)

POSITION(substr IN largestr) returns 0 if substr is not in largestr, but
if substr starts at 1-based position N in largestr it returns N. If
largestr is a separated list of values then you can use POSITION to see
if a value is in the list using the following template:

POSITION(sep || soughtval || sep in sep || valuelist || sep) > 0

You need to concatenate sep before and after both soughtval and
valuelist to make sure you don't get false hits, e.g. '12' is in
'341234,345,98', but ',12,' isn't in ',341234,345,98,'. Got it?

Now, just make sure you get that coalesce thingy right and you're on
your way.

Kjell

--
------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



[Non-text portions of this message have been removed]