Maximo

Expand all | Collapse all

Get classification hierarchy path (Oracle SQL)

  • 1.  Get classification hierarchy path (Oracle SQL)

    Posted 3 days ago
    Edited by User1971 2 days ago
    MAM 7.6.1.2; Oracle 19c:

    I thought I'd share a query that I use to generate the classification hierarchy path via SQL:

    select ltrim(sys_connect_by_path(classificationid, ' \ '),' \ ') as hierarchypath
    from maximo.classstructure
    start with parent is null
    connect by prior classstructureid = parent



    I thought that might be useful to someone. It's certainly been handy for me.



  • 2.  RE: Get classification hierarchy path (Oracle SQL)

    Posted 3 days ago
    This is a great feature of Oracle SQL, I'm sure many will find it useful.
    I used to use it for getting the location hierarchy but we recently moved from Oracle SQL to SQL Server and I'm now struggling to achieve the same results.

    I've got this far, but it's not quite right. 

    WITH HierarchyCTE(location, parent, path, systemid)AS
    (SELECT location, parent, CAST(parent AS varchar(1024)), systemid
    FROM lochierarchy
    WHERE parent = 'CPP-390E' and systemid = 'PRIMARY'
    UNION ALL
    SELECT c.location, c.parent, CAST(p.path + ' \ ' + c.location AS varchar(1024)), c.systemid
    FROM lochierarchy AS c INNER JOIN HierarchyCTE AS p ON c.parent = p.location WHERE c.systemid = 'PRIMARY' )

    SELECT location, parent, path, systemid
    FROM HierarchyCTE

    I'd like to get to

    But I'm getting

    Hopefully someone might be able to help.

    ------------------------------
    Richard White
    ------------------------------



  • 3.  RE: Get classification hierarchy path (Oracle SQL)

    Posted 2 days ago
    Try something like below. I haven't done a lot of testing on it, but at a glance it looks good against our system. 

    WITH location_hier(location,siteid,parent,systemid,locpath) AS
    (
    SELECT location,siteid,parent,systemid,cast(location as varchar(1024)) as locpath FROM lochierarchy WHERE parent is null

    UNION ALL

    SELECT z.location,z.siteid,z.parent,z.systemid,CAST(location_hier.locpath + '\' + z.location as varchar(1024)) as locpath FROM lochierarchy z INNER JOIN location_hier ON location_hier.location=z.parent and location_hier.siteid=z.siteid and location_hier.systemid=z.systemid
    )
    SELECT * FROM location_hier
    WHERE systemid='PRIMARY'

    ------------------------------
    Steven Shull
    Director of Development
    Projetech Inc
    Cincinnati OH
    ------------------------------



  • 4.  RE: Get classification hierarchy path (Oracle SQL)

    Posted 2 days ago
    Steven
    Thank you very much, you are always so helpful.
    It works a treat.

    ------------------------------
    Richard White
    ------------------------------