Recursive SQL(With문을 이용한)
보통 계층적인 구조를 만들때 사용한다.
Oracle의 Connect by절은 DB2에서는 없다.
그래서 With절을 이용해서 만들어야 한다.
다음 사이트를 참고한다.
http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/
DB2 9.5 SQL CookBook에도 나와있다.(18page 맨아래 예제, 307page)
예제 1. (LEVEL 숫자 포함)
Oracle
SELECT LEVEL, name
FROM emp
START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid
DB2
WITH n(level, empid, name) AS
(SELECT 1, empid, name
FROM emp
WHERE name = 'Goyal'
UNION ALL
SELECT n.level + 1, nplus1.empid, nplus1.name
FROM emp as nplus1, n
WHERE n.empid = nplus1.mgrid)
SELECT level, name FROM n;
LEVEL NAME
----------- ----------
1 Goyal
2 Zander
2 Henry
2 Scott
3 McKeough
3 Barnes
3 O'Neil
3 Smith
3 Shoeman
9 record(s) selected
예제 2.
Oracle
SELECT
TO_CHAR(TO_DATE('201106','YYYYMM') + LEVEL-1, 'YYYYMMDD') AS DT,
TO_CHAR(TO_DATE('201106','YYYYMM') + LEVEL-1, 'DAY') AS WEEK
FROM DUAL
CONNECT BY LEVEL <= TRUNC(TO_DATE('201106','YYYYMM')+32,'MM') - to_date('201106','YYYYMM')
DB2
WITH DateRange(dt) AS
(
SELECT date('2011-06-01') AS dt FROM dual
UNION ALL
SELECT (date(dt) + 1 day) AS dt FROM DateRange WHERE dt < (date('2011-07-01') - 1 day)
)
SELECT to_char(A.dt,'YYYYMMDD') as dt FROM DateRange A
예제 3.
Oracle
SELECT LEVEL, TO_CHAR(TO_NUMBER('20111006') - 3 + LEVEL) Y_CD
FROM DUAL
CONNECT BY LEVEL <= 3
DB2
WITH TEMPDATE(Y_CD, LVL) AS
(
SELECT CHAR(INTEGER('20111006')-2) AS Y_CD, 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CHAR(INTEGER(Y_CD) + 1), LVL+1 FROM TEMPDATE WHERE LVL <= 3
)
SELECT * FROM TEMPDATE
보통 계층적인 구조를 만들때 사용한다.
Oracle의 Connect by절은 DB2에서는 없다.
그래서 With절을 이용해서 만들어야 한다.
다음 사이트를 참고한다.
http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/
DB2 9.5 SQL CookBook에도 나와있다.(18page 맨아래 예제, 307page)
예제 1. (LEVEL 숫자 포함)
Oracle
SELECT LEVEL, name
FROM emp
START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid
DB2
WITH n(level, empid, name) AS
(SELECT 1, empid, name
FROM emp
WHERE name = 'Goyal'
UNION ALL
SELECT n.level + 1, nplus1.empid, nplus1.name
FROM emp as nplus1, n
WHERE n.empid = nplus1.mgrid)
SELECT level, name FROM n;
LEVEL NAME
----------- ----------
1 Goyal
2 Zander
2 Henry
2 Scott
3 McKeough
3 Barnes
3 O'Neil
3 Smith
3 Shoeman
9 record(s) selected
예제 2.
Oracle
SELECT
TO_CHAR(TO_DATE('201106','YYYYMM') + LEVEL-1, 'YYYYMMDD') AS DT,
TO_CHAR(TO_DATE('201106','YYYYMM') + LEVEL-1, 'DAY') AS WEEK
FROM DUAL
CONNECT BY LEVEL <= TRUNC(TO_DATE('201106','YYYYMM')+32,'MM') - to_date('201106','YYYYMM')
DB2
WITH DateRange(dt) AS
(
SELECT date('2011-06-01') AS dt FROM dual
UNION ALL
SELECT (date(dt) + 1 day) AS dt FROM DateRange WHERE dt < (date('2011-07-01') - 1 day)
)
SELECT to_char(A.dt,'YYYYMMDD') as dt FROM DateRange A
예제 3.
Oracle
SELECT LEVEL, TO_CHAR(TO_NUMBER('20111006') - 3 + LEVEL) Y_CD
FROM DUAL
CONNECT BY LEVEL <= 3
DB2
WITH TEMPDATE(Y_CD, LVL) AS
(
SELECT CHAR(INTEGER('20111006')-2) AS Y_CD, 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CHAR(INTEGER(Y_CD) + 1), LVL+1 FROM TEMPDATE WHERE LVL <= 3
)
SELECT * FROM TEMPDATE
