On Thu, 12 Apr 2012 07:25:26 -0000, "venussoftop" <[email protected]> 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
