我正在处理一个在"WHERE"子句中包含"CASE"语句的查询。但是SQL Server 2008在执行时出现了一些错误。有人能帮我提供正确的查询吗？以下是查询：
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
首先，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 * FROM emp_master
WHERE emp_last_name=
CASE emp_first_name
WHEN 'test' THEN 'test'
WHEN 'Mr name' THEN 'name'
END
这应该暂时解决您的问题，但我必须提醒您，这不是一个好方法：
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
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部分可以这样写：
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
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'
谢谢你的提问，实际上我在下面的查询中寻找其他东西。这可能对某人有所帮助。
SELECT DISTINCT CASE WHEN OPPORTUNITY='' THEN '(BLANK)' ELSE OPPORTUNITY END
AS OPP,LEN(OPPORTUNITY) FROM [DBO].[TBL]
上述查询是为了填写下拉列表，其中空白值显示为"（空白）"。此外，如果我们将这个值传递到sqlwhere子句中，以获得带有其他值的空白值，我不知道如何处理。最后提出了以下解决方案，这可能对某些人有所帮助。
在这里，
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 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
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 ...
这是我的解决方案
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)
我正在使用类似的方法，根据指针作为字符串和UI 中的下拉列表来过滤用户
where
u.username like '%' + isnull(@needle, '') + '%'
and 1 =
(
case
when @status = 0 then 1 -- All in uu
when @status = u.statusid then 1 -- Special status
else 0 -- otherwise reject
end
)
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';
以这种方式使用。