Find ancestor from hierarchy

October 2, 2013 at 11:06 am | Posted in Oracle Developement, PL/SQL | 3 Comments
Tags: , , , , , , , , , , , ,

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
      ,ltrim(regexp_substr(sys_connect_by_path(ename, ':'), ':[A-Z]{1,}', 1, &x), ':') ancestor
  FROM emp
 WHERE connect_by_isleaf = 1
 START WITH ename = 'SMITH'
CONNECT BY PRIOR mgr = empno
 ORDER SIBLINGS BY ename;

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
------------------------------ ----------
:SMITH:FORD:JONES:KING         JONES
SQL >

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
Advertisements

3 Comments »

RSS feed for comments on this post. TrackBack URI

  1. 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.
    Jack

  2. This post just saved me a lot of time. Thanks Christoph!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Entries and comments feeds.

%d bloggers like this: