Friday, January 4, 2013

Retrieving a tree in Oracle database SQL

Assume that we have an Employee table with a self relation to represent that each employee has many  managers. There is a solution to retrieve the tree of all the managers for a specific employee in a hierarchical query as a tree in Oracle database SQL using the pseudocolumns. The pseudocolumns behave like a column table but not stored in the database and also are like functions but with no arguments. However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

The syntax for doing so would be:
SELECT [LEVEL] , column , expr ...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)]

So for example for employee and his bosses
SELECT ename , empno , mgr
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR mgr = empno ;

Thanks goes to Ahmed Hisham for this information :).

References:
1- Pseudocolumns in Oracle® Database SQL Reference (10.2).
2- Hierarchical Query with example.

No comments:

Post a Comment