Maximo

Expand all | Collapse all

Get classification hierarchy path (Oracle SQL)

  • 1.  Get classification hierarchy path (Oracle SQL)

    Posted Wed June 09, 2021 04:18 PM
    Edited by User1971 3 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.


    Source:
    Generate hierarchical path



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

    Posted Thu June 10, 2021 02:27 AM
    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 Sat June 19, 2021 07:33 PM
    Edited by User1971 3 days ago

    Here's a version of the query that also includes the USE WITH objects (grouped by & concatenated):

    select 
        ltrim(sys_connect_by_path(cl.classificationid, ' \ '),' \ ') as hierarchypath,
        cl.classstructureid,
        cl.description,
        uw.usewith
    from 
        maximo.classstructure cl
    left join
        (
        select 
            classstructureid, 
            listagg(objectname,', ') within group(order by objectname) as usewith
        from 
            maximo.classusewith
        group by 
            classstructureid
        ) uw
        on cl.classstructureid = uw.classstructureid
    start with parent is null
    connect by prior cl.classstructureid = parent
    order by
        hierarchypath
    



    For extra bonus points, it would be cool to get the specs/attributes as either:
         A) grouped/concatenated into a single field
    or, B) in separate fields: USEWITH1, USEWITH2, USEWITH3, etc.. 

    In my case, I have a maximum of 25 specs/attributes per classification. Not sure which option would be better.





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

    Posted Thu June 10, 2021 07:59 AM
    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
    ------------------------------



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

    Posted Thu June 10, 2021 10:32 AM
    Steven
    Thank you very much, you are always so helpful.
    It works a treat.

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



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

    Posted 3 days ago
    Edited by User1971 3 days ago

    For what it's worth, the same sort of query can be applied to assets too (not just classifications):

    select

        assetnum,

        ltrim(sys_connect_by_path(assetnum, ' \ '),' \ ') as path,

        level

    from

       maximo.asset

    start with parent is null

    connect by prior assetnum = parent

     



    I also added a LEVEL column that gives us the level number. This could be done in the classifications queries too.



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

    Posted 2 days ago
    Edited by User1971 2 days ago