Get the root record when its parent id matches the root’s id, by Zahar Hilkevich

Very often developers decide to set parent id of a hierarchy root record to the record’s id value, meaning that the parent of that record is the record itself. This works fine as long as you don’t need to show a branch (or the entire) hierarchy from a record up to its very top root.

Let’s look at a query against the famous scott schema that comes with default Oracle’s database:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
 FROM emp
 CONNECT BY empno=PRIOR mgr
 START WITH empno=7788

Result:

   ID NAME        PARENT_ID      LEVEL
----- ---------- ---------- ----------
 7788 SCOTT            7566          1
 7566 JONES            7839          2
 7839 KING                           3

so far so good – note that King’s manager employee number (parent_id) is NULL.

Let’s update it to 7839 (King’s empno) and re-run the above SQL:

UPDATE emp
SET mgr=empno
WHERE empno=7839

Now, the result is quite different:

SQL> SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
 2  FROM emp
 3  CONNECT BY empno=PRIOR mgr
 4  START WITH empno=7788
 5  /
 ERROR:
 ORA-01436: CONNECT BY loop in user data

Of course, you can use NOCYCLE clause:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
FROM emp
CONNECT BY NOCYCLE empno=PRIOR mgr
START WITH empno=7788

Result:

  ID NAME        PARENT_ID      LEVEL
---- ---------- ---------- ----------
7788 SCOTT            7566          1
7566 JONES            7839          2

The KING has gone! Now the challenge is quite apparent – we need to see the entire branch that includes the KING!

The trick is to mimic the original design when the King’s parent id was NULL!

A very first idea is to use WITH clause:

WITH x AS (
 SELECT empno AS ID, ename AS name, DECODE(mgr,empno,NULL,mgr) AS parent_id
 FROM emp
 )
 SELECT ID, name, parent_id, LEVEL
 FROM x
 CONNECT BY id=PRIOR parent_id
 START WITH id=7788

Result:

  ID NAME       PARENT_ID       LEVEL
---- ---------- --------------- ----------
7788 SCOTT      7566            1
7566 JONES      7839            2
7839 KING                       3

Note, that parent_id for KING is shown as NULL even though we updated it to 7839 – this can be fixed by applying NVL on the parent_id if needed.

A closer look allowed us to find a nice workaround that does not rely on the WITH clause:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
FROM emp
CONNECT BY empno=PRIOR DECODE(mgr,empno,NULL,mgr)
START WITH empno=7788

Result:

  ID NAME        PARENT_ID      LEVEL
---- ---------- ---------- ----------
7788 SCOTT            7566          1
7566 JONES            7839          2
7839 KING             7839          3

Here we go!

Don’t forget to rollback the changes to set mgr=NULL for the KING’s record

P.S. If you like this trick, you can find many more in my book “Oracle SQL Tricks and Workarounds”

Advertisements

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