Under the Hood

Under the Hood

On January 23, 2014, Posted by , In Aviation,Fleet Management,Healthcare,Industry,IT Asset Management,Maven,Maximo, With Comments Off on Under the Hood

Have you ever needed to list all locations under a common parent?  If you use Oracle, this is a simple task. Oracle has a built-in function that allows you to string together multiple child records in a single row.

The Oracle listagg function is a built-in function that allows for many table columns to be displayed within a single row:

Example SQL:
select parent, listagg(location, ‘,’)
within group (order by location)
from lochierarchy
group by parent;

Example SQL Output:
Parent Location | Child Locations
Facility                 | Facility01, Facility02, Facility02, Facility03, Facility04

The function returns a concatenated list of all of the children of each parent.  This is very useful when working with multiple location hierarchies in Maximo.

Comments are closed.