WITH T AS ( SELECT 'A' AS EMPCD, TO_DATE('20190110', 'YYYYMMDD') AS IN_DATE, 4 TERM FROM DUAL UNION ALL SELECT 'B' AS EMPCD, TO_DATE('20190112', 'YYYYMMDD') AS IN_DATE, 5 TERM FROM DUAL )
출력되어지는 결과는?
EMPCD | IN_DATE |
A | 20190110 |
A | 20190111 |
A | 20190112 |
A | 20190113 |
B | 20190112 |
B | 20190113 |
B | 20190114 |
B | 20190115 |
B | 20190116 |
위 결과처럼 입력일자(IN_DATE)가 기간(TERM)만큼 보여지도록 하는것인데
LEVEL... CONNECT BY를 사용하여 보았지만 결과가 기하급수적으로 늘어나서 도움 요청드립니다
WITH T AS ( SELECT 'A' AS EMPCD, TO_DATE('20190110', 'YYYYMMDD') AS IN_DATE, 4 TERM FROM DUAL UNION ALL SELECT 'B' AS EMPCD, TO_DATE('20190112', 'YYYYMMDD') AS IN_DATE, 5 TERM FROM DUAL ) SELECT A.EMPCD , A.IN_DATE + LV - 1 IN_DATE , TERM , LV FROM T A ,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= ( SELECT MAX(TERM) FROM T )) B WHERE B.LV <= A.TERM ORDER BY A.EMPCD , B.LV
다건 테이블에 직접 Connect By LEVEL <= n 하시면 안됩니다.
단건 테이블 dual 에 적용 후 조인하는 방식으로 하세요.
http://gurubee.net/article/55635