[oracle] sys_connect_by_path()

SYS_CONNECT_BY_PATH( 컬럼명, '구분자')

 

오라클 계층형 쿼리함수이다.

첫번째 인자에 컬럼명이 두번째 인자는 구분되는 문자열값을 받는다.

 

ORA-30004 : SYS_CONECT_BY_PATH 함수를 사용할 때 열 값의 일부로 분리자를 사용할 수 없습니다.

 

 

함수에서 받는 컬럼명의 해당 값이 해당 구분자를 포함할때 나는 오류이다.

구분자를 바꾸거나 해당컬럼값에 입력되는 값중 구분자를 제한해야 한다.

 

 

 

 

WITH DT AS 

 

    (SELECT TO_DATE('2014-04-02', 'YYYY-MM-DD') - 7 * (LEVEL-1) AS DT1, LEVEL FROM DUAL

     CONNECT BY LEVEL <= (TO_DATE('2014-04-02', 'YYYY-MM-DD')-(TO_DATE('2014-02-05', 'YYYY-MM-DD')) /7+1

    )

    SELECT CHECK_ID

                 CHECK_CATEGORY,

                 CHECK_CATEGORY_NAME,

                 COUNT(*) OVER(PARTITION BY CHECK_CATEGORY) CHECK_CATEGORY_CNT,

                 ROW_NUMBER() OVER(PARTITION BY CHECK_CATEGORY ORDER BY CHECK_ID) CHECK_CATEGORY_RNUM,

                 CHECK_MANAGER,

                 CHECK_MANAGER_NM,

                 CHECK_CONTENT,

                 CHECK_PARAMETER_TARGET,

                 CHECK_PARAMETER_VALUE,

                 CHECK_PURPOSE_TARGET,               

                 CHECK_PURPOSE_VALUE,        

                 STATE_RESULT,        
       FROM (

                  SELECT CHECK_ID,

                               CHECK_CATEGORY,

                               CHECK_CATEGORY_NAME,

                               CHECK_MANAGER,

                               CHECK_MANAGER_NM,

                               CHECK_CONTENT,

                               CHECK_PARAMETER_TARGET,

                               CHECK_PARAMETER_VALUE,

                               CHECK_PURPOSE_TARGET,               

                               CHECK_PURPOSE_VALUE,        

                               SUBSTR(MAX(sys_connect_by path(STATE_RESULT_VALUE, ';') STATE_RESULT

                     FROM  (

                                SELECT CL.CHECK_ID,

                                             CL.CHECK_CATEGORY,

                                             pkg_common.getCodeValueById(CL.CHECK_CATEGORY) CHECK_CATEGORY_NAME,

                                             CL.CHECK_MANAGER,

                                             pkg_common.getCodeValueById(CL.CHECK_MANAGER) CHECK_MANAGER_NM,

                                             CL.CHECK_CONTENT,

                                             CL.CHECK_PARAMETER_TARGET,

                                             CL.CHECK_PARAMETER_VALUE,

                                             CL.CHECK_PURPOSE_TARGET,

                                             CL.CHECK_PURPOSE_VALUE,

                                             ROW_NUMBER() OVER(PARTITION BY CL.CHECK_ID ORDER BY CL.DT1 DESC) RNUM

                                   FROM (

                                             SELECT * FROM TB_SECU_DAY_CHECK_LIST, DT

                                              WHERE NVL(TB_ITP_SECU_DAY_CHECK_LIST.CHECK_DEL_YN, 'N') = 'N'

                                             ) CL,

                                             (

                                             SELECT STATE_DATE,

                                                          CHECK_ID,

                                                          NVL(STATE_RESULT_VALUE, ' ') STATE_RESULT_VALUE

                                                FROM TB_ITP_SECU_DAY_CHECK_STATE

                                              WHERE STATE_DATE BETWEEN TO_DATE('2014-02-05', 'YYYY-MM-DD') AND

                                                          TO_DATE('2014-04-02', 'YYYY-MM-DD')

                                              ) STATE

                                  WHERE CL.CHECK_ID = STATE.CHECK_ID(+)

                                              AND CL.DT1 = STATE.STATE_DATE(+)

                                  ) SL

                       WHERE 1 = 1

                                   START WITH RNUM   =   1

                                   CONNECT BY PRIOR RNUM = RNUM - 1

                                   AND PRIOR CHECK_ID = CHECK_ID

                                   GROUP BY CHECK_ID,

                                                    CHECK_CATEGORY,

                                                    CHECK_CATEGORY_NM,

                                                    CHECK_MANAGER,  

                                                    CHECK_MANAGER_NM,

                                                    CHECK_CONTENT,    

                                                    CHECK_PARAMETER_TARGET,

                                                    CHECK_PARAMETER_VALUE,

                                                    CHECK_PURPOSE_TARGET,               

                                                    CHECK_PURPOSE_VALUE

                                   ORDER BY CHECK_CATEGORY, CHECK_ID

                         ) WHERE 1=1 ;