WALKUP TREES AND INLINE VIEW
WALKUP TREES
Using
hierarchical queries, you can retrieve data based on a natural hierarchical
relationship between rows in a table. However, where a hierarchical
relationship exists between the rows of a table, a process called tree walking
enables the hierarchy to be constructed.
Ex:
SQL> select ename
|| '==>' || prior ename, level from
emp start with ename = 'KING'
connect by prior empno=mgr;
ENAME||'==>'||PRIORENAM LEVEL
------------------------------------ --------
KING==> 1
JONES==>KING 2
SCOTT==>JONES 3
ADAMS==>SCOTT 4
FORD==>JONES 3
SMITH==>FORD 4
BLAKE==>KING 2
ALLEN==>BLAKE 3
WARD==>BLAKE 3
MARTIN==>BLAKE 3
TURNER==>BLAKE 3
JAMES==>BLAKE 3
CLARK==>KING 2
MILLER==>CLARK
3
In the above
Start with clause specifies the root row
of the table.
Level pseudo column gives the 1 for root
, 2 for child and so on.
Connect by prior clause specifies the
columns which has parent-child relationship.
INLINE VIEW OR TOP-N ANALYSIS
In the select statement instead of table
name, replacing the select statement is known as inline view.
Ex:
SQL>
Select ename, sal, rownum rank from (select *from emp order by
sal);
ENAME SAL RANK
----------
---------- ----------
SMITH 800 1
JAMES 950 2
ADAMS 1100 3
WARD 1250 4
MARTIN 1250 5
MILLER 1300 6
TURNER 1500 7
ALLEN 1600 8
CLARK 2450 9
BLAKE 2850 10
JONES 2975 11
SCOTT 3000 12
FORD 3000 13
KING 5000 14
No comments:
Post a Comment