Oracle案例统计

Question

您可以在另一个case语句中使用case语句的别名吗？我有以下声明

CASE
WHEN ROW_NUMBER()
OVER(PARTITION BY s.sco_cand, aw.award_id
ORDER BY
s.sco_cand, aw.award_id, ai.a_instalment_id
) = 1 THEN
SUM(vt.net_amount - sl18.fee_paid)
END AS DIFFERENCE

这可以计算出两个值之间的差异，同时抑制报告的重复值(但这并不重要，tbh(。

我想用上面的语句来创建一个"；评论"列，该列测试在上面语句中找到的输出，并向用户提供反馈建议。我已经成功地创建了这个专栏，但是我必须将上面的整个代码粘贴到我的新案例语句中，使其能够工作

CASE

WHEN CASE
WHEN ROW_NUMBER ()
OVER (
PARTITION BY S.SCO_CAND,AW.AWARD_ID
ORDER BY
S.SCO_CAND,
AW.AWARD_ID,
AI.A_INSTALMENT_ID) = 1
THEN
SUM (VT.NET_AMOUNT - SL18.FEE_PAID)

END = 0
THEN
'OK'
WHEN CASE
WHEN ROW_NUMBER ()
OVER (
PARTITION BY S.SCO_CAND,AW.AWARD_ID
ORDER BY
S.SCO_CAND,
AW.AWARD_ID,
AI.A_INSTALMENT_ID) = 1
THEN
SUM (VT.NET_AMOUNT - SL18.FEE_PAID)

END
IS NULL
THEN
'TRANSACTION MISSING'
WHEN CASE
WHEN ROW_NUMBER ()
OVER (
PARTITION BY S.SCO_CAND,AW.AWARD_ID
ORDER BY
S.SCO_CAND,
AW.AWARD_ID,
AI.A_INSTALMENT_ID) = 1
THEN
SUM (VT.NET_AMOUNT - SL18.FEE_PAID)

END > 0
THEN
'OVER PAYMENT'
WHEN CASE
WHEN ROW_NUMBER ()
OVER (
PARTITION BY S.SCO_CAND,AW.AWARD_ID
ORDER BY
S.SCOT_CAND,
AW.AWARD_ID,
AI.A_INSTALMENT_ID) = 1
THEN
SUM (VT.NET_AMOUNT - SL18.FEE_PAID)

END < 0
AND SCY.CCHANGE IS NOT NULL 
THEN
'UNDERPAYMENT'
ELSE 'INVESTIGATE'

END
COMMENTS

我将有大约11个使用初始代码块的用例语句(一旦完整的测试列表完成(。无论如何，我是否可以对初始块进行别名，然后进一步引用别名，以防止代码变得难以阅读(我必须注意，经验不足的员工也必须遵循此代码(。

Answer 1

最简单的方法是外部选择

select .. 
case 
when DIFFERENCE = 0 then 'OK'
when DIFFERENCE > 0 then 'OVER PAYMENT' 
when DIFFERENCE < 0 then 'UNDER PAYMENT' 
else 'INVESTIGATE' 
end COMMENTS
from (
select ..
CASE
WHEN ROW_NUMBER()
OVER(PARTITION BY s.sco_cand, aw.award_id
ORDER BY
s.sco_cand, aw.award_id, ai.a_instalment_id
) = 1 THEN
SUM(vt.net_amount - sl18.fee_paid)
END AS DIFFERENCE
from ..
) t

Answer 2

当然可以使用WITH..AS子句，如

WITH t AS
(
SELECT CASE
WHEN ROW_NUMBER() OVER(PARTITION BY S.SCO_CAND,
AW.AWARD_ID ORDER BY S.SCO_CAND,
AW.AWARD_ID,
AI.A_INSTALMENT_ID) = 1 THEN
SUM(VT.NET_AMOUNT - SL18.FEE_PAID)
END AS case_stmt
FROM t0
)
SELECT CASE
WHEN case_stmt = 0 THEN
'OK'
WHEN case_stmt = 1 AND ... THEN 
SUM(VT.NET_AMOUNT - SL18.FEE_PAID) 
WHEN case_stmt > 0 THEN 
'OVER PAYMENT'      
....     
....
ELSE 'INVESTIGATE'       
END AS comments
FROM t