在 SQL Server 的 WHERE 子句中使用带有 CASE 语句的“IN"谓词 [英] Using an 'IN' predicate with CASE statements in the WHERE clause for SQL Server

问题描述

这是一个非常有效的 SQL 查询:

Here's a SQL query that works absolutely fine:

DECLARE @IncludePTO AS CHAR
SET @IncludePTO = 'Y'

SELECT     
   HQCO.HQCo, HQCO.Name, PREH.PRDept, PREH.Craft, HRRM.PositionCode, PRTH.Employee, 
   PREH.LastName, PREH.FirstName, SUM(PRTH.Hours) AS Hrs
FROM PREH 
INNER JOIN HRRM ON PREH.PRCo = HRRM.HRCo AND PREH.Employee = HRRM.HRRef 
INNER JOIN PRTH ON PREH.PRCo = PRTH.PRCo AND PREH.Employee = PRTH.Employee 
INNER JOIN HQCO ON PRTH.PRCo = HQCO.HQCo
WHERE     
    (dbo.PRTH.PREndDate BETWEEN '1/1/2012 00:00:00' AND '1/1/2013 00:00:00')
    AND (PRGroup = 2)
    AND dbo.PRTH.EarnCode IN (1,3,8)
GROUP BY 
    HQCO.HQCo, HQCO.Name, PREH.PRDept, PREH.Craft, 
    HRRM.PositionCode, PRTH.Employee, PREH.LastName, PREH.FirstName

但是，我正在尝试在 WHERE 子句中使用IN"谓词和 CASE WHEN 结合.看看下面修改后的查询:

But, I am trying to use an 'IN' predicate coupled with the CASE WHEN in the WHERE clause.. Have a look at the modified query below:

DECLARE @IncludePTO AS CHAR
SET @IncludePTO = 'Y'

SELECT     
   HQCO.HQCo, HQCO.Name, PREH.PRDept, PREH.Craft, HRRM.PositionCode, PRTH.Employee, 
   PREH.LastName, PREH.FirstName, SUM(PRTH.Hours) AS Hrs
FROM PREH 
INNER JOIN HRRM ON PREH.PRCo = HRRM.HRCo AND PREH.Employee = HRRM.HRRef 
INNER JOIN PRTH ON PREH.PRCo = PRTH.PRCo AND PREH.Employee = PRTH.Employee 
INNER JOIN HQCO ON PRTH.PRCo = HQCO.HQCo
WHERE     
    (dbo.PRTH.PREndDate BETWEEN '1/1/2012 00:00:00' AND '1/1/2013 00:00:00')
    AND (PRTH.PRGroup = 2)
    AND 
       CASE WHEN @IncludePTO = 'Y' THEN dbo.PRTH.EarnCode IN (1,2,3,8,100,110,120,115)
            ELSE dbo.PRTH.EarnCode IN (1,2,3,8)
       END
GROUP BY 
    HQCO.HQCo, HQCO.Name, PREH.PRDept, PREH.Craft, HRRM.PositionCode, PRTH.Employee, 
    PREH.LastName, PREH.FirstName

它似乎不起作用，并不断给我一个语法错误:

It doesn't seem to work and keeps giving me a syntax error:

消息 156，级别 15，状态 1，第 15 行
关键字IN"附近的语法不正确.

在指出我遗漏的方面有什么帮助或指导吗?我真的很感激任何帮助/提示..非常感谢，

Any help or guidance in pointing out what I'm missing? I would really appreciate any help/tips.. Thanks much,

Pranav

推荐答案

你说得对.语法是不允许的.你可以试试:

You are correct. The syntax is not allowed. You can try:

((@IncludePTO = 'Y' and dbo.PRTH.EarnCode IN (1,2,3,8,100,110,120,115)) or
 (@IncludePTO <> 'Y' and dbo.PRTH.EarnCode IN (1,2,3,8))
)

这可以简化为:

(bo.PRTH.EarnCode IN (1,2,3,8) or (@IncludePTO <> 'Y' and dbo.PRTH.EarnCode IN (100,110,120,115))
)

这假设@IncludePTO 永远不会为 NULL.如果是这样，那么这将成为测试的一部分.

This assumes that @IncludePTO is never NULL. If so, then that would have to be part of the test.

如果你真的想要在 where 子句中使用 case 语句，你可以这样做:

If you really want case statement in the where clause, you can do:

(CASE WHEN @IncludePTO = 'Y' and dbo.PRTH.EarnCode IN (1,2,3,8,100,110,120,115)
      then 'true'
      when @IncludePTO <> 'Y' and dbo.PRTH.EarnCode IN (1,2,3,8)
      then 'true'
      else 'false'
 end) = 'true'

这篇关于在 SQL Server 的 WHERE 子句中使用带有 CASE 语句的“IN"谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！