CREATE TABLE TEST42 (YMD VARCHAR2(08),ACC_CD VARCHAR2(04),ACC_AM NUMBER);
CREATE INDEX TEST42_I ON TEST40(YMD,ACC_CD);
INSERT INTO TEST42 VALUES ('19990930','122', 300);
INSERT INTO TEST42 VALUES ('19990930','130', 200);
INSERT INTO TEST42 VALUES ('19990930','120', 150);
INSERT INTO TEST42 VALUES ('19990930','141', 90);
INSERT INTO TEST42 VALUES ('19990930','131', 30);
CREATE TABLE TEST41 (ACC_CD VARCHAR2(04),PACC_CD VARCHAR2(04));
CREATE INDEX TEST41_I ON TEST40(ACC_CD);
INSERT INTO TEST41 VALUES ('100','');
INSERT INTO TEST41 VALUES ('120','100');
INSERT INTO TEST41 VALUES ('130','100');
INSERT INTO TEST41 VALUES ('122','120');
INSERT INTO TEST41 VALUES ('121','120');
INSERT INTO TEST41 VALUES ('123','120');
INSERT INTO TEST41 VALUES ('131','130');
INSERT INTO TEST41 VALUES ('140','100');
INSERT INTO TEST41 VALUES ('141','140');


순환참조에서 상위코드로 집계



조건.
     TEST41 :

     ACC_CD PACC_CD
     ------ -------
     100      
     120    100
     130    100
     122    120
     121    120
     123    120
     131    130
     140    100
     141    140    


     TEST42 :

     YMD      ACC_CD ACC_AM    
     -------- ------ ----------
     19990930 122           300
     19990930 130           200
     19990930 120           150
     19990930 141            90
     19990930 131            30

    TEST42 에는 각 일자별로 특정계정에 대한 발생금액이 들어있다.
    TEST41 은 계정코드와 해당 계정이 속한 상위 계정에 대한 정보를
    가지고 있다.

    즉 다음과 같은 게층형 구조를 포함하고 있는 DATA 이다.

    100 -> 120 -> 121
               -> 122
               -> 123
           130 -> 131
           140 -> 141
