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

问题描述

我试图在我正在处理的查询的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 casestatement. Both conditions have the same value for @type. I assume that is a typo.

这篇关于CASE语句中的where子句在tsql中查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！