osden

• Oct 7th, 2007

 

select empno, ename, level from emp where connect by mgr=empno start with job='PRESIDENT';

dipanjan80

 Profile Answers by dipanjan80 

• Oct 6th, 2010

 

 

• Using LEAD analytical function its possible to get hierarchical set of data. Here is an example:

 

SELECT deptno, empno, sal, LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL, LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL FROM emp WHERE deptno IN (10, 20) ORDER BY deptno, sal DESC;

DEPTNO  EMPNO   SAL NEXT_LOWER_SAL PREV_HIGHER_SAL

------- ------ ----- -------------- ---------------

     10   7839  5000           2450               0

     10   7782  2450           1300            5000

     10   7934  1300              0            2450

     20   7788  3000           3000               0

     20   7902  3000           2975            3000

     20   7566  2975           1100            3000

     20   7876  1100            800            2975

     20   7369   800              0            1100

Art11

 Profile Answers by Art11 

• Sep 27th, 2011

 

My example is not perfect but close.

Code
SELECT deptno, job, ename, sal


 ,CURSOR(


      SELECT ename, deptno, job, sal


         , DENSE_RANK () OVER (ORDER BY sal DESC) rnk 


        , substr(Lpad(ename,Length(ename) + DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC),'-'), 1, 20) lvl


          FROM emp


         WHERE mgrs.deptno = deptno


           AND mgrs.empno <> empno


           AND mgr IS NOT NULL


         ORDER BY rnk


        ) EMPL


 FROM emp mgrs


 WHERE job = 'MANAGER'


ORDER BY 1, 3


/