--- In firebird-support@yahoogroups.com, Mark Rotteveel <mark@...> wrote:

>
> On Thu, 12 Apr 2012 07:52:34 -0000, "venussoftop" <venussoftop@...>
> wrote:
> > Thanks Mark. I worked on it as per your suggestion and I have the
> > following now (I have replaced all variables with literals)
> > SELECT tSaleinvoice.*
> > FROM tSaleInvoice
> > WHERE tSaleInvoice.iID IN (CASE WHEN COALESCE('24315,24371', '') = ''
> > THEN ((SELECT cSaleInvIDs FROM tSaleInvoiceCvrLtr WHERE iID = 993))
> > ELSE ((SELECT tSI.iID FROM tSaleInvoice tSI WHERE tSI.iBuyerID =
> > 1583 AND CAST(tSI.tDt AS DATE) = CAST('2012-03-31' AS DATE))) END)
> >
> > I get the following error as the THEN is a list of IDs and ELSE is
> > multiple rows from it's sub-SELECT
> > ---------------------------
> > An IBPP error occurred.
> > ---------------------------
> > *** IBPP::SQLException ***
> > Context: Statement::Fetch
> > Message: isc_dsql_fetch failed.
> >
> > SQL Message : -811
> > multiple rows in singleton select
> >
> > Engine Code : 335544652
> > Engine Message :
> > multiple rows in singleton select
>
> What are you trying to achieve? That query makes no sense:
> First COALESCE('24315,24371', '') = '' will always be false, as the first
> argument of COALESCE is never NULL,
> Second:a CASE construct only returns a single scalar value, not a list of
> values as you are (I think) trying here. This is the reason you get the
> error 'multiple rows in singleton select', those selects are producing
> multiple rows, but only single value is expected here.
>

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

Please advise
Bhavbhuti