This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Here is a query that I use to pull the location hierarchy for our sites. It should be easily adapted to the asset tables.
WITH LOCDATA AS (SELECT LOCANC.LOCATION, LOCANC.ANCESTOR FROM PZMAX.LOCANCESTOR LOCANC, PZMAX.LOCATIONS LOC WHERE LOCANC.SITEID = LOC.SITEID AND LOCANC.LOCATION=LOC.LOCATION AND LOCANC.SITEID = :SITEID AND LOC.STATUS = 'OPERATING')
SELECT PATHDATA.LOCATION, PATHDATA.LOCPATH, PATHDATA.LOCLEV
FROM
(SELECT LOCDATA.LOCATION, MAX(LEVEL) AS MAXLEVEL
FROM LOCDATA
START WITH LOCDATA.LOCATION = :TOPLEVEL
CONNECT BY NOCYCLE PRIOR LOCDATA.LOCATION = LOCDATA.ANCESTOR
GROUP BY LOCDATA.LOCATION) DEEPEST,
(SELECT LOCDATA.LOCATION, LEVEL AS LOCLEV, SYS_CONNECT_BY_PATH(LOCDATA.LOCATION, '/') as LOCPATH
FROM LOCDATA
START WITH LOCDATA.LOCATION = :TOPLEVEL
CONNECT BY NOCYCLE PRIOR LOCDATA.LOCATION = LOCDATA.ANCESTOR)PATHDATA
WHERE
DEEPEST.MAXLEVEL = PATHDATA.LOCLEV AND
DEEPEST.LOCATION = PATHDATA.LOCATION
ORDER BY PATHDATA.LOCATION
Thank you!