[DB2] With문을 이용한 Recursive SQL

Oracle의 Connect by절은 보통 계층적인 구조를 만들때 사용합니다.

그러나 DB2에서는 위의 SQL문을 제공하지 않습니다. 다른 DBMS에도 없는 것으로 알고 있습니다.

그래서 계층 구조를 만들려면 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