Hierarchical query to view the hierarchy as a plain text
I was looking around queries to process hierarchical data and came across some of Oracle's features to query hierarchical data and was able to create this quite interesting way to view parent/child data in maximo to view location hierarchy data, for example. It uses the "start with", "connect by" and "prior" syntax and outputs the leaf element with a nice tree path. select location , parent , systemid , level , RPAD ( '.' , ( level -1 )* 2 , '.' ) || location AS tree , LTRIM ( SYS_CONNECT_BY_PATH ( location , ' < ' ), ' < ' ) AS path , CONNECT_BY_ISLEAF AS leaf from lochierarchy where systemid = 'HVAC' --where location in ('SF-CMT-1MZ-FCU-43','FCU-CMT-1F-FCU-68','08-CMT-1088','FCU-CMT','FCU','ACAIR','HVAC','MECH') and systemid='HVAC' START WITH location in ( 'SF-CMT-1MZ-FCU-43' ) CONNECT...