问题描述
我正在尝试在 where 子句中为我正在处理的查询编写 case 语句.我在 Crystal 报表中导入代码，我基本上是想说变量类型"是否设置为创建"在 where 子句中针对此日期范围运行，否则针对不同的日期范围运行.它一直给我一个错误.我似乎无法确定我的语法有什么问题.帮助任何人?
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)
推荐答案
你不需要case语句
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) )
)
这留下了您在 case 语句中的无意义逻辑.@type 的两个条件具有相同的值.我认为这是一个错字.
This leaves the non-sensical logic you have in the casestatement. Both conditions have the same value for @type. I assume that is a typo.
这篇关于tsql查询中where子句中的CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！