Maximo Open Forum

Jason Uppenborn posted 04-11-2022 10:19

As a rule, hierarchy tables, tables with a PARENT attribute, have a corresponding Ancestor table. Asset, LocHierarchy, WorkOrder and ClassStructure are examples of hierarchy tables. Every record in a hierarchy table has corresponding Ancestor records that link to itself, to its parent, to its grand parent, and so on all the way to the very top of the hierarchy (the very first ancestor). The result is that you can super-easily determine if one hierarchy record is an ancestor or descendent of another.

For example, the following generalized query will return data if the hier2 record is somewhere (anywhere) under hier1. In the where clause, you could leave off the hier1 condition to look from one location all the way up, or leave off the hier2 condition to look all the way down. This simple joining is much easier than including a hierarchical or recursive query and then joining to that.

select hier1.num, hier1.description, hier2.num, hier2.description
from hierarchy hier1
  join ancestor anc on hier1.num = anc.ancestor
  join hierarchy hier2 on hier2.num = anc.num​
where 
  hier1.num = 'NUM_1'
  and hier2.nuum = 'BIG_NUM'

Steven Hauptman posted 04-11-2022 10:24

Hi Tom,

The drill down just uses the simple parent-child relationship.  AssetAncestor is a more comprehensive set of relationships to allow for easier searching - like all work orders, contracts, etc for an asset in a given hierarchy.