--- In firebird-support@yahoogroups.com, "venussoftop" <venussoftop@...> wrote:

>
>
>
> --- In firebird-support@yahoogroups.com, Mark Rotteveel <mark@> wrote:
> >
> > On Thu, 12 Apr 2012 07:25:26 -0000, "venussoftop" <venussoftop@>
> > wrote:
> > > Hi all
> > >
> > > Can I use a CASE WHEN in the WHERE for a statement as below? The idea
> > was
> > > to either pull out records from the tSaleInvoice based on a list of PKs
> > in
> > > a separate table.field (see my other IN .... post) if the value is not
> > > present then on the same records date and iBuyerID.
> > >
> > > SELECT tSaleinvoice.*
> > > FROM tSaleInvoice
> > > WHERE CASE WHEN COALESCE (:cSaleInvIDs, '') = ''
> > > THEN iBuyerID = :iBuyerID AND CAST(tSaleInvoice.tDt AS DATE) =
> > > CAST(:tDt AS DATE)
> > > ELSE tSaleInvoice.iID IN (SELECT tSaleInvoiceCvrLtr.cSaleInvIDs
> > > FROM
> > tSaleInvoiceCvrLtr
> > > WHERE
> > > tSaleInvoiceCvrLtr.iID
> > =
> > > :vp_iID) END
> > >
> > > So
> > > SELECT tSaleInvoiceCvrLtr.*
> > > FROM tSaleInvoiceCvrLtr
> > > WHERE tSaleInvoiceCvrLtr.iID = 993
> > > should allow me to bring out many records from tSaleInvoice on two
> > > criterias
> > > 1) on tSaleInvoiceCvrLtr.cSaleInvIDs is populated or if not
> > > 2) on tSaleInvoiceCvrLtr.iBuyerID and tSaleInvoiceCvrLtr.tDt
> > >
> > > Hope I was able to explain my problem. The first SELECT is from Report
> > > Manager thus the :fieldnames
> >
> > The result of case when is a scalar value, not a boolean condition. You
> > need to do something like
> >
> > <value or field> = <case when construct>
> >
> > Mark
> >
>
>
> 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
>
> ---------------------------
> OK
> ---------------------------
>
> Please advise
>
> Thanks
> Bhavbhuti
>

Okay okay I used the LIST() function, the error is gone but I do not get any results returned
SELECT tSaleinvoice.*
FROM tSaleInvoice
WHERE tSaleInvoice.iID IN (CASE WHEN COALESCE('24315,24371', '') = '' THEN ((SELECT cSaleInvIDs FROM tSaleInvoiceCvrLtr WHERE iID = 993))
ELSE ((SELECT LIST(tSI.iID) FROM tSaleInvoice tSI WHERE tSI.iBuyerID = 1583 AND CAST(tSI.tDt AS DATE) = CAST('2012-03-31' AS DATE))) END)