Maximo

 View Only

Classification summary view, including aggregated USE WITH column

  • 1.  Classification summary view, including aggregated USE WITH column

    Posted Sun December 19, 2021 07:41 AM
    Edited by User1971 Mon December 20, 2021 11:32 PM
    MAM 7.6.1.2, Oracle 19c:

    I thought I'd share a classification summary query that I came up with (including an aggregated USE WITH column) -- in case it's useful to anyone.


    select
        cl.classstructureid,
        level as pseudo_level, --the CLASSANCESTOR table already has an OOB HIERARCHYLEVEL column. But the level numbers in that column are in reverse-order (sorted descending) and the level numbers start at 0 instead of 1 -- which isn't what we want.
        count(level) over (partition by connect_by_root(cl.classificationid)) as level_count,
        cl.classificationid,
        cl.description,
        cl.cghierarchypath,
        --ltrim(sys_connect_by_path(cl.classificationid, ' \ '),' \ ') as hierarchypath, --the hierarchy path can be calculated dynamically too.
        cl.parent,
        cl.haschildren,
        connect_by_root(cl.classstructureid) as top_classstructureid,
        connect_by_root(cl.classificationid) as top_classificationid,
        uw.usewith,
        cl.cgactive
    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 cl.parent is null
    connect by prior cl.classstructureid = parent
    order by
        top_classstructureid,  --handles scenarios like the FACILITIES classifications -- we have top-level FACILITIES classifications for both ASSET and WO (different sets of classifications)
        cghierarchypath





    Notes:

    • Related: Maximo classification summary query
    • LEVEL_COUNT is the number of classifications that are in the same set (have the same top CLASSSTRUCTUREID).
    • Hierarchy Path:
      • In my case, CGHIERARCHYPATH is a custom persistent field in CLASSSTRUCTURE, populated by an automation script: mbo.setValue('CGHIERARCHYPATH', mbo.getString('HIERARCHYPATH'))
      • But it's easy to calculate the hierarchy path dynamically in the query above too, by adding this calculated column to the SELECT statement: ltrim(sys_connect_by_path(cl.classificationid, ' \ '),' \ ') as hierarchypath,
      • More info here: Generate hierarchical path