Write sample code that can create a hierachical set of data without using a start with and connect by clause in PL/SQL

soorajsk_84
Profile Answers by soorajsk_84 Questions by soorajsk_84
Aug 9th, 2007
3
4218

Questions by soorajsk_84 answers by soorajsk_84

Showing Answers 1 - 3 of 3 Answers

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