데이터베이스 질문과 답변
동일테이블을 조건별로 반복 액세스 하는걸 막고자 CONNECT BY LEVEL을 이용하여 WHERE 조건에 넣고자 하는데 문법 에러가 납니다.
밑에 보시면 LV가 1일 때는 IN조건으로 ('111012','111013','111014') 2일땐 ('111015','111016','111017', '111018') 이렇게 조건을 주고 싶습니다. 방법을 아시는 고수님들 부탁드립니다. ^^
WHERE V.WKTIME_ID IN (DECODE( D.LV, 1, ('111012','111013','111014'), 2, ('111015','111016','111017', '111018')
, 3, ('01','02','03'), ('04','05','06','07','08','09','10','11') ) )
by 마농
[2018.01.09 18:02:04]
WHERE CASE WHEN v.wktime_id IN ('111012','111013','111014') THEN 1
WHEN v.wktime_id IN ('111015','111016','111017', '111018') THEN 2
WHEN v.wktime_id IN ('01','02','03') THEN 3
WHEN v.wktime_id IN ('04','05','06','07','08','09','10','11') THEN 4
END = d.lv
by 삐르짱
[2018.01.09 18:20:56]
WHERE V.WKTIME_ID IN (SELECT '111012' FROM DUAL WHERE D.LV = 1 UNION ALL
SELECT '111013' FROM DUAL WHERE D.LV = 1 UNION ALL
SELECT '111014' FROM DUAL WHERE D.LV = 1 UNION ALL
SELECT '111015' FROM DUAL WHERE D.LV = 2 UNION ALL
SELECT '111016' FROM DUAL WHERE D.LV = 2 UNION ALL
SELECT '111017' FROM DUAL WHERE D.LV = 2 UNION ALL
SELECT '111018' FROM DUAL WHERE D.LV = 2 UNION ALL
SELECT '01' FROM DUAL WHERE D.LV = 3 UNION ALL
SELECT '02' FROM DUAL WHERE D.LV = 3 UNION ALL
SELECT '03' FROM DUAL WHERE D.LV = 3 UNION ALL
SELECT '04' FROM DUAL WHERE D.LV = 4 UNION ALL
SELECT '05' FROM DUAL WHERE D.LV = 4 UNION ALL
SELECT '06' FROM DUAL WHERE D.LV = 4 UNION ALL
SELECT '07' FROM DUAL WHERE D.LV = 4 UNION ALL
SELECT '08' FROM DUAL WHERE D.LV = 4 UNION ALL
SELECT '09' FROM DUAL WHERE D.LV = 4 UNION ALL
SELECT '10' FROM DUAL WHERE D.LV = 4 UNION ALL
SELECT '11' FROM DUAL WHERE D.LV = 4
)
by 부족한놈
[2018.01.09 18:40:59]
마농님 삐르짱님 답변 감사드립니다.... 근데 수행시간이 더 느려졌어요 ㅡㅡ; 기존의 UNION ALL로 ㅋ
by 마농
[2018.01.10 07:12:33]
WHERE v.wktime_id IN ('111012','111013','111014'
,'111015','111016','111017','111018'
,'01','02','03'
,'04','05','06','07','08','09','10','11')
AND CASE WHEN v.wktime_id IN ('111012','111013','111014') THEN 1
WHEN v.wktime_id IN ('111015','111016','111017','111018') THEN 2
WHEN v.wktime_id IN ('01','02','03') THEN 3
WHEN v.wktime_id IN ('04','05','06','07','08','09','10','11') THEN 4
END = d.lv
by 마농
[2018.01.10 11:25:59]
다시 생각해 보니...이부분이 from 절에 있을 듯 한데...
(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 4) d
어차피 조건을 보면 서로 id 가 겹치는 부분이 없으므로
이부분을 아예 빼버리는게 맞을 듯 하네요.
SELECT v.*
, CASE WHEN v.wktime_id IN ('111012','111013','111014') THEN 1
WHEN v.wktime_id IN ('111015','111016','111017','111018') THEN 2
WHEN v.wktime_id IN ('01','02','03') THEN 3
WHEN v.wktime_id IN ('04','05','06','07','08','09','10','11') THEN 4
END AS lv
FROM v
-- , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 4) d -- 제거
WHERE v.wktime_id IN ('111012','111013','111014'
,'111015','111016','111017','111018'
,'01','02','03'
,'04','05','06','07','08','09','10','11')
;
) 버튼을 클릭하여 작성 하시면 됩니다.