问题描述
我正在使用在"WHERE"子句中包含"CASE"语句的查询.但是,SQL Server 2008在执行它时会出现一些错误.有人可以帮助我提供正确的查询吗?这是查询:
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
推荐答案
首先,CASE
语句必须是表达式的 part ,而不是表达式本身.
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
使用如下组合的OR语句可能会带来更好的运气:
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')
)
尽管,无论哪种方式,我都不确定您将获得多大的查询计划. WHERE
子句中的这些类型的恶作剧通常会阻止查询优化器利用索引.
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.
这篇关于“案例"指的是"WHERE"中的声明SQL Server 2008中的子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!