问题描述
我试图在我正在处理的查询的where子句中写一个case语句。我正在导入水晶报表中的代码,我基本上试图说如果变量'type'设置为'创建'运行在这个日期范围在where子句else为不同的日期范围运行。它不断给我一个错误。我似乎无法识别我的语法错误在这里。
I'm trying to write a case statement in the where clause for a query I am working on. I am importing the code in Crystal reports and I am basically trying to say if the variable 'type' is set to 'create' run for this date range in the where clause else run for a different date range. It keeps giving me an error. I can't seem to identify what is wrong with my syntax here. Help anyone?
DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME
DECLARE @type VARCHAR(20)
SET @Date1 = '2010-1-1'
SET @Date2 = '2010-2-1'
SET @type = '{?DateType}'
select *
from filled
WHERE
(CASE WHEN @type = 'create' THEN
filled.CREATEDON >= @Date1
AND filled.CREATEDON < DATEADD(d, +1, @Date2)
WHEN @type <> 'create' THEN
filled.datefilled >= @Date1
AND filled.datefilled < DATEADD(d, +1, @Date2)
END)
推荐答案
您不需要案例
语句
WHERE ( (@type = 'create' and filled.CREATEDON >= @Date1 AND filled.CREATEDON < DATEADD(d, +1, @Date2) ) or
(@type <> 'create' and filled.datefilled >= @Date1 AND filled.datefilled < DATEADD(d, +1, @Date2) )
)
$ b b
这会在 case
语句中留下非感性逻辑。两个条件对 @type
具有相同的值。我假设这是一个错字。
This leaves the non-sensical logic you have in the case
statement. Both conditions have the same value for @type
. I assume that is a typo.
这篇关于CASE语句中的where子句在tsql中查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!