PJF_EPS_HIERARCHIES_CF_V

Details

• Schema: FUSION

• Object owner: PJF

• Object type: VIEW

Columns

Name
STRUCTURE_ID LEVEL0_NODE_ID LEVEL1_NODE_ID LEVEL2_NODE_ID LEVEL3_NODE_ID LEVEL4_NODE_ID LEVEL5_NODE_ID LEVEL6_NODE_ID LEVEL7_NODE_ID LEVEL8_NODE_ID LEVEL9_NODE_ID LEVEL10_NODE_ID DISTANCE

Query

SQL_Statement

select substr( scbp, instr(scbp,'-',1,1)+1, instr(scbp,'-',1,2)-instr(scbp,'-',1,1)-1) structure_id, node_id level0_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, decode(DISTANCE, 3, node_id, decode(DISTANCE, 4, node_id, decode(DISTANCE, 5, node_id, decode(DISTANCE, 6, node_id, decode(DISTANCE, 7, node_id, decode(DISTANCE, 8, node_id, decode(DISTANCE, 9, node_id, substr( scbp, instr(scbp,'-',1,9)+1, instr(scbp,'-',1,10)-instr(scbp,'-',1,9)-1)))))))))) level1_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, decode(DISTANCE, 3, node_id, decode(DISTANCE, 4, node_id, decode(DISTANCE, 5, node_id, decode(DISTANCE, 6, node_id, decode(DISTANCE, 7, node_id, decode(DISTANCE, 8, node_id, substr( scbp, instr(scbp,'-',1,8)+1, instr(scbp,'-',1,9)-instr(scbp,'-',1,8)-1))))))))) level2_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, decode(DISTANCE, 3, node_id, decode(DISTANCE, 4, node_id, decode(DISTANCE, 5, node_id, decode(DISTANCE, 6, node_id, decode(DISTANCE, 7, node_id, substr( scbp, instr(scbp,'-',1,7)+1, instr(scbp,'-',1,8)-instr(scbp,'-',1,7)-1)))))))) level3_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, decode(DISTANCE, 3, node_id, decode(DISTANCE, 4, node_id, decode(DISTANCE, 5, node_id, decode(DISTANCE, 6, node_id, substr( scbp, instr(scbp,'-',1,6)+1, instr(scbp,'-',1,7)-instr(scbp,'-',1,6)-1))))))) level4_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, decode(DISTANCE, 3, node_id, decode(DISTANCE, 4, node_id, decode(DISTANCE, 5, node_id, substr( scbp, instr(scbp,'-',1,5)+1, instr(scbp,'-',1,6)-instr(scbp,'-',1,5)-1)))))) level5_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, decode(DISTANCE, 3, node_id, decode(DISTANCE, 4, node_id, substr( scbp, instr(scbp,'-',1,4)+1, instr(scbp,'-',1,5)-instr(scbp,'-',1,4)-1))))) level6_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, decode(DISTANCE, 3, node_id, substr( scbp, instr(scbp,'-',1,3)+1, instr(scbp,'-',1,4)-instr(scbp,'-',1,3)-1)))) level7_node_id, decode(DISTANCE, 1, node_id, decode(DISTANCE, 2, node_id, substr( scbp, instr(scbp,'-',1,2)+1, instr(scbp,'-',1,3)-instr(scbp,'-',1,2)-1))) level8_node_id, decode(DISTANCE, 1, node_id, substr( scbp, instr(scbp,'-',1,1)+1, instr(scbp,'-',1,2)-instr(scbp,'-',1,1)-1)) level9_node_id, substr( scbp, instr(scbp,'-',1,1)+1, instr(scbp,'-',1,2)-instr(scbp,'-',1,1)-1) level10_node_id, DISTANCE from (select sys_connect_by_path (node_id, '-') || '-' scbp, level DISTANCE, node_id from pjf_eps_hierarchies where node_type_code in ('R','U','S') CONNECT BY PRIOR node_id = parent_node_id START WITH node_type_code = 'R')