An Introduction to Teradata Recursions: Generating Date Ranges Without a Physical Table

 Introduction to Teradata Recursions

To link records from a primary table with a specific date range from a secondary table, a common requirement is to create a simulated history table with snapshot dates using a change history table with defined timelines. Consider the following illustration:

CREATE TABLE Snapshot_History
(
PK INTEGER NOT NULL,
ATTRIB CHAR(10),
SNAPSHOT_DATE DATE
);

CREATE TABLE Change_History
(
PK INTEGER NOT NULL,
ATTRIB CHAR(10),
START_DATE DATE,
END_DATE DATE
);

The proposed solution would resemble this:

SELECT  t01.PK, t01.ATTRIB, t02, table?CALENDAR_DATE AS SNAPSHOT_DATE
FROM
Change_History t01
INNER JOIN
SYS_CALENDAR.CALENDAR t02
WHERE
t02.CALENDAR_DATE BETWEEN START_DATE AND END_DATE;

You must create your own calendar table if you lack permission to access the SYS_CALENDAR table. Unless a standard table already exists within your Teradata Data Warehouse, this is likely your only option.

I despise creating calendar tables for various reasons, primarily because every Data Warehouse I’ve encountered has scattered them throughout various subject areas. These tables exhibit varying shapes and naming conventions to exacerbate matters, rendering the task even more difficult.

Therefore, I began investigating a solution that didn’t require an actual table. Additionally, I aimed to optimize performance by minimizing the need to join numerous calendar dates, as I only required a handful.

In my experience, if Teradata cannot apply partition elimination to unnecessary dates early in the execution plan, the query performance can suffer greatly. This is particularly true for Teradata Releases before 13.

Upon experimenting with recursions, I have achieved the following result. Please refer to the below to convert the following SQL statement into a recursive view. My solution generates the desired date range without utilizing an actual table. Revised: After experimenting with recursions, I have produced the following solution. To create a recursive view from the below SQL statement, please reference it accordingly. My solution generates the desired date range without using a physical table.

WITH RECURSIVE DateRanges(CALENDAR_DATE) AS
(
SELECT DATE'2014-04-01' AS CALENDAR_DATE
FROM
( SELECT * FROM (SELECT NULL AS X) X) DUMMYTABLE
UNION ALL
SELECT
CALENDAR_DATE + INTERVAL '1' DAY
FROM
DateRanges
WHERE
CALENDAR_DATE < CURRENT_DATE
)
SELECT *
FROM DateRanges;

You can find numerous beneficial applications by modifying the previous SQL statement. For instance, I suggest altering it to retrieve solely the final day of every month within a specified timeframe.

It is easy for you to post additional solutions.

Conclusion:

Teradata recursion can facilitate the creation of ranks without needing physical tables by utilizing the “SELECT * FROM (SELECT NULL AS X) X” statement for dummy table creation.