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.