英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论