tsql查询中where子句中的CASE语句 [英] CASE statement in where clause in tsql query

问题描述

我正在尝试在 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语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！