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.ri...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 [Non-text portions of this message have been removed]