Introduction to Teradata Recursions

We sometimes need to join records in one table with a specified range of dates in a second table. One application that comes to my mind in the first place is to fake a snapshot history table (containing a snapshot date) based on a change history table (having timelines limited by a start date and an end date). Here is one example:

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 solution would probably look like 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;

But what should you do if you do not have permission to read from the SYS_CALENDAR.CAtable? You probably will have to create your calendar table if you are not in the lucky situation that somewhere in your Teradata Data Warehouse exists already one as standard.

I came to hate building my calendar tables for several reasons. Still, the principal reason is that each Data Warehouse I have seen until now ended up having calendar tables widely scattered across all subject areas. To make things even worse, these tables will show up in different shapes and naming conventions, making your life even harder.

Hence I started to research a solution that should not need any real table. Further, from a performance point of view, I wanted to avoid joining tens of thousands of calendar dates despite only requiring a few dates.

From my experience, you can end up with an awful performing query if Teradata is not able to apply early in the execution plan some partition elimination on the unneeded dates (This is especially valid for any Teradata Release before 13).

This was when I started to play around with recursions, and here is the result (you may probably want to transform the below SQL statement into a recursive view). My solution does not need any real table, and it only creates the range of dates you want:

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;

I am sure; you will find many other useful applications by adapting the above SQL statement. For example, I could imagine changing it to deliver only the last day of each month for a certain time range.

I leave it to you as an easy task to post any further solutions.

Conclusion:

Teradata recursions can be helpful if you require to create any ranks. Besides, the “dummy table creation” statement “SELECT * FROM (SELECT NULL AS X) X” permits achieving this without any physical tables.