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 BY location = PRIOR parent and systemid = PRIOR systemid




I did try to make it output the path in reverse, but because of the way it works it slows down the query a lot.

Comments

Popular posts from this blog

BMXAA6713E - The record could not be retrieved from the database. Error

Rebuilding Maximo Text Indexes due to Query performance issues

List of things checked by Maximo Integrity checker