如何优化PL/SQL中的嵌套CASE语句

Question

我对PL/SQL还很陌生。我在SELECT查询中使用以下嵌套的CASE语句。这遵循WHERE子句。但是，由于这种嵌套，查询大约需要6分钟才能完成。如果删除了至少一个CASE块，则查询只需大约1分钟即可完成。有什么方法可以优化这个查询吗？

(case
when a = 'STAGE PAYMENT' then
'Y'
when b not IN ('To be Received', 'Received') then
'N'
when c != (d - NVL(e, 0) - NVL(f, 0) - NVL(g, 0)) then
'Y'
when NVL(h, 0) + NVL(i, 0) + NVL(j, 0) <> 0 then
case 
when c != k then
'Y'
when (-l != NVL(e, 0) + NVL(f, 0) + NVL(g, 0) + NVL(m, 0)) then
'Y' 
else 'N'
end 
else 'N'
end = 'Y')

我试过使用IF-ELSE块，但没有任何好处。

Answer 1

用AND聚合父案例中的嵌套案例怎么样？

(case
when a = 'STAGE PAYMENT' then
'Y'
when b not IN ('To be Received', 'Received') then
'N'
when c != (d - NVL(e, 0) - NVL(f, 0) - NVL(g, 0)) then
'Y'
when NVL(h, 0) + NVL(i, 0) + NVL(j, 0) <> 0 and c != k then
'Y'
when NVL(h, 0) + NVL(i, 0) + NVL(j, 0) <> 0 and (-l != NVL(e, 0) + NVL(f, 0) + NVL(g, 0) + NVL(m, 0)) then
'Y'
-- this serves as else 'N' in your code  
when NVL(h, 0) + NVL(i, 0) + NVL(j, 0) <> 0 then
'N'
else 'N'
end = 'Y')