데이터베이스 질문과 답변

쿼리 문의드립니다. 2 46

by 가을에사랑 [2018.04.17 17:17:08]

아래와 같은 결과를 얻으려고 CONNECT BY를 생각해 보고 시도 하다가
답을 얻지 못해서 조언을 받고자 올려봅니다.
CONNECT_BY_ISLEAF를 이용해보려도 시도 해보고 있구요...
REGEXP_SUBSTR를 사용하고 나서 CONNECT BY를 해서인지....토드에서 오류도 나네요 ㅠㅠ
안쪽 쿼리만으로는 잘 되는데...

SELECT
    PROCID, EDU_SEQ
    ,MAX(CONNECT_BY_ISLEAF * PROCID) OVER () AS CHANGE_PROCID
    ,MAX(CONNECT_BY_ISLEAF * EDU_SEQ) OVER () AS CHANGE_EDU_SEQ
    ,EDU_CHANGE_FLAG_TYPE AS CHANGE_EDU_CHANGE_FLAG_TYPE
    ,EDU_GBN, EDU_PLANTYPE
    ,EDU_ID_LIST, EDU_NAME_LIST, EDU_DEPTNAME
    ,EDU_ETC_LIST, EDU_ETC_CNT
    ,EDU_TITLE, EDU_ORGAN, EDU_PLACE, EDU_TUITION_FEE, EDU_ETC_FEE, EDU_REFUND_FLAG
    ,EDU_YEAR, EDU_PERIOD_TYPE, EDU_PERIOD_LIST, EDU_PERIOD_ETC, EDU_START_DATE, EDU_END_DATE, EDU_TIME
    ,EDU_TYPE, EDU_CONTENTS_ETC, SPV_GBN
    ,EDU_FLAG, EDU_CHANGE_FLAG_TYPE
FROM
(
    SELECT
        EL.PROCID, EL.EDU_SEQ, 
        EDU_BEFORE_PROCID,
        EDU_BEFORE_SEQ,
        EL.EDU_GBN, EL.EDU_PLANTYPE,
        NVL(LTRIM(REGEXP_SUBSTR(EDU_ID_LIST,'[^,]+',1,LV)), 'NOT') EDU_ID_LIST,
        LTRIM(REGEXP_SUBSTR(EDU_NAME_LIST,'[^,]+',1,LV)) EDU_NAME_LIST,
        LTRIM(REGEXP_SUBSTR(EDU_DEPTNAME,'[^,]+',1,LV)) EDU_DEPTNAME,
        EL.EDU_ETC_LIST, EL.EDU_ETC_CNT,
        EL.EDU_TITLE, EL.EDU_ORGAN, EL.EDU_PLACE, EDU_TUITION_FEE, EDU_ETC_FEE, EDU_REFUND_FLAG,
        EL.EDU_YEAR, EDU_PERIOD_TYPE, NVL(EDU_PERIOD_LIST, 'NOT') EDU_PERIOD_LIST, EDU_PERIOD_ETC, EDU_START_DATE, EDU_END_DATE, EDU_TIME,
        EL.EDU_TYPE, EL.EDU_CONTENTS_ETC, EL.SPV_GBN,
        EDU_FLAG, EDU_CHANGE_FLAG_TYPE
    FROM
        WF_EDUCATION_LIST EL,
        (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) LE
    WHERE 1=1
        AND EDU_GBN = 'OUT'
        AND EDU_NAME_LIST LIKE '%이순신%'
        AND REGEXP_SUBSTR(EDU_ID_LIST,'[^,]+',1,LV) IS NOT NULL
)A
WHERE 1=1
START WITH EDU_BEFORE_SEQ IS NULL
CONNECT BY PRIOR EDU_SEQ = EDU_BEFORE_SEQ


ID가 1번인 업무 이외에는 변경사항이 없어서 그대로 조회되어야 하지만
1번은 두번에 걸쳐서 변경사항이 생겨서 최초 값과 최종변경값을 얻어내려는 상태입니다.

 

원본 데이터

ID SEQ BEFORE_ID BEFORE_SEQ CHANGE USING TITLE 원인
1 11       NOT 업무1  
1 12       USE 업무2  
2 13       USE 업무3  
3 14       USE 업무4  
4 15 1 11 CHANGE NOT 업무1 바쁨
5 16       USE 업무5  
6 17 4 15 CHANGE USE 업무1 또 바쁨

 

조회 결과

ID SEQ CHANGE_ID CHANGE_SEQ CHANGE_TYPE USING TITLE 원인
1 11 6 17 CHANGE USE 업무1 또바쁨
1 12       USE 업무2  
2 13       USE 업무3  
3 14       USE 업무4  
5 16       USE 업무5  
by 신이만든지기 [2018.04.17 18:41:07] 
WITH T AS (
SELECT 1 ID, 11 SEQ, NULL BEFORE_ID, NULL BEFORE_SEQ, NULL CHANGE, 'NOT' USING, '업무1' TITLE, NULL REASON FROM DUAL UNION ALL
SELECT 1 ID, 12 SEQ, NULL BEFORE_ID, NULL BEFORE_SEQ, NULL CHANGE, 'USE' USING, '업무2' TITLE, NULL REASON FROM DUAL UNION ALL
SELECT 2 ID, 13 SEQ, NULL BEFORE_ID, NULL BEFORE_SEQ, NULL CHANGE, 'USE' USING, '업무3' TITLE, NULL REASON FROM DUAL UNION ALL
SELECT 3 ID, 14 SEQ, NULL BEFORE_ID, NULL BEFORE_SEQ, NULL CHANGE, 'USE' USING, '업무4' TITLE, NULL REASON FROM DUAL UNION ALL
SELECT 4 ID, 15 SEQ, 1 BEFORE_ID, 11 BEFORE_SEQ, 'CHANGE' CHANGE, 'NOT' USING, '업무1' TITLE, '바쁨' REASON FROM DUAL UNION ALL
SELECT 5 ID, 16 SEQ, NULL BEFORE_ID, NULL BEFORE_SEQ, NULL CHANGE, 'USE' USING, '업무5' TITLE, NULL REASON FROM DUAL UNION ALL
SELECT 6 ID, 17 SEQ, 4 BEFORE_ID, 15 BEFORE_SEQ, 'CHANGE' CHANGE, 'USE' USING, '업무1' TITLE, '또 바쁨' REASON FROM DUAL
)
SELECT ID, SEQ
     , DECODE(BEFORE_ID, ID, NULL, BEFORE_ID) BEFORE_ID
     , DECODE(BEFORE_SEQ, SEQ, NULL, BEFORE_SEQ) BEFORE_SEQ
     , CHANGE, USING, TITLE, REASON
  FROM (SELECT ID, SEQ
             , MAX(BEFORE_ID) OVER(PARTITION BY ID, SEQ) BEFORE_ID
             , MAX(BEFORE_SEQ) OVER(PARTITION BY ID, SEQ) BEFORE_SEQ
             , CHANGE, USING, TITLE, REASON
             , ROW_NUMBER() OVER(PARTITION BY ID, SEQ ORDER BY BEFORE_SEQ DESC) RN
          FROM (SELECT CONNECT_BY_ROOT ID ID
                     , CONNECT_BY_ROOT SEQ SEQ
                     , ID BEFORE_ID, SEQ BEFORE_SEQ
                     , CHANGE, USING, TITLE, REASON             
                  FROM T  
                 START WITH BEFORE_ID IS NULL
                CONNECT BY  BEFORE_ID = PRIOR ID
                        AND BEFORE_SEQ = PRIOR SEQ ))
 WHERE RN = 1;

 

by 마농 [2018.04.17 19:46:48] 
SELECT CONNECT_BY_ROOT(procid ) procid
     , CONNECT_BY_ROOT(edu_seq) edu_seq
     , DECODE(LEVEL, 1, null, procid ) change_procid
     , DECODE(LEVEL, 1, null, edu_seq) change_edu_seq
     , ...
  FROM (SELECT *
          FROM wf_education_list
         WHERE edu_gbn = 'OUT'
           AND edu_name_list LIKE '%이순신%'
        )
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH edu_before_procid IS NULL
        AND edu_before_seq    IS NULL
 CONNECT BY PRIOR procid  = edu_before_procid
        AND PRIOR edu_seq = edu_before_seq
;

 

by 가을에사랑 [2018.04.18 10:14:58]

마농님 감사합니다. ^^

그런데 추가 질문이 있어요...

제가 적은 쿼리에서 FROM절에 있는 REGEXP_SUBSTR는 필수로 필요한데 
토드에서 실행하면 REGEXP_SUBSTR 때문에 EOF에러가 나서 실행되다 말게 되는데 해결책이 있을까요
알려주신대로 하면 잘 되는데 REGEXP_SUBSTR까지 이용하면 같은 현상이라서요 ㅠㅠ

하나의 PROCID, EDU_SEQ에 해당하는 EDU_ID_LIST에는(0000004823,0000008557)
여러 사람이 존재하는데 각각을 개별 사용하게 됩니다.
그래서 PROCID, EDU_SEQ, EDU_ID가 기준값이 되거든요

ORA-03113: 통신 채널에 EOF 가 있습니다
프로세스 ID: 0
세션 ID: 430 일련 번호: 27575

by 마농 [2018.04.18 13:02:27]

connect by level 을 이용한 행복제 및 regexp_substr 은
계층 쿼리 전개 전에 수행하시면 안되고, 중복 발생으로 쿼리가 뻗을 수 있음
계층 전개 후에 수행하세요.

SELECT procid
     , edu_seq
     , change_procid
     , change_edu_seq
     , REGEXP_SUBSTR(edu_id_list, '[^,]+', 1, lv) edu_id
     , ...
  FROM (SELECT CONNECT_BY_ROOT(procid ) procid
             , CONNECT_BY_ROOT(edu_seq) edu_seq
             , DECODE(LEVEL, 1, null, procid ) change_procid
             , DECODE(LEVEL, 1, null, edu_seq) change_edu_seq
             , edu_id_list
             , ...
          FROM (SELECT *
                  FROM wf_education_list
                 WHERE edu_gbn = 'OUT'
                   AND edu_name_list LIKE '%이순신%'
                )
         WHERE CONNECT_BY_ISLEAF = 1
         START WITH edu_before_procid IS NULL
                AND edu_before_seq    IS NULL
         CONNECT BY PRIOR procid  = edu_before_procid
                AND PRIOR edu_seq = edu_before_seq
        ) a
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) b
 WHERE lv <= REGEXP_COUNT(edu_id_list, '[^,]+')
;