--- In [email protected], 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
