Hierarchical Query: Get the ancestors in top down order

June 25, 2015 at 4:39 pm | Posted in Oracle Developement | Leave a comment

This query starts with the leaf node and climbs up the hierarchy to the top ancestor. It then displays the result in the usual top-down order:


CMR@vrep > list
 select lvl
    ,employee
   from (select level    lvl
	    ,lpad(' ', 2 * (  
                    max(rownum)
                     over () - level)
                 , ' ')
	     ||empno
	     ||', '
	     ||ename employee
	    ,rownum  rn
       from emp
     connect by empno=prior mgr
     start with ename=upper('&ename'))
  order by rn desc

CMR@vrep > /
Enter value for ename: adams

       LVL EMPLOYEE
---------- --------------------------
     4      7839, KING
     3        7566, JONES
     2          7788, SCOTT
     1           7876, ADAMS

CMR@vrep >

 

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: