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