Hierarchical tree in form 6i [message #573233] |
Mon, 24 December 2012 10:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
irfankundi786@yahoo.com
Messages: 269 Registered: February 2009 Location: pakistan
|
Senior Member |
|
|
Hierarchical Tree in oracle form 6i ...
i am trying to build tree in form 6i but it give error
SELECT 1,
level,
job
,
'',
ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
order by level
i want to show job wise employees ...
|
|
|
|
|
|
Re: Hierarchical tree in form 6i [message #573251 is a reply to message #573248] |
Tue, 25 December 2012 05:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
or:
SQL> select decode(grouping(ename),
2 0, ' '||ename,
3 job||': '||count(*)||' employees') data
4 from emp
5 group by rollup(job, ename)
6 having grouping(job) = 0
7 order by job, decode(grouping(ename), 1, ' ', ename)
8 /
DATA
-------------------------------------------------------------
ANALYST: 2 employees
FORD
SCOTT
CLERK: 4 employees
ADAMS
JAMES
MILLER
SMITH
MANAGER: 3 employees
BLAKE
CLARK
JONES
PRESIDENT: 1 employees
KING
SALESMAN: 4 employees
ALLEN
MARTIN
TURNER
WARD
Regards
Michel
|
|
|
|