109. 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