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

Please advise

Thanks and regards
Bhavbhuti