SQL SERVER 2008 – How to code a tree-structured query

Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.

The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the “current” employee’s boss.

The LEVEL pseudo-column is an indication of how deep in the tree one is. SQL SERVER can handle queries with a depth of up to 255 levels. Look at this example:

select  LEVEL, EMPNO, ENAME, MGR

from  EMP

connect by prior EMPNO = MGR

start with MGR is NULL;

One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:

select lpad(‘ ‘, LEVEL * 2) || ENAME ……..

One uses the “start with” clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a “connect by prior” clause is that you cannot perform a join to other tables. The “connect by prior” clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.

One way of working around this is to use PL/SQL, open the driving cursor with the “connect by prior” statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

2 thoughts on “SQL SERVER 2008 – How to code a tree-structured query

  1. SELECT empId, LEVEL
    FROM Expemp
    START WITH empId = @empId
    CONNECT BY PRIOR empId = entryId

    Can any one please help me to convert this oracle into sql query.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s