Recursive CTE: The Elegance of Simplicity in SQL – 1

Common Table Expressions (CTE) in SQL on IBM iSeries (AS/400) are a powerful and flexible tool for creating temporary, reusable queries, simplifying and improving the readability of complex SQL queries.

Below are the articles in the CTE series (including this current one):

• iSeries Sql – CTE Common Table Expressions – definitions and usage

• Recursive CTE: The Elegance of Simplicity in SQL – 1

• Recursive CTE: The Elegance of Simplicity in SQL – 2

• Recursive CTE: The Elegance of Simplicity in SQL – 3

• Recursive CTE how to concatenate the results onto a single line

Recursive Common Table Expressions (CTE) are a powerful tool in SQL that allows you to perform operations that, in the absence of this functionality, would require the development of specific programs or the use of complex loops.

These CTEs are particularly useful for working with hierarchical data, such as trees or tree structures, and for performing iterative or recursive calculations.

In this article we will see a significant example in detail relating to ‘Navigation in hierarchical structures’

Hierarchical level assignment with Recursive CTE

Description Context and Objective

This query is designed to display the organizational structure of a company in a hierarchical form. It can be used to understand reporting relationships between employees and managers and to identify the depth of the organizational hierarchy.

While the original table may contain other relevant information, for the purpose of this query, the focus is solely on Employee Identifier (ID), Employee Name (Name), and Manager Identifier (IdManagerI.

The main objective is to generate a list in which each employee (ID and Name) is associated with the hierarchical level from 0 to n, where 0 represents the top Manager or Company, 1 the employees who depend on the top Manager, and so on.

Suppose we have an archive that represents the following hierarchical structure (where the top Manager is I011):

the contents of the employee archive:

Query

WITH -- starting data
     employee (ID, Name, IdManager) AS (
         VALUES  ('I011', 'Employee_011',  NULL) , ('I021', 'Employee_021', 'I011'),
                 ('I022', 'Employee_022', 'I011'), ('I023', 'Employee_023', 'I011'),
                 ('I031', 'Employee_031', 'I022'), ('I032', 'Employee_032', 'I022')
,
                 ('I041', 'Employee_041', 'I031'), ('I042', 'Employee_042', 'I031'),
                 ('I043', 'Employee_043', 'I031'), ('I045', 'Employee_045', 'I032'),
                 ('I046', 'Employee_046', 'I032'), ('I047', 'Employee_047', 'I032')
,
                 ('I051', 'Employee_051', 'I042'), ('I052', 'Employee_052', 'I046'),
                 ('I061', 'Employee_061', 'I051')
, ('I062', 'Employee_062', 'I051'),
                 ('I063', 'Employee_063', 'I051'), ('I064', 'Employee_064', 'I052')
               , ('I065', 'Employee_065', 'I052'), ('I066', 'Employee_066', 'I052'))
  ,  hierarchy (ID, Name, Level) AS (
         SELECT ID, Name
              , 0 AS Level
           FROM employee
          WHERE IdManager IS NULL
        UNION ALL
         SELECT e.ID, e.Name, h.Level + 1
           FROM employee e
          INNER JOIN hierarchy h ON e.IDManager = h.ID
          )
SELECT * FROM hierarchy;

Result

Result table (hierarchy)

Detailed description of the query

Definition of the Employee Table:

• The employee CTE is defined as a temporary table containing three columns: ID (employee identifier), Name (employee name), and IdManager (identifier of each employee’s manager).
   
• The table is populated with a series of values ​​that represent a company organizational structure. For example, ‘I011’ is an employee with no manager (NULL), which implies that he could be a manager or a department head (top Manager).

Hierarchy construction:

• The CTE hierarchy is a recursive CTE composed of two parts: a base part and a recursive part, separated by the UNION ALL operator.
   
• Basic Part: Select employees who do not have a manager (IdManager IS NULL). These employees represent the starting point of the hierarchy (for example, company management). These are assigned a Level 0, indicating the highest level of the hierarchy.
   
• Recursive Part: Connects each employee to their manager through an INNER JOIN between the CTE employee and the hierarchy itself. At each step of the recursion, the level increases by 1 (h.Level + 1), thus representing each employee’s position in the company hierarchy.

Final Selection:

• The final query SELECT * FROM hierarchy; selects all records from the CTE hierarchy, resulting in a list of employees with their respective levels in the organizational structure.

Consideration

• Recursive CTE is an effective way to handle hierarchical or recursive data in SQL, especially when the data structure is not flat but nested or multi-level.
   
• This type of query can be particularly useful in corporate environments where the organizational structure is complex.
   
• This type of query can be particularly useful in corporate environments where the organizational structure is complex.

Please note that actual implementation and functionality may vary depending on the version of the iSeries operating system and DB2 database. It is always good practice to refer to the official IBM documentation for more precise and detailed information