11.9 How do I…Traverse a tree structure? Problem

In my application, I have a table containing data in a hierarchical structure. I need to create a query that navigates the tree structure to create a report. I have not been able to generate a simple query to traverse a tree structure. How do I traverse a tree structure in a query?

Technique

The CONNECT BY clause in a query provides support for hierarchical data structures. Unfortunately, using the CONNECT BY clause can be confusing. Navigating a tree structure requires two clauses. The START WITH clause identifies where the query begins in the tree structure. The CONNECT BY PRIOR clause identifies the parent/child relationship between key fields. The format of the query is shown here:

SELECT fields

FROM table

START WITH column = value

CONNECT BY PRIOR parent primary key = child foreign key;

An additional WHERE clause or an ORDER BY clause is not allowed by Oracle in a query using the CONNECT BY structure.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP11_24.SQL, shown in Figure 11.17, creates the sample table and data used in this How-To.

The sample table contains names of employees for an organization. Each record contains an EMP_NO column to uniquely identify each employee and a MGR_NO column to identify his or her manager. If the MGR_NO column is NULL, the employee is at the top of the corporate structure. Run the file to create the sample table and data.

SQL> START CHP11_24.SQL

Table dropped.

Table created.

2. Load CH11_25.SQL into the SQL buffer. The file contains a query of the sample table for the hierarchical structure of the data.

SQL> GET CHP11_25.sql

1 SELECT

2 LPAD(‘ ‘,4*(LEVEL-1))||EMP_NAME EMP_NAME

3 FROM

4 EMP11

5 START WITH MGR_NO IS NULL

6* CONNECT BY PRIOR EMP_NO = MGR_NO

Line 2 uses the LEVEL pseudo-column to left-pad the EMP_NAME column with four spaces for each level of the hierarchy. The START WITH clause in line 5 begins the tree navigation for records in which the MGR_NO column is NULL. Because a NULL MGR_NO column represents an employee at the top of the corporate structure, you want the tree navigation to start there. The CONNECT BY PRIOR clause identifies that the EMP_NO column of the previous level joins to the MGR_NO column from the next level down.

3. Run the statement to display the results of the query. The results of the query are shown in Figure 11.18.

The query used the LEVEL pseudo-column to indent the results of the query and display the records in the tree structure. Because the JONES, TOM record contained no MGR_NO column, the query results begin with that column. The CONNECT BY PRIOR clause causes the tree to be navigated beginning at the record.

How It Works

Step 1 creates the sample table and records used in this How-To. Steps 2 and 3 present a query using the START WITH and CONNECT BY PRIOR clauses to navigate the data’s hierarchical structure. The START WITH clause identifies the root of the tree structure and the CONNECT BY PRIOR clause identifies the columns linking the hierarchy. The LEVEL pseudo-column is used in the select list to indent the results based on the level of the tree structure.

Comments

A hierarchical structure of data is definitely not easy to work with. The CONNECT BY PRIOR clause in a query enables Oracle to perform this task. Remember to include the START WITH clause, and also keep in mind that the ordering of the columns in the CONNECT BY prior clause is important.