Walk up trees and Inline views

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