Select a Maximo location hierarchy in SQL Server

Sometimes the quickest way to understand a Maximo location hierarchy is to see the whole thing as a query result. SQL Server's recursive common table expressions are a good fit: start with the top-level location, repeatedly find its children and build a readable path as you go.

This query is read-only, but run it against a non-production database first and review its execution plan before using it on a large hierarchy.

Choose the site and system

A Maximo site can contain multiple location systems, and a location can have a different parent in each one. A hierarchy query therefore needs both SITEID and SYSTEMID; filtering by the site alone can combine unrelated paths.

Set the two variables to the hierarchy you want to inspect:

DECLARE @SITEID VARCHAR(20) = 'BEDFORD';
DECLARE @SYSTEMID VARCHAR(20) = 'PRIMARY';

The values above are examples. Use a site and system from your own environment.

Select the hierarchy

The anchor query finds the root location, where PARENT is NULL. The recursive query then joins every child to the previous level within the same site, organisation and system.

DECLARE @SITEID VARCHAR(20) = 'BEDFORD';
DECLARE @SYSTEMID VARCHAR(20) = 'PRIMARY';
 
WITH LOCATION_TREE (
  LOCATION,
  PARENT,
  HIERARCHY_LEVEL,
  SITEID,
  ORGID,
  SYSTEMID,
  TREEPATH,
  VISITED_LOCATIONS
) AS (
  SELECT
    LOCATION_HIERARCHY.LOCATION,
    LOCATION_HIERARCHY.PARENT,
    0 AS HIERARCHY_LEVEL,
    LOCATION_HIERARCHY.SITEID,
    LOCATION_HIERARCHY.ORGID,
    LOCATION_HIERARCHY.SYSTEMID,
    CAST(LOCATION_HIERARCHY.LOCATION AS VARCHAR(MAX)) AS TREEPATH,
    CAST('>' + LOCATION_HIERARCHY.LOCATION + '>' AS VARCHAR(MAX)) AS VISITED_LOCATIONS
  FROM
    LOCHIERARCHY AS LOCATION_HIERARCHY
  WHERE
    LOCATION_HIERARCHY.PARENT IS NULL
    AND LOCATION_HIERARCHY.SITEID = @SITEID
    AND LOCATION_HIERARCHY.SYSTEMID = @SYSTEMID
 
  UNION ALL
 
  SELECT
    CHILD.LOCATION,
    CHILD.PARENT,
    LOCATION_TREE.HIERARCHY_LEVEL + 1,
    CHILD.SITEID,
    CHILD.ORGID,
    CHILD.SYSTEMID,
    CAST(LOCATION_TREE.TREEPATH + ' > ' + CHILD.LOCATION AS VARCHAR(MAX)),
    CAST(LOCATION_TREE.VISITED_LOCATIONS + CHILD.LOCATION + '>' AS VARCHAR(MAX))
  FROM
    LOCHIERARCHY AS CHILD
    INNER JOIN LOCATION_TREE
      ON CHILD.PARENT = LOCATION_TREE.LOCATION
      AND CHILD.SITEID = LOCATION_TREE.SITEID
      AND CHILD.ORGID = LOCATION_TREE.ORGID
      AND CHILD.SYSTEMID = LOCATION_TREE.SYSTEMID
  WHERE
    CHARINDEX('>' + CHILD.LOCATION + '>', LOCATION_TREE.VISITED_LOCATIONS) = 0
)
SELECT
  LOCATION,
  PARENT,
  HIERARCHY_LEVEL,
  SITEID,
  ORGID,
  SYSTEMID,
  TREEPATH
FROM
  LOCATION_TREE
ORDER BY
  TREEPATH
OPTION (MAXRECURSION 32767);

The result contains one row per location, its immediate parent, its depth below the root and a path such as:

PLANT > BUILDING-A > FLOOR-1 > ROOM-101

Sorting by TREEPATH keeps parents and their descendants together in a form that is easy to export or inspect.

Why the extra columns matter

Join on LOCATION, SITEID, ORGID and SYSTEMID. A location can belong to more than one system and have a different parent in each hierarchy. IBM's location-system documentation describes this model.

VISITED_LOCATIONS is not displayed. It records the locations already used in the current path so malformed circular data cannot recurse forever. The final MAXRECURSION option raises SQL Server's default recursion limit for unusually deep but valid trees; the cycle check remains the first line of defence.

VARCHAR(MAX) prevents the path from being truncated at a 255-character limit. If location identifiers contain characters outside your database code page, use NVARCHAR(MAX) and Unicode string literals instead.

Use this only for hierarchical systems

This query expects each location to have no more than one parent within the selected system. Maximo also supports network systems, where a location can have multiple parents and children. A network is a graph rather than a simple tree, so the same location can legitimately appear on several paths.

For application development, IBM also provides a hierarchy-aware REST API scoped by SYSTEMID. Prefer that supported API when an integration needs to navigate Maximo data rather than produce a database report.