带 OR 的 where 子句中的 Case 语句 [英] Case statement in where clause w/an OR

问题描述

提前道歉，因为我觉得我可能忘记/遗漏了一些明显的东西.开始;我在我的 WHERE 子句中使用 case 语句，以下工作正常:

Apologies in advance since I feel like I'm probably forgetting/missing something obvious on this one. Here goes; I'm using a case statement in my WHERE clause, the below works fine:

WHERE r.[SomeCol] = @SomeColVal
AND SomeOtherCol =  
(
CASE WHEN (@Year = 0 AND @Period = 0) THEN
@SomeVal
     WHEN...
...
     ELSE
@SomeVal
END 

我的问题"是我想在我的 ELSE 块中添加一个额外的 OR 子句......像这样:

My "issue" is that I want to add an additional OR clause to my ELSE block..something like this:

WHERE r.[SomeCol] = @SomeColVal
AND SomeOtherCol =  
(
CASE WHEN (@Year = 0 AND @Period = 0) THEN
@SomeVal
     WHEN...
...
     ELSE
@SomeVal OR @SomeVal - 1
END 

当然，这会引发此错误:关键字OR"附近的语法不正确.在 ELSE 语句中

Naturally, this throws this error: Incorrect syntax near the keyword 'OR'. within the ELSE statement

因此我的问题是……我可以用来完成此任务的正确/替代逻辑是什么?
提前致谢

Hence my question...what is the correct/alternate logic I can use to accomplish this?
Thank you in advance

推荐答案

CASE 是一个返回一个值的表达式.您可以在两个表达式之间执行 OR，而不是针对单个 CASE 表达式进行测试，唯一的区别是 else 子句.(使用 IN 作为编写 SomeOtherCol = ... OR SomeOtherCol = 的快捷方式)你可以这样做:

CASE is an expression that returns one value. Instead of testing against the single CASE expresssion, you could do an OR between two, that only difference is the else clause. (Using IN as a shortcut for writing SomeOtherCol = ... OR SomeOtherCol =) You could do:

WHERE r.[SomeCol] = @SomeColVal
AND SomeOtherCol in   
    (CASE WHEN (@Year = 0 AND @Period = 0) THEN
        @SomeVal
        CASE WHEN...
            ...
        CASE ELSE
        @SomeVal END,
    CASE WHEN (@Year = 0 AND @Period = 0) THEN
        @SomeVal
        CASE WHEN...
            ...
        CASE ELSE
        @SomeVal - 1 END)

我的猜测是这比您需要的逻辑更多，如果知道您的具体情况，可以编写更简单、更清晰的 else 语句.

My guess is this has more logic than you need, and if the specifics of your situation were known a much simpler and clearer else statement could be written.

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