Hierarchy Query

select level, self_id, child_id, self_name, child_name

from

(select parent_id self_id, child_id, parent_name self_name, child_name from

(select HREL.SUBJECT_ID parent_id,

HREL.OBJECT_ID child_id,

HSUB.PARTY_NAME parent_name,

HOBJ.PARTY_NAME child_name

from hz_relationships HREL,

HZ_PARTIES HSUB,

HZ_PARTIES HOBJ

where 1=1

AND HREL.OBJECT_TYPE ='ORGANIZATION'

AND HREL.SUBJECT_TYPE ='ORGANIZATION'

AND HREL.RELATIONSHIP_CODE ='PARENT_OF'

AND HREL.SUBJECT_ID = HSUB.PARTY_ID

AND HREL.OBJECT_ID = HOBJ.PARTY_ID

) y

where 1=1) x

start with x.self_id  = <start with party id>

connect by prior x.child_id = x.self_id

order by 1,2,3,4;


--------------

select  
level_info
--,self_id
--,child_id
--,self_name
--,child_name
,(select party_name from hz_parties where party_id = 179695 ) root_node_name
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 1) level1
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 2) level2
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 3) level3 
from (
select level level_info, self_id, child_id, self_name, child_name , 
SYS_CONNECT_BY_PATH(child_name, '//') path_data
from 
(select parent_id self_id, child_id, parent_name self_name, child_name from
(select HREL.SUBJECT_ID parent_id,
HREL.OBJECT_ID child_id,
HSUB.PARTY_NAME parent_name,
HOBJ.PARTY_NAME child_name
from hz_relationships HREL,
HZ_PARTIES HSUB,
HZ_PARTIES HOBJ
where 1=1
AND HREL.OBJECT_TYPE ='ORGANIZATION' 
AND HREL.SUBJECT_TYPE ='ORGANIZATION' 
AND HREL.RELATIONSHIP_CODE ='PARENT_OF' 
AND HREL.SUBJECT_ID = HSUB.PARTY_ID
AND HREL.OBJECT_ID = HOBJ.PARTY_ID
) y
where 1=1) x
start with x.self_id  = 179695
connect by nocycle prior x.child_id = x.self_id)
order by 1,2,3,4;