SELECT A.DEPT_CODE
     , A.DEPT_VERSION
     , A.DEPT_NAME
     , A.PRIOR_DEPT_CODE
     , NVL(A.INT_PLAN, 0) INT_PLAN_CNT
     , NVL(A.EXT_PLAN, 0) EXT_PLAN_CNT
     , LEVEL
  FROM (SELECT A.DEPT_CODE
             , A.DEPT_VERSION
             , A.DEPT_NAME
             , A.PRIOR_DEPT_CODE
             -- 국내목표
             , (SELECT SUM(INT_PLAN_CNT)
                  FROM TB_APP_PLAN_MGT
                 WHERE DEPT_CODE IN
                       (SELECT DEPT_CODE
                          FROM (SELECT *
                                  FROM TB_ORG_DEPT
                                 WHERE DEPT_VERSION = ’20090921’)
                        CONNECT BY PRIOR DEPT_CODE = PRIOR_DEPT_CODE
                         START WITH DEPT_CODE = A.DEPT_CODE)) INT_PLAN
             -- 해외목표
             , (SELECT SUM(EXT_PLAN_CNT)
                  FROM TB_APP_PLAN_MGT
                 WHERE DEPT_CODE IN
                       (SELECT DEPT_CODE
                          FROM (SELECT *
                                  FROM TB_ORG_DEPT
                                 WHERE DEPT_VERSION = ’20090921’)
                        CONNECT BY PRIOR DEPT_CODE = PRIOR_DEPT_CODE
                         START WITH DEPT_CODE = A.DEPT_CODE)) EXT_PLAN
          FROM TB_ORG_DEPT A
             , TB_APP_PLAN_MGT B
         WHERE B.PLAN_YEAR(+) = ’2009’
           AND B.PLAN_MONTH(+) = ’09’
           AND B.DEPT_CODE(+) = A.DEPT_CODE
           AND B.DEPT_VERSION(+) = A.DEPT_VERSION
           AND A.DEPT_VERSION = ’20090921’
           AND A.USE_YN = ’1’) A
CONNECT BY PRIOR DEPT_CODE = PRIOR_DEPT_CODE
 START WITH PRIOR_DEPT_CODE = ’0’;

지금 짜논 쿼린데요..

일단 버전에 여러 버전이라 최근날짜버전을 가져와서 CONNECT BY해서

바깥쪽에서 SUM을 하고 다시 CONNECT BY를 했습니다.

이 쿼리도 도움을 받아서 겨우 하위레벨 값이 상위레벨에 더해져서 나오는데

시간이 너무너무 오래 걸리네요 좀 빨리 하는 방법없을까요.ㅠㅠ

초보라 설명이 좀 저렴하네요 양해바랍니다.ㅠ