如何在存储过程的SQL Select语句中实现嵌套的CASE语句?

huangapple go评论54阅读模式
英文:

How to implement nested CASE statements in a SQL Select of a stored procedure?

问题

CASE 
    WHEN @Mode = 7 
        THEN 
            CASE 
                WHEN t.TranDate > @Due 
                    THEN t.ChgAmount 
            END AS CurrentCharges
        ELSE
            CASE 
                WHEN t.Descr = '-- Prior Balance --'  
                    THEN ''
                    ELSE t.ChgAmount 
            END 
END AS ChgAmount,
CASE 
    WHEN t.Descr = '-- Prior Balance --' 
        THEN '' 
        ELSE t.PayAmount 
END AS PayAmount,
英文:

I have a SELECT statement from a temp table in a stored procedure that selects these two columns:

DECLARE @Mode INT

CASE 
    WHEN t.Descr = '-- Prior Balance --' 
        THEN '' 
        ELSE t.ChgAmount 
END AS ChgAmount, 
CASE 
    WHEN t.Descr = '-- Prior Balance --' 
        THEN '' 
        ELSE t.PayAmount 
END AS PayAmount,

I want to conditionally return those two columns differently depending on the value of @Mode, specifically if it is equal to 7.

I'm getting confused about the levels of nesting that I need and the formatting of doing this.

So far I have tried something like this:

CASE 
    WHEN @Mode = 7 
        THEN 
            CASE 
                WHEN t.TranDate > @Due 
                    THEN t.ChgAmount 
            END	
END AS CurrentCharges,
CASE 
    WHEN t.Descr = '-- Prior Balance --'  
        THEN '' 
        ELSE  t.ChgAmount 
END AS ChgAmount,
CASE 
    WHEN t.Descr = '-- Prior Balance --' 
        THEN '' 
        ELSE t.PayAmount 
END AS PayAmount,  

The above SELECT might work, but it would stil return the extra column. How should I nest the other, original, CASE statement for the ChgAmount?

答案1

得分: 1

首先,让我们以抽象的术语澄清以下伪代码:

如果 X 那么
    情况 1
    如果 Y 那么
        情况 2
    否则
        情况 3
    结束如果
否则
    情况 4
结束如果
  • 情况 1 等同于“X 为真”
  • 情况 2 等同于“X 为真且 Y 为真”
  • 情况 3 等同于“X 为真且 Y 为假”
  • 情况 4 等同于“X 为假”

此外,让我们澄清 case-when 的条件在逻辑上与我们上面呈现的 if-then 伪代码非常相似,因此,您可以应用复合条件而不是嵌套 case-when,但是,如果您喜欢,也可以实现嵌套 case-when 条件,这是一种风格问题。

因此,您需要通过自问以下问题来制定要应用的逻辑:

  • 我的不同情况中是否需要相同数量的字段?(如果不需要,那么您可能需要在不同情况下编写不同的查询)
  • 如果 @Mode 为 7,我的字段有哪些情况?
  • 如果 @Mode 不为 7,我的字段有哪些情况?
  • 我如何将上述情况中的条件合并成一致的(复合)条件,而无需嵌套?

如果您将这些问题的答案作为对此问题的编辑,那么我们将能够更恰当地回答您的问题,而不仅仅是我在这个答案中使用的一般术语,并且我们还可以为您提供代码。然而,如果您仔细考虑了这一点,您也可能能够以非嵌套的方式实现这一点。如果您能理解到能够将其实现为非嵌套的方式,那么您也可以将该实现转化为嵌套实现。

英文:

First, let's clarify in abstract terms that in the following pseudo-code

If X then
    Case 1
    If Y then
        Case 2
    Else
        Case 3
    End If
Else
    Case 4
End If
  • Case 1 is equivalent to "X is true"
  • Case 2 is equivalent to "X is true and Y is true"
  • Case 3 is equivalent to "X is true and Y is false"
  • Case 4 is equivalent to "X is false"

Furthermore, let's clarify that case-when criterias are logically very similar to our pseudo-code presented above of if-then, hence, you can apply composite criteria instead of nesting case-when if you prefer that, but also, you can implement nested case-when criterias, it's a matter of style.

As a result, you will need to formulate the logic you want to apply, by asking yourself the following questions:

  • do I need the same number of fields in my different cases? (if not, then you will probably need to write different queries in different cases)
  • what cases do I have for my fields if @Mode is 7?
  • what cases do I have for my fields if @Mode is not 7?
  • how can I merge my criterias in the points above into coherent (composite) criterias that would not require nesting?

If you answer these questions as an edit to this question, then we will be able to more properly answer your questions than the general terms I'm using in this answer and we may provide code for you as well. However, if you think this through, then you might also be able to implement this in a not nested way. And, if you are able to understand this as far as to implement it into a not nested way, then you could transform that implementation into a nested implementation as well.

huangapple
  • 本文由 发表于 2023年2月9日 00:43:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/75388989.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定