Oracle Hierarchical query: how to include top-level parent

Question

I have a hierarchical query to track a reporting structure. This almost works, except that it's not reporting the very top level node, probably because the top-level people "report" to themselves.

The query is:

select
  level,
  empid, 
  parentid
from usertable
connect by nocycle prior parentid= empid
start with empid = 50

This produces:

LEVEL  EMPID PARENTID               
------ ----- --------  
1      50    258            
2      258   9555
3      9555  17839

I'm not getting a level 4, since it would look like:

4      17839 17839

Without changing data, is there a way to modify my query so that all 4 levels are returned? The goal is to get the empids, so I can do a check for

id in (hierarchical subquery)

BTW, if I remove the nocycle from the query I get an error.

Answer 1

You need to do the hierarchy the other way around, from the root to the leaves.

select
  level,
  empid, 
  parentid
from usertable
start with empid = 17839
connect by empid != 17839 and prior empid = parentid

LEVEL                  EMPID                  PARENTID               
---------------------- ---------------------- ---------------------- 
1                      17839                  17839                  
2                      9555                   17839                  
3                      258                    9555                   
4                      50                     258                    

4 rows selected

Answer 2

seems like you have a cycle in data. Without "nocycle" it will not work straight away. If you know that all your data has maximum nesting level 4, then you could add condition "and level <= 4" and remove nocycle. Should work.

Answer 3

Chris,

You only get 3 rows because your top level row is not set the way it should to handle hierarchical queries. Typically the top level row, or president KING in Oracle's well known EMP table, has no manager. In your case you should not set the parentid of 17389 to 17389 itself, but to NULL. Either update the table accordingly, or use a view to accomodate for this situation.

An example:

SQL> select empno
  2       , mgr
  3    from emp
  4   where empno in (7876,7788,7566,7839)
  5  /

     EMPNO        MGR
---------- ----------
      7566       7839
      7788       7566
      7839       7839
      7876       7788

4 rijen zijn geselecteerd.

This part of the EMP table has four levels with its top level row (7839) set to itself. The same as your empid 17839. And this leads to only three rows using your query:

SQL>  select level
  2        , empno
  3        , mgr
  4     from emp
  5  connect by nocycle prior mgr = empno
  6    start with empno = 7876
  7  /

     LEVEL      EMPNO        MGR
---------- ---------- ----------
         1       7876       7788
         2       7788       7566
         3       7566       7839

3 rijen zijn geselecteerd.

Either use a (inline) view to set the mgr/parentid column to null for the top level:

SQL>  select level
  2        , empno
  3        , mgr
  4     from ( select empno
  5                 , nullif(mgr,empno) mgr
  6              from emp
  7          )
  8  connect by nocycle prior mgr = empno
  9    start with empno = 7876
 10  /

     LEVEL      EMPNO        MGR
---------- ---------- ----------
         1       7876       7788
         2       7788       7566
         3       7566       7839
         4       7839

4 rijen zijn geselecteerd.

Or fix your data with an UPDATE statement:

SQL> update emp
  2     set mgr = null
  3   where empno = 7839
  4  /

1 rij is bijgewerkt.

SQL>  select level
  2        , empno
  3        , mgr
  4     from emp
  5  connect by nocycle prior mgr = empno
  6    start with empno = 7876
  7  /

     LEVEL      EMPNO        MGR
---------- ---------- ----------
         1       7876       7788
         2       7788       7566
         3       7566       7839
         4       7839

4 rijen zijn geselecteerd.

And you can leave out the NOCYCLE keyword as well, after you are done fixing.

Regards, Rob.