데이터베이스 질문과 답변
쿼리 문의드립니다.
0
4
1,501
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, '[^,]+')
;
) 버튼을 클릭하여 작성 하시면 됩니다.