Infolinks

Saturday 12 May 2012

SQL-WALKUP TREES AND INLINE VIEW


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

Q) How to Nth Highest sal from a table?
Ans:
SQL> Select * from(Select ename,sal,dense_rank() over(order by sal desc) nth_hisal from emp)Where nth_hisal=&eval;

No comments:

Post a Comment