SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Get Root value from a Hierarchical query

user5605610Aug 9 2010 — edited Aug 9 2010

I am trying to write a hierarchical query for one of my tables.

I need to find out the root value by inputting a leaf value of any level.

for example: I am inputting a value which is at 4th level. I need to find the root value without displaying the other levels.

Please help.

Thanks.

This post has been answered by NSK2KSN on Aug 9 2010

Jump to Answer

munky

Sample data?
Expected output?
CREATE TABLE and INSERT statements?

Please provide us with enough information to give you a solution. You may want to look up CONNECT_BY_ROOT.

Cheers

Ben

user5605610

SELECT empno,
ename,
job,
mgr,
hiredate,
level
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr

7839 KING PRESIDENT 1
7566 JONES MANAGER 2
7788 SCOTT ANALYST 3
7876 ADAMS CLERK 4
7902 FORD ANALYST 3
7369 SMITH CLERK 4
7698 BLAKE MANAGER 2
7499 ALLEN SALESMAN 3
7521 WARD SALESMAN 3
7654 MARTIN SALESMAN 3
7844 TURNER SALESMAN 3
7900 JAMES CLERK 3
7782 CLARK MANAGER 2
7934 MILLER CLERK 3

For example if child_name = 'MILLER' in the where clause, it should return 'KING'.

Thanks.

munky

SELECT     empno,
           ename,
           job,
           mgr,
           hiredate,
           LEVEL,
           CONNECT_BY_ROOT ename
FROM       emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr

You should be able to work it out from that.

Cheers

Ben

user5605610

But, nee to search for the root level manager for a given employee. My input would be employee name.

Thanks

Frank Kulash

Hi,

What you posted is a Top-Down Query , but what you want is a Bottom-Up Query . Just reverse the CONNECT BY condition, and add the WHERE clause:

SELECT 	empno, 
	ename, 
	job, 
	mgr, 
	hiredate,
	level 
FROM 	emp 
WHERE	CONNECT_BY_ISLEAF	= 1
START WITH  ename	= 'MILLER'
CONNECT BY  empno 	= PRIOR mgr
;

"Leaf", as in "CONNECT_BY_IS<b>LEAF</b>" is relative to the CONNECT BY clause, which may be exactly the opposite of how you normally view the data.
A "leaf" is a node that at LEVEL=n, not connected to any nodes at LEVEL=(n+1).
In this query, the only node that is a leaf is the one you might normally think of as the root.

1 person found this helpful

munky

So.. no attempt to work it out then?

What about

WITH my_data AS
(
 SELECT     empno,
            ename,
            job,
            mgr,
            hiredate,
            LEVEL,
            CONNECT_BY_ROOT ename AS root_boy
 FROM       emp
 START WITH mgr IS NULL
 CONNECT BY PRIOR empno = mgr
)
SELECT root_boy
FROM   my_data 
WHERE  ename = :p_name

Cheers

Ben

NSK2KSN

Answer

TRY THIS,

SELECT     ename, PRIOR  ename MGRNAME
      FROM emp
     WHERE ename = <INPUT YOUR ENAME HERE>
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr

Edited by: NSK2KSN on Aug 9, 2010 6:10 PM

Marked as Answer by user5605610 · Sep 27 2020

munky

Oops, yeah use Frank's - much neater. I'd keep the root_boy alias though! ;)

munky

Hi

You've marked the incorrect answer correct. It will give you the wrong results. It only goes back one level - not to the root.

Cheers

Ben

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 6 2010

Added on Aug 9 2010

9 comments

6,726 views

SQL & PL/SQL

Get Root value from a Hierarchical query

Comments

Post Details