SQLServer2008中“ WHERE”子句中的“ CASE”语句

我正在处理一个查询，它包含“ WHERE”子句中的“ CASE”语句。但是 SQLServer2008在执行时出现了一些错误。有人能帮我解答正确的问题吗？这里有一个问题:

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
1156260 次浏览
小开

首先，CASE语句必须是表达式的 一部分，而不是表达式本身。

换句话说，你可以拥有:

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

但它不会像你写的那样起作用。例如:

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

使用这样的组合 OR 语句可能会有更好的运气:

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

不过，不管怎样，我不确定你的查询计划会有多好。WHERE子句中的这些类型的诡计通常会阻止查询优化器利用索引。

小开

我认为你的问题的开头应该是这样的:

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

但是

尾巴里的东西是完全不可理解的

小开

这应该能暂时解决你的问题，但我必须提醒你，这不是一个好办法:

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
小开 
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 emp_master
WHERE emp_last_name=
CASE emp_first_name
WHEN 'test'    THEN 'test'
WHEN 'Mr name' THEN 'name'
END
小开 
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';

这样使用。

小开

WHERE部分可以这样写:

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
小开

这是我的解决办法

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

注意 戴维

小开

这个管用

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)
小开

您也可以尝试像下面的例子，以显示只出口货物

   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
小开

谢谢这个问题，实际上我正在寻找其他东西，在下面的查询。这可能会有所帮助的人。

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

上面的查询是填写下拉列表中的空白值显示为“(空白)”。另外，如果我们将这个值传递给 sql where 子句，以获得其他值的空值，我不知道如何处理这个问题。最后提出了以下的解决方案，这可能对某些人有所帮助。

就是这个,

 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
小开 
CASE LEN('TestPerson')
WHEN 0 THEN co.personentered  = co.personentered ELSE co.personentered LIKE '%TestPerson'

试试以下方法:

... and (
(LEN('TestPerson') = 0 and co.personentered  = co.personentered) or
(LEN('TestPerson') <> 0 and co.personentered LIKE '%TestPerson') ) and ...
小开 
 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'

提交答案