Recursive CTE: The Elegance of Simplicity in SQL – 2

Common Table Expressions (CTE) in SQL on IBM iSeries (AS/400) are a powerful and versatile tool for processing temporary, reusable queries. They help simplify and increase the clarity of complex SQL queries, making them more manageable and easier to understand.

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

In particular, Recursive Common Table Expressions (CTE) are a powerful tool in SQL that allows you to perform operations which, 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 related to ‘Generating a list of dates’ (within a date range).

Recursive CTE to Generate Date List

Description Context and Objective

This query is designed to generate a list of dates, within a predefined range, associating each date with the name of the corresponding day of the week and the progressive number in the range. If the date range consists of January 1st to December 31st of a year, the dates for the entire year are generated.

Query

WITH 
   DayNames (DayNumber, DayName) AS (
       VALUES (1, 'Sunday'),   (2, 'Monday'),   (3, 'Tuesday'), 
              (4, 'Wednesday'), (5, 'Thursday'), (6, 'Friday'), 
              (7, 'Saturday')
       )
 , RangeDate (DateFrom, DateTo) AS (
       VALUES (CAST('2024-01-01' AS DATE), CAST('2024-12-31' AS DATE))
       )
 , DateLog (Date, Day, Progr) AS (
       SELECT (SELECT DateFrom From RangeDate) AS Date
            , (SELECT DayName FROM DayNames WHERE DayNumber =
                      (DAYOFWEEK((SELECT DateFrom From RangeDate)))) AS Day
            , 1 AS Progr
         FROM SYSIBM.SYSDUMMY1
        UNION ALL
       SELECT Date  + 1 DAYS AS Date
            , (SELECT DayName FROM DayNames WHERE DayNumber =
                      (DAYOFWEEK(Date + 1 DAYS))) AS Day
            , 1 + Progr AS Progr
         FROM DateLog
        WHERE Date < (SELECT DateTo From RangeDate)
       )
SELECT * FROM DateLog;

Result

Detailed description of the query

The query is composed of three main CTEs

CTE DayNames:

• This CTE creates a simple table of correspondence between the numbers of the days of the week (1-7) and their names (from ‘Sunday’ to ‘Saturday’).

CTE RangeDate:

• This CTE defines the date range you want to generate, in this case, from January 1, 2024 (DateFrom) to December 31, 2024 (DateTo).

Recursive CTE DataLog:

• This CTE is the heart of the query. Use recursion to generate a list of dates, starting on January 1, 2024 and ending on December 31, 2024.
   
• The first element of the CTE (anchor member) establishes the starting point: the initial date (January 1, 2024), the name of the corresponding day obtained via a subquery with the DayNames CTE, and a running counter (initialized to 1). 
• The second element (recursive member) adds a day to the current date on each iteration, determines the day name for the new date (again via a subquery with DayNames), and increments the rolling counter. This process continues until the final date, December 31, 2024, is reached.

The Final Query

• The final query simply selects all rows from the DateLog CTE. This list includes every day of 2024, with the corresponding day of the week name and a running counter representing the number of days since the beginning of the year.

Key Points and Considerations

• Using Recursive CTE: The DateLog CTE is a classic example of using recursive CTE to generate a sequential list of values ​​- in this case, a calendar for a year.

• Subquery with DayNames: In both members of the DateLog CTE, there is a subquery that connects to the DayNames CTE to get the name of the day of the week. This shows how CTEs can be interconnected to perform complex calculations efficiently.

• Recursion Limitation: The WHERE clause in the recursive member of the DateLog CTE ensures that the recursion stops at the end of the year 2024, preventing an infinite loop.

• Performance and Limitations: Recursive CTEs can be demanding in terms of performance, especially for large date ranges. Additionally, DB2 has a limit on the number of recursive iterations allowed in a CTE, so it is important to ensure that the query does not exceed this limit. In some DB2 configurations, this limit is set to 100 by default, but can be increased (for example, up to 1000 or more) if necessary.