Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Query...... (Oracle 10g, UNIX)
icon5.gif  Hierarchical Query...... [message #346163] Sat, 06 September 2008 16:32 Go to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Hi all,
I need to write a qry using the normal employee table, where there is a parent-child relationship. I need to find the names of all employees and their managers(may not be immediate, but must be in the same path upto root), having same salary. That means any node and it's any ancestor having same salary.

Thanks in advance........
Amitava...
Re: Hierarchical Query...... [message #346164 is a reply to message #346163] Sat, 06 September 2008 16:34 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

http://asktom.oracle.com has many fine coding examples.

Should this post be moved to the homework subforum below?
Re: Hierarchical Query...... [message #346165 is a reply to message #346164] Sat, 06 September 2008 17:16 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Hi all,
let me explain my probleam area by the diagram below:

file:\\C:\Documents and Settings\amdasgupta\My Documents\My Pictures\tree.bmp

Consider the tree above(nodes A1..A13). I need to find out the node pairs like--(A1, A9, both having values 11),
(A2, A8, both having values 5), etc...

I do not care for the node pairs like--(A3, A10, as they are not in diff. path, though they have same value 100).

"anacedent" thinks this qry is very very simple....... Sad Confused
But I am totally lost, so please help me out.

Thanks in advance
Amitava...
Re: Hierarchical Query...... [message #346166 is a reply to message #346163] Sat, 06 September 2008 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>But I am totally lost
Now this make two of us who are lost.

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: Hierarchical Query...... [message #346175 is a reply to message #346165] Sun, 07 September 2008 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no "diagram below".

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Hierarchical Query...... [message #346458 is a reply to message #346163] Mon, 08 September 2008 11:01 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
This one may be good

Processing ...
select sal,substr(wm_concat(sys_connect_by_path(ename,'->')),3)
from scott.emp
connect by ( prior empno = mgr )
group by sal
Query finished, retrieving results...
    SAL                  SUBSTR(WM_CONCAT(SYS_CONNECT_BY_PATH(ENAME,'->')),3)               
---------- -------------------------------------------------------------------------------- 
       800 FORD->SMITH,->JONES->FORD->SMITH,->SMITH,->KING->JONES->FORD->SMITH              
       950 JAMES,->BLAKE->JAMES,->KING->BLAKE->JAMES                                        
      1100 SCOTT->ADAMS,->KING->JONES->SCOTT->ADAMS,->JONES->SCOTT->ADAMS,->ADAMS           
      1250 WARD,->KING->BLAKE->MARTIN,->KING->BLAKE->WARD,->BLAKE->MARTIN,->MARTIN,->BLAKE--
           >WARD                                                                            
      1300 MILLER,->CLARK->MILLER,->KING->CLARK->MILLER                                     
      1500 TURNER,->BLAKE->TURNER,->KING->BLAKE->TURNER                                     
      1600 ALLEN,->KING->BLAKE->ALLEN,->BLAKE->ALLEN                                        
      2450 CLARK,->KING->CLARK                                                              
      2850 BLAKE,->KING->BLAKE                                                              
      2975 JONES,->KING->JONES                                                              
      3000 SCOTT,->JONES->SCOTT,->KING->JONES->SCOTT,->KING->JONES->FORD,->JONES->FORD,->FO-
           RD                                                                               
      5000 KING                                                                             

12 row(s) retrieved



Bye Alessandro
Re: Hierarchical Query...... [message #347307 is a reply to message #346458] Thu, 11 September 2008 04:56 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
just a correction.

Processing ...
select sal,wm_concat(managers)
from (
		select sal,
			empno, 
			managers,
			lvl,
			max(lvl) over (partition by empno) mlvl
		from (
				select sal,
					empno,
					substr(sys_connect_by_path(ename,'->'),3) as managers,
					level as lvl
				from scott.emp a
				connect by ( prior empno = mgr )
			)
	)
where lvl = mlvl
group by sal
Query finished, retrieving results...
    SAL                                   WM_CONCAT(MANAGERS)                               
---------- -------------------------------------------------------------------------------- 
       800 KING->JONES->FORD->SMITH                                                         
       950 KING->BLAKE->JAMES                                                               
      1100 KING->JONES->SCOTT->ADAMS                                                        
      1250 KING->BLAKE->WARD,KING->BLAKE->MARTIN                                            
      1300 KING->CLARK->MILLER                                                              
      1500 KING->BLAKE->TURNER                                                              
      1600 KING->BLAKE->ALLEN                                                               
      2450 KING->CLARK                                                                      
      2850 KING->BLAKE                                                                      
      2975 KING->JONES                                                                      
      3000 KING->JONES->SCOTT,KING->JONES->FORD                                             
      5000 KING                                                                             

12 row(s) retrieved


Bye Alessandro
Previous Topic: Pls Explain the Difference (merged)
Next Topic: trying to find solution!
Goto Forum:
  


Current Time: Mon Dec 05 21:24:35 CST 2016

Total time taken to generate the page: 0.08353 seconds