문제.
    문제는 TEST42에서 발생한 DATA에 대해서 자신의 계정과 자신의
    상위계정을 TEST41에서 모두 찾아 해당 값을 더해 주는 것이다.
    예를 들어
    123 계정에 대해서 발생한 금액이 있다면 123,120,100 계정에
    각각 값을 더하고,
    130 계정에 대하여 발생한 금액이 있다면 130,100 계정에 각각 값을
    더하여 각 계정별 합을 구하는 문제이다.
    위의 DATA를 이용했을 경우 결과가 다음과 같이 나오면 된다.

    YMD      ACC_ SUM(S1.ACC
    -------- ---- ----------
    19990930 100         770     <= 300+200+150+90+30
    19990930 120         450     <= 300+150
    19990930 122         300     <= 300
    19990930 130         230     <= 200+30
    19990930 131          30     <= 30
    19990930 140          90     <= 90
    19990930 141          90     <= 90
        
생각.
    전형적인 순환참조로
    흔히 이야기하는 B.O.M. 구조를 가지는 DATA 이다.
    발생금액을 기준으로 볼 경우,
    특정 계정에 금액이 발생하면 TEST41 을 찾아가 해당 계정의
    상위계정을  모두 찾아서 금액을 할당하고,
    다음 DATA들에 대해서 동일한 행위를 반복한후 계정별로 집계가
    이루어 진다면 해결 가능하다.
    반대로 계정구조를 기준으로 볼 경우,
    가장 상위계정부터 차례로 자신의 하위계정을 모두 찾아낸후
    그 계정들이 가지고 있는 금액만 집계한다면 또한 해결 가능한
    문제이다.
    후자의 경우에서처럼 상위코드를 기준으로 하위코드를 찾아내는 일을
    쉽게 해결해 주는 구문이 CONNECT BY ...START WITH... 이다.
    잠시 설명을 추가하자면
    CONNECT BY 는 계층형 관계를 QUERY 하기 위하여 사용되며,
    SELECT 시 TABLE 사이의 계층관계를 정의 하는 부분이다.
    한쪽 조건앞에 PRIOR 구를 붙여야 하며 PRIOR가 붙은 쪽은
    PARENT-CHILD 관계에서 CHILD 에 해당하며 다른 한쪽에 PARENT를
    기술한다.
    위의 경우를 예로들면
    CONNECT BY PRIOR ACC_CD = PACC_CD 와 같이 기술될 것이다.
    START WITH는 만족해야 하는 조건을 지정함으로써 트리구조의 ROOT에
    해당하는 행을 지정한다.
    START WITH를 생략하면 SELECT 문의 WHERE 구를 만족하는 모든행이
    ROOT로 된다.
    그리고 CONNECT BY를 포함한 SELECT 문의 경우 LEVEL을
    사용할 수 있다.
    자세한 내용은 기본문법을 설명한 참고서적을 이용하기 바란다.
        
    어찌되었건 우리는 이용가능한 모든 환경을 이용하여 우리의 문제를
    해결하면 된다.
    TEST41에 존재하는 모든코드에 대해서 자신의 하위 LEVEL에
    해당하는 CODE를 찾고,
    그코드에 해당하는 금액을 TEST42에서 읽어다 더하는 것으로 우리의
    목적을 달성할 수 있을 것이다.

해법.
    단계1.가장먼저 짚고 넘어야 할 부분은  CONNECT BY ..START WITH..의
          사용법이다.
          다음과 같은 QUERY 를 만들어 보자.
    
          SELECT LEVEL,ACC_CD,PACC_CD
          FROM   TEST41
          CONNECT BY PRIOR ACC_CD = PACC_CD
          START WITH ACC_CD = '100'

          아래와 같은 결과가 나온다.
          LEVEL 별로 한칸씩 밀어 넣어보면 쉽게 구분이 간다.

          LEVEL       ACC_ PACC
          ---------- ---- ----
                  1   100      
                   2  120  100
                    3 122  120
                    3 121  120
                    3 123  120
                   2  130  100
                    3 131  130
                   2  140  100
                    3 141  140
     단계2.우리가 하고자 하는 일은 TEST41에 존재하는 ACC_CD 하나에
           대해서 단계1의 작업을 수행하고,그결과를 TEST42와 연결해
           해당 CODE에 딸린 금액을 읽어 오는 일이다.
           결국은 두개의 테이블을 조인하여
           TEST42의해당일자/TEST41의ACC_CD 를 기준으로 GROUP을 짓고
           TEST42의 ACC_AM의 SUM를 구하는데,
           이때 TEST41의 ACC_CD를 ROOT로 하여 전개된 ACC_CD 들의
           해당금액들이 ACC_AM 이 된다.
           말로하면 어렵고 직접 문장을 보자.  

           SELECT S1.YMD,
                  S2.ACC_CD,
                  SUM(S1.ACC_AM)
           FROM   TEST42  S1,
                  TEST41  S2
           WHERE  S1.ACC_CD IN (SELECT  ACC_CD
                                FROM    TEST41
                                CONNECT BY PRIOR ACC_CD = PACC_CD
                                START WITH ACC_CD = S2.ACC_CD)
           GROUP BY
                  S1.YMD,
                  S2.ACC_CD

           PLAN :
           SELECT STATEMENT Optimizer=CHOOSE
             SORT (GROUP BY)
               FILTER
                 NESTED LOOPS
                   TABLE ACCESS (FULL) OF TEST41
                   TABLE ACCESS (FULL) OF TEST42
                 FILTER
                   CONNECT BY
                     TABLE ACCESS (FULL) OF TEST41
                     TABLE ACCESS (BY USER ROWID) OF TEST41
                     TABLE ACCESS (FULL) OF TEST41
뒷풀이.
      ACC_AM 이 존재하는 코드와 그 상위 코드에 대해서만  DATA가
      DISPLAY 된다.
      구조를 미리 가져다 놓고 구조를 기준으로 값을 가져다 붙이는
      것도 물론 가능하다.
      그런 주제는 이미 우리가 앞에서 다루었으므로 생략하기로 한다.
      꼭 그렇게 하지 않더라도 방법은 또 있을 것이다.
      결과가 아래와 같이 나오는 문장도 직접 만들어 보자.

      ACC_CD   SUM(S1.ACC
      -------- ----------
      100             770
      120             450
      121                
      122             300
      123                
      130             230
      131              30
      140              90
      141              90