Christoph's 2 Cents

A Backup for My Brain!

Oracle DevelopementPL/SQL

Find ancestor from hierarchy

I needed to find a quick way to select a particular ancestor for a child in a hierarchical table. For example: “Find the manager of SMITH” (parent), or “Find SMITH’s manager’s manager” (grand parent).

Using the emp table as an example, I came up with the following solution:

SELECT sys_connect_by_path(ename, ':') path,
                sys_connect_by_path(ename, ':'), 
                ':[A-Z]{1,}', 1, &x),':'
       ) ancestor
  FROM emp
 WHERE connect_by_isleaf = 1
CONNECT BY PRIOR mgr = empno

This query traverses the hierarchical tree up from the child, and limits it to only that row where the employee I’m looking for is the leaf (connect_by_isleaf=1), i.e. the lowest in the hierarchy.  Then I use the sys_connect_by_path to fetch the entire hierarchical path, and utilize the regexp_substr function to pick out the ancestor. Which ancestor is selected depends on &x. If x=1 then the child itself is selected. If x=2, the parent is selected; x=3 is the grand parent and so on.

SQL > /
Enter value for x: 3
PATH                           ANCESTOR
------------------------------ ----------

Lastly, here are a couple of examples that show traversing the path forwards and backwards from/to a given descendant:

col ename for a20
col root for a20
col path for a30
PROMPT Forwards path:
PROMPT ==============
SELECT connect_by_root ename root
     , sys_connect_by_path(ename, ':') path
     , ename
  FROM emp
 WHERE ename          = 'SMITH'
START WITH ename       = 'KING'
CONNECT BY prior empno = mgr

PROMPT Backwards path:
PROMPT ===============
SELECT connect_by_root ename ename
     , sys_connect_by_path(ename, ':') path
     , ename root
  FROM emp
 WHERE connect_by_isleaf = 1
 START WITH ename       = 'SMITH'
CONNECT BY empno = prior mgr

Forwards path:

ROOT                 PATH                           ENAME
-------------------- ------------------------------ --------------------
KING                 :KING:JONES:FORD:SMITH         SMITH

Backwards path:

ENAME                PATH                           ROOT
-------------------- ------------------------------ --------------------
SMITH                :SMITH:FORD:JONES:KING         KING

3 thoughts on “Find ancestor from hierarchy

  • Christopher,
    This is absolutely the best explanation and illustration of these hierarchical functions that I’ve run across! Thanks so much.
    We have Laserfiche as our document management system and its core table is hierarchical. Navigating the trees has been an awful challenge. We have about 1 million documents and sifting through the metadata just got a lot easier with your examples.
    Thanks again.

Comments are closed.