Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Query...... (Oracle 10g, UNIX)
Hierarchical Query...... [message #346163] |
Sat, 06 September 2008 16:32  |
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 #346165 is a reply to message #346164] |
Sat, 06 September 2008 17:16   |
dasgupta.amitava@gmail
Messages: 32 Registered: November 2007
|
Member |
|
|
Hi all,
let me explain my probleam area by the diagram below:

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.......
But I am totally lost, so please help me out.
Thanks in advance
Amitava...
|
|
|
|
|
Re: Hierarchical Query...... [message #346458 is a reply to message #346163] |
Mon, 08 September 2008 11:01   |
 |
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  |
 |
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
|
|
|
Goto Forum:
Current Time: Thu Feb 13 11:11:04 CST 2025
|