using Case in the where clase
i have a query when the @rec = yes i need to have where rcpt_date not null
when @rec = no then where rcpt_date is null
and @rec = All then where rcpt_date = '%'
i cant seem to get the case to work, I have found sample but no joy
declare @rec as varchar(3)
set @rec = 'Yes'
SELECT
Rcvr_Name, Rcpt_Date, Iniator, Arriv_date,
Carrier, Vendor, PO, [Description], QTY,
Priority, Misc_Receipt, FA_Req, partNO, TrackNO
FROM dbo.GCO_HOT_PT_LST
when @rec = no then where rcpt_date is null
and @rec = All then where rcpt_date = '%'
i cant seem to get the case to work, I have found sample but no joy
declare @rec as varchar(3)
set @rec = 'Yes'
SELECT
Rcvr_Name, Rcpt_Date, Iniator, Arriv_date,
Carrier, Vendor, PO, [Description], QTY,
Priority, Misc_Receipt, FA_Req, partNO, TrackNO
FROM dbo.GCO_HOT_PT_LST
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SELECT
Rcvr_Name, Rcpt_Date, Iniator, Arriv_date,
Carrier, Vendor, PO, [Description], QTY,
Priority, Misc_Receipt, FA_Req, partNO, TrackNO
FROM dbo.GCO_HOT_PT_LST
where
CASE
WHEN @rec = 'Yes' AND rcpt_date IS NOT NULL THEN 1
WHEN @rec = 'No' AND rcpt_date IS NULL THEN 1
When @rec = 'All' and (rcpt_date IS NULL or rcpt_date > '') then 1
ELSE 0 END = 1
GOT IT
Rcvr_Name, Rcpt_Date, Iniator, Arriv_date,
Carrier, Vendor, PO, [Description], QTY,
Priority, Misc_Receipt, FA_Req, partNO, TrackNO
FROM dbo.GCO_HOT_PT_LST
where
CASE
WHEN @rec = 'Yes' AND rcpt_date IS NOT NULL THEN 1
WHEN @rec = 'No' AND rcpt_date IS NULL THEN 1
When @rec = 'All' and (rcpt_date IS NULL or rcpt_date > '') then 1
ELSE 0 END = 1
GOT IT
ASKER