순환쿼리 관련 질문입니다.
현재 작업쿼리를 보면 ROW 생성은 된거 같은데 순서가 제대로 안잡힙니다.
원하는 순서는 엑셀로 업로드 하였습니다.
많은 의견 부탁드립니다
* LEV 낮은 순으로 MAX_USE 만큼 반복 ROW를 생성
* 상위로 올라가면 하위LEV 도 같이 반복
* 기초 데이터 -> TEMP_T
* 작업중인 쿼리
with temp_t as
(select '10000' UIDcol, 'AAA' COD_ID, '' UID_MGR, 1 max_use
from dual
UNION ALL
select '10001' UIDcol, 'ABC' COD_ID, '10000' UID_MGR, 1 max_use
from dual
UNION ALL
select '10002' UIDcol, 'ABD' COD_ID, '10000' UID_MGR, 3 max_use
from dual
UNION ALL
select '10003' UIDcol, 'ACC' COD_ID, '10000' UID_MGR, 2 max_use
from dual
UNION ALL
select '10004' UIDcol, 'ACA' COD_ID, '10000' UID_MGR, 1 max_use
from dual
UNION ALL
select '10005' UIDcol, 'ADC' COD_ID, '10000' UID_MGR, 1 max_use
from dual
UNION ALL
select '10006' UIDcol, 'AFC' COD_ID, '10000' UID_MGR, 3 max_use
from dual
UNION ALL
select '10007' UIDcol, 'ACG' COD_ID, '10006' UID_MGR, 1 max_use
from dual
UNION ALL
select '10008' UIDcol, 'ACA' COD_ID, '10006' UID_MGR, 3 max_use
from dual
UNION ALL
select '10009' UIDcol, 'ABC' COD_ID, '10008' UID_MGR, 2 max_use
from dual
UNION ALL
select '10010' UIDcol, 'AfC' COD_ID, '10000' UID_MGR, 1 max_use
from dual)
select * from (
select a.*,
TO_NUMBER(dbms_xmlgen.getxmltype('SELECT ' || con_val || ' FROM dual')
.Extract('//text()')) P_MAX_USE
from (select LEVEL LEV,
LPAD(' ', 4 * (LEVEL - 1)) || COD_ID UIDlev,
UIDcol,
UID_MGR,
max_use,
SUBSTR(SYS_CONNECT_BY_PATH(max_use, '*'), 2) con_val
from temp_t
start with UID_MGR is null
connect by prior UIDcol = UID_MGR) a ) a , (select level row_seq from dual connect by level <= 99) b
where p_max_use >= row_seq
with temp_t as (select '10000' UIDcol, 'AAA' COD_ID, '' UID_MGR, 1 max_use from dual UNION ALL select '10001' UIDcol, 'ABC' COD_ID, '10000' UID_MGR, 1 max_use from dual UNION ALL select '10002' UIDcol, 'ABD' COD_ID, '10000' UID_MGR, 3 max_use from dual UNION ALL select '10003' UIDcol, 'ACC' COD_ID, '10000' UID_MGR, 2 max_use from dual UNION ALL select '10004' UIDcol, 'ACA' COD_ID, '10000' UID_MGR, 1 max_use from dual UNION ALL select '10005' UIDcol, 'ADC' COD_ID, '10000' UID_MGR, 1 max_use from dual UNION ALL select '10006' UIDcol, 'AFC' COD_ID, '10000' UID_MGR, 3 max_use from dual UNION ALL select '10007' UIDcol, 'ACG' COD_ID, '10006' UID_MGR, 1 max_use from dual UNION ALL select '10008' UIDcol, 'ACA' COD_ID, '10006' UID_MGR, 3 max_use from dual UNION ALL select '10009' UIDcol, 'ABC' COD_ID, '10008' UID_MGR, 2 max_use from dual UNION ALL select '10010' UIDcol, 'AfC' COD_ID, '10000' UID_MGR, 1 max_use from dual) --select * from temp_t SELECT UIDCOL , COD_ID , UID_MGR , MAX_USE , LEVEL FROM (SELECT * FROM TEMP_T A , (SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 3 ) B WHERE A.MAX_USE >= B.LVL ORDER BY A.UIDCOL , B.LVL ) CONNECT BY PRIOR UIDCOL = UID_MGR START WITH UIDCOL = '10000'