I am working with a query which contains "CASE" statement within "WHERE" clause. But SQL Server 2008 is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:

SELECT tl.storenum 'Store #', co.ccnum 'FuelFirst Card #', co.dtentered 'Date Entered', CASE st.reasonid WHEN 1 THEN 'Active' WHEN 2 THEN 'Not Active' WHEN 0 THEN st.ccstatustypename ELSE 'Unknown' END 'Status', CASE st.ccstatustypename WHEN 'Active' THEN ' ' WHEN 'Not Active' THEN ' ' ELSE st.ccstatustypename END 'Reason', UPPER(REPLACE(REPLACE(co.personentered,'RT\\\\',''),'RACETRAC\\\\','')) 'Person Entered', co.comments 'Comments or Notes'
FROM comments co INNER JOIN cards cc ON co.ccnum=cc.ccnum INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid LEFT JOIN stores s ON s.StoreNum = tl.StoreNum
WHERE CASE LEN('TestPerson') WHEN 0 THEN co.personentered = co.personentered ELSE co.personentered LIKE '%TestPerson' END AND cc.ccnum = CASE LEN('TestFFNum') WHEN 0 THEN cc.ccnum ELSE 'TestFFNum' END AND CASE LEN('2011-01-09 11:56:29.327') WHEN 0 THEN co.DTEntered = co.DTEntered ELSE CASE LEN('2012-01-09 11:56:29.327') WHEN 0 THEN co.DTEntered >= '2011-01-09 11:56:29.327' ELSE co.DTEntered BETWEEN '2011-01-09 11:56:29.327' AND '2012-01-09 11:56:29.327' END END AND tl.storenum < 699
ORDER BY tl.StoreNum

1

13 Answers

First off, the CASE statement must be part of the expression, not the expression itself.

In other words, you can have:

WHERE co.DTEntered = CASE WHEN LEN('blah') = 0 THEN co.DTEntered ELSE '2011-01-01' END 

But it won't work the way you have written them eg:

WHERE CASE LEN('TestPerson') WHEN 0 THEN co.personentered = co.personentered ELSE co.personentered LIKE '%TestPerson' END 

You may have better luck using combined OR statements like this:

WHERE ( (LEN('TestPerson') = 0 AND co.personentered = co.personentered ) OR (LEN('TestPerson') <> 0 AND co.personentered LIKE '%TestPerson') )

Although, either way I'm not sure how great of a query plan you'll get. These types of shenanigans in a WHERE clause will often prevent the query optimizer from utilizing indexes.

3

Try the following:

SELECT * FROM emp_master
WHERE emp_last_name=
CASE emp_first_name WHEN 'test' THEN 'test' WHEN 'Mr name' THEN 'name'
END

This should solve your problem for the time being but I must remind you it isn't a good approach :

WHERE CASE LEN('TestPerson') WHEN 0 THEN CASE WHEN co.personentered = co.personentered THEN 1 ELSE 0 END ELSE CASE WHEN co.personentered LIKE '%TestPerson' THEN 1 ELSE 0 END END = 1 AND cc.ccnum = CASE LEN('TestFFNum') WHEN 0 THEN cc.ccnum ELSE 'TestFFNum' END AND CASE LEN('2011-01-09 11:56:29.327') WHEN 0 THEN CASE WHEN co.DTEntered = co.DTEntered THEN 1 ELSE 0 END ELSE CASE LEN('2012-01-09 11:56:29.327') WHEN 0 THEN CASE WHEN co.DTEntered >= '2011-01-09 11:56:29.327' THEN 1 ELSE 0 END ELSE CASE WHEN co.DTEntered BETWEEN '2011-01-09 11:56:29.327' AND '2012-01-09 11:56:29.327' THEN 1 ELSE 0 END END END = 1 AND tl.storenum < 699 

0

I think that the beginning of your query should look like that:

SELECT tl.storenum [Store #], co.ccnum [FuelFirst Card #], co.dtentered [Date Entered], CASE st.reasonid WHEN 1 THEN 'Active' WHEN 2 THEN 'Not Active' WHEN 0 THEN st.ccstatustypename ELSE 'Unknown' END [Status], CASE st.ccstatustypename WHEN 'Active' THEN ' ' WHEN 'Not Active' THEN ' ' ELSE st.ccstatustypename END [Reason], UPPER(REPLACE(REPLACE(co.personentered,'RT\\\\',''),'RACETRAC\\\\','')) [Person Entered], co.comments [Comments or Notes]
FROM comments co INNER JOIN cards cc ON co.ccnum=cc.ccnum INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid LEFT JOIN stores s ON s.StoreNum = tl.StoreNum
WHERE CASE WHEN (LEN([TestPerson]) = 0 AND co.personentered = co.personentered) OR (LEN([TestPerson]) <> 0 AND co.personentered LIKE '%'+TestPerson) THEN 1 ELSE 0 END = 1 AND 

BUT

what is in the tail is completely not understandable

There WHERE part could be written like this:

WHERE (LEN('TestPerson') <> 0 OR co.personentered = co.personentered) AND (LEN('TestPerson') = 0 OR co.personentered LIKE '%TestPerson') AND (cc.ccnum = CASE LEN('TestFFNum') WHEN 0 THEN cc.ccnum ELSE 'TestFFNum' END ) AND (LEN('2011-01-09 11:56:29.327') <> 0 OR co.DTEntered = co.DTEntered ) AND ((LEN('2011-01-09 11:56:29.327') = 0 AND LEN('2012-01-09 11:56:29.327') <> 0) OR co.DTEntered >= '2011-01-09 11:56:29.327' ) AND ((LEN('2011-01-09 11:56:29.327') = 0 AND LEN('2012-01-09 11:56:29.327') = 0) OR co.DTEntered BETWEEN '2011-01-09 11:56:29.327' AND '2012-01-09 11:56:29.327' ) AND tl.storenum < 699 

Thanks for this question, actually I am looking for something else which is in below query. this may helps someone.

 SELECT DISTINCT CASE WHEN OPPORTUNITY='' THEN '(BLANK)' ELSE OPPORTUNITY END AS OPP,LEN(OPPORTUNITY) FROM [DBO].[TBL]

above query is to fill in dropdown which blank values shows as "(blank)". Also if we pass this value into sql where clause to get blank values with other values I don't know how to handle that. And finally came up with below solution this may helps to somebody.

here it is ,

 DECLARE @OPP TABLE (OPP VARCHAR(100)) INSERT INTO @OPP VALUES('(BLANK)'),('UNFUNDED'),('FUNDED/NOT COMMITTED')
SELECT DISTINCT [OPPORTUNITY]
FROM [DBO].[TBL] WHERE ( CASE WHEN OPPORTUNITY ='' THEN '(BLANK)' ELSE OPPORTUNITY END IN (SELECT OPP FROM @OPP))
ORDER BY 1 

 select
d.DISTNAME,e.BLKNAME,a.childid,a.studyingclass
from Tbl_AdmissionRegister a
inner join District_master b on a.Schooid=b.Schooid
where
case when len('3601')=4 then c.distcd when len('3601')=6 then c.blkcd when len('3601')=11 then c.schcd end = '3601'

1

You could also try like below eg. to show only Outbound Shipments

 SELECT shp_awb_no,shpr_ctry_cd, recvr_ctry_cd, CASE WHEN shpr_ctry_cd = record_ctry_cd THEN "O" ELSE "I" END AS route FROM shipment_details WHERE record_ctry_cd = "JP" AND "O" = CASE WHEN shpr_ctry_cd = record_ctry_cd THEN "O" ELSE "I" END

here is my solution

AND CLI.PE_NOM Like '%' + ISNULL(@NomClient, CLI.PE_NOM) + '%'

Regads Davy

This works

declare @v int=A
select * from Table_Name where XYZ=202
and
dbkey=(case @v when A then 'Some Value 1'
else 'Some Value 2'
end)

CASE LEN('TestPerson') WHEN 0 THEN co.personentered = co.personentered ELSE co.personentered LIKE '%TestPerson'

Try the following:

... and ( (LEN('TestPerson') = 0 and co.personentered = co.personentered) or (LEN('TestPerson') <> 0 and co.personentered LIKE '%TestPerson') ) and ...

select TUM1.userid,TUM1.first_name + ' ' +TUM1.last_name as NAME,tum1.Business_Title,TUM1.manager_id,tum2.First_Name + ' ' + tum2.Last_Name as [MANAGER NAME],TUM1.project,TUM1.project_code,TUM1.rcc_code,TUM1.department,TCM.Company_Name,
case
when tum1.Gender_ID=1 then 'male'
else 'female'
end 'GENDER'
,tum1.Band as BAND,
case when tum1.Inactive=0 then 'STILL IN COMPANY'
else 'LEFT COMPANY'
end 'ACTIVE/INACTIVE'
from tbl_user_master TUM1
join tbl_Company_Master TCM on TCM.Company_Code=TUM1.Company_Code
join tbl_User_Master TUM2 on TUM1.Manager_ID=TUM2.UserID
where tum1.UserID in ('54545414')

SELECT * from TABLE WHERE 1 = CASE when TABLE.col = 100 then 1 when TABLE.col = 200 then 2 else 3 END and TABLE.col2 = 'myname';

Use in this way.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy