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

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

Sorry logic correction but no results are returned
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)