SELECT DECODE(first_tb.flag,
NULL,
CASE
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '6')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '6') THEN
'6'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '5')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '5') THEN
'5'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '4')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '4') THEN
'4'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '3')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '3') THEN
'3'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '2')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '2') THEN
'2'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '1') THEN
'1'
END,
CASE
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '6')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '6') THEN
'6'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '5')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '5') THEN
'5'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '4')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '4') THEN
'4'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '3')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '3') THEN
'3'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '2')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '2') THEN
'2'
WHEN ABS(NVL(first_tb.margin_shortfall, 0)) >=
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'A'
AND shortfall_level = '1') THEN
'1'
END) AS alt_level,
.....
from (select .. from xx)first_tb
其中first_tb是一个复杂的查询,由first_tb得到first_tb.margin_shortfall的值,然后进行判断得到不同的级别
下面的字句存在大量重复,仅仅是由于列值shortfall_level = '6'不同。 有什么样的方法能否简化并使得查询高效?
(SELECT start_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '6')
AND ABS(NVL(first_tb.margin_shortfall, 0)) <=
(SELECT end_shortfall
FROM comp_level
WHERE above_or_below_alert_level = 'B'
AND shortfall_level = '6')
SQL> desc comp_level
Name Null? Type
----------------------------------------- -------- ----------------------------
ABOVE_OR_BELOW_ALERT_LEVEL NOT NULL CHAR(1)
SHORTFALL_LEVEL NOT NULL NUMBER(2)
START_SHORTFALL NOT NULL NUMBER(10)
END_SHORTFALL NOT NULL NUMBER(10)
SQL> select * from comp_level;
A SHORTFALL_LEVEL START_SHORTFALL END_SHORTFALL
- --------------- --------------- -------------
B 2 3000000 10000000
B 3 2000000 3000000
B 4 500000 2000000
B 5 10000 500000
B 6 0 10000
A 2 1000000 5000000
A 3 500000 1000000
A 4 100000 500000
A 5 5000 100000
A 6 0 5000
B 1 10000000 -1
A SHORTFALL_LEVEL START_SHORTFALL END_SHORTFALL
- --------------- --------------- -------------
A 1 5000000 -1