WHEN T6.START_END_DIFF <= 20 THEN

最新推荐文章于 2024-08-14 06:53:59 发布

原创最新推荐文章于 2024-08-14 06:53:59 发布 · 390 阅读

0 ·

CC 4.0 BY-SA版权

ORCLE 专栏收录该内容

3 篇文章

订阅专栏

本文深入探讨了SQL中CASE WHEN与DECODE函数的使用场景及技巧，通过具体的例子展示了如何灵活运用这些函数进行数据筛选与转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

小心case when 里面的判断

SELECT T7.XXC_TYPE,

T7.EVENT_ID,
DECODE(T7.DIS_END, 0, 1, 0) COL0,
DECODE(T7.DIS_END, 1, 1, 0) COL1,
DECODE(T7.DIS_END, 2, 1, 0) COL2,
DECODE(T7.DIS_END, 3, 1, 0) COL3,
DECODE(T7.DIS_END, 4, 1, 0) COL4,
DECODE(T7.DIS_END, 5, 1, 0) COL5,
DECODE(T7.DIS_END, 6, 1, 0) COL6
FROM (SELECT T6.XXC_TYPE,
T6.EVENT_ID,
CASE
WHEN T6.START_END_DIFF > 0 AND
T6.START_END_DIFF <= 10 THEN
0
WHEN T6.START_END_DIFF <= 20 THEN
1
WHEN T6.START_END_DIFF <= 30 THEN
2
WHEN T6.START_END_DIFF <= 40 THEN
3
WHEN T6.START_END_DIFF <= 50 THEN
4
WHEN T6.START_END_DIFF <= 60 THEN
5
ELSE
6
END DIS_END
FROM (SELECT T5.XXC_TYPE,
T4.EVENT_ID,
ABS((T3.OUTAGE_START_TIME -
T4.OUTAGE_START_TIME) * 24 * 60) START_TIME_DIFF,
ABS((T3.OUTAGE_END_TIME - T4.OUTAGE_END_TIME) * 24 * 60) START_END_DIFF
FROM DMS_CRC_EVENT_RIMS_TRANS T3,
DMS_CRC_EVENT_SD_TRANS T4,
(SELECT T1.EVENT_ID RIMS_EVENT_ID,
T2.EVENT_ID SD_EVENT_ID,
T1.XXC_TYPE
FROM DMS_CRC_EVENT_RIMS T1,
DMS_CRC_EVENT_SD T2
WHERE T1.XXC_EVENT_ID = T2.OUTAGE_ID AND
T1.XXC_TYPE = T2.XXC_TYPE
AND T1.XXC_TYPE IN (2)
AND t2.event_id=909069
AND T1.OUTAGE_START_TIME BETWEEN TO_DATE('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2013-11-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) T5
WHERE T3.TRANS_CODE = T4.TRANS_CODE AND
T3.EVENT_ID = T5.RIMS_EVENT_ID
AND T4.EVENT_ID = T5.SD_EVENT_ID
) T6) T7
GROUP BY T7.XXC_TYPE, T7.EVENT_ID, T7.DIS_END