Devart Forums

Code: Select all

create table TEST
(
  RN           NUMBER not null,
  PRN          NUMBER(17),
  CODE         VARCHAR2(20) not null,
  NAME         VARCHAR2(240) not null,
  hier_level   NUMBER(1) not null
)
/
INSERT INTO TEST (RN,PRN,CODE,NAME,hier_level) 
SELECT 1 ,null, '01-00-00','A',1  FROM DUAL UNION ALL
SELECT 2 ,null, '02-00-00','B',1  FROM DUAL UNION ALL
SELECT 3 ,1,    '01-01-00','C',2  FROM DUAL UNION ALL
SELECT 4 ,1,    '01-02-00','D',2  FROM DUAL UNION ALL
SELECT 5 ,2,    '02-01-00','E',2   FROM DUAL UNION ALL
SELECT 6 ,2,    '02-02-00','G',2  FROM DUAL UNION ALL
SELECT 7 ,3,    '01-01-01','J',3   FROM DUAL UNION ALL
SELECT 8 ,3,    '01-01-01','S',3  FROM DUAL UNION ALL
SELECT 9 ,4,    '01-02-01','R',3  FROM DUAL UNION ALL
SELECT 10,4,    '01-02-02','T',3  FROM DUAL
/
create or replace type ins_obj  as object  
 (RN number(17), CODE varchar2(20), NAME varchar2(240))
/
create or replace type ins_tab as table of  ins_obj
/
create or replace type st as object(
x varchar2(100),
y varchar2(100)
)
/

Code: Select all

select 
CAST(MULTISET
(SELECT  i.RN,i.CODE,i.NAME FROM test i where i.hier_level = 1 
START WITH i.rn=T.RN CONNECT BY  i.RN=  prior i.PRN)
 as ins_tab)   L1,
 st(CODE ,name)
from TEST T