Tree-Structured Queries

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Calibri;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\li720\lang1033\f0\fs22 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.\par
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for testing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss. \par
\par
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example: \par
\par
Select LEVEL, EMPNO, ENAME, MGR\par
From EMP\par
Connect by prior EMPNO = MGR\par
Start with MGR is NULL;\par
\pard\par
\pard\li720 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: \par
Select lpad(' ', LEVEL * 2) || ENAME ........\par
\par
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. \par
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. \par
\pard\par
\pard\sa200\sl276\slmult1\lang9\f1\par
}