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