MAM 7.6.1.2; Oracle 19c:

I want to write an SQL query on the LOCATIONS table that has a column for the pseudo top-level of each location:


Is there a way to do that using existing fields from one of the locations tables?

#AssetandFacilitiesManagement
#Maximo

A note about my "pseudo top-level" wording:

We have a dummy location called "SERVICES” which is the technically the top-level location for the entire location hierarchy.
 - I think a single, top-level location is a requirement when you define a "hierarchical system" of locations.

Locations application --> Open Drilldown


I don't consider the "SERVICES" level to be a real level.
When I say I want to get the "pseudo top-level" in the original question, I'm technically referring to level-2.

(I normally just exclude the "SERVICES" level from my queries: ... <> 'SERVICES'.)

#Maximo
#AssetandFacilitiesManagement

Everything at level 2 will have SERVICES as the parent. We can use the lochierarchy table to find all locations the parent SERVICES and use that to filter the locancestor table.

select location,ancestor from locancestor where exists 
(select 1 from lochierarchy where parent = 'SERVICES' and lochierarchy.location = locancestor.ancestor)​

Original Message:
Sent: Thu October 14, 2021 07:38 PM
From: User1971
Subject: Locations SQL query: Get specific hierarchy level as column (the pseudo top-level)

A note about my "pseudo top-level" wording:

We have a dummy location called "SERVICES" (same name as our single-site) which is the technically the top-level location for the entire location hierarchy.
 - I think a single, top-level location is a requirement when you define a "hierarchical system" of locations.

Locations application --> Open Drilldown


Anyway, the point I wanted to make was: I don't consider the "SERVICES" level to be a real level.
When I say I want to get the "pseudo top-level" in the original question, I'm technically referring to level-2.

(I normally just exclude the "SERVICES" level from my queries: ... <> 'SERVICES'.)

#AssetandFacilitiesManagement
#Maximo

Here is a query you can use to see the location hierarchy for all locations under your 'SERVICES'  location. I added a field that indents the location with periods so you can easily see the relationships. If doing this for a report, I change it to use a space instead of a period, and make sure to set the property of the BIRT text field to respect the leading space. (It strips them out by default.)

You could do something similar in DB2 or MS SQL, but you'd need to use standard ANSI recursive SQL to do it there.

select
  location,
  level,
  lpad('.',level*5) || location as padded_loc
from
  lochierarchy
connect by 
  prior location = parent and prior siteid = siteid
start with
  parent = 'SERVICES'

If you wanted to see all location hierarchies, you would just replace:

parent is 'SERVICES'

with

parent is null

Here is what the output looks for the whole location hierarchy in the Maximo sample data:

------------------------------
CHRISTOPHER WESTFALL
------------------------------

Original Message:
Sent: Thu October 14, 2021 07:35 PM
From: User1971
Subject: Locations SQL query: Get specific hierarchy level as column (the pseudo top-level)

MAM 7.6.1.2; Oracle 19c:

I want to write an SQL query on the LOCATIONS table that has a column for the pseudo top-level of each location:


Is there a way to do that using existing fields from one of the locations tables?

#AssetandFacilitiesManagement
#Maximo