Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  Get classification hierarchy path (Oracle SQL)

    Posted Wed June 09, 2021 04:18 PM
    Edited by System Admin Wed March 22, 2023 11:48 AM
    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


    #AssetandFacilitiesManagement
    #Maximo


  • 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
    ------------------------------

    Original Message


  • 3.  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
    ------------------------------

    Original Message


  • 4.  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
    ------------------------------

    Original Message


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

    Posted Sat June 19, 2021 07:33 PM
    Edited by System Admin Wed March 22, 2023 11:43 AM

    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.


    Original Message


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

    Posted Wed October 13, 2021 07:25 PM
    Edited by System Admin Wed March 22, 2023 11:56 AM

    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.

    Original Message


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

    Posted Thu October 14, 2021 06:17 PM
    Edited by System Admin Wed March 22, 2023 11:54 AM

    #Maximo
    #AssetandFacilitiesManagement


Related Content