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,
CREATE TABLE Change_History
PK INTEGER NOT NULL,
The proposed solution would resemble this:
SELECT t01.PK, t01.ATTRIB, t02, table?CALENDAR_DATE AS SNAPSHOT_DATE
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
( SELECT * FROM (SELECT NULL AS X) X) DUMMYTABLE
CALENDAR_DATE + INTERVAL '1' DAY
CALENDAR_DATE < CURRENT_DATE
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.
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.