In a previous article, Roland explained how to build a data range without touching a physical table by using recursion. This time, we explore an alternative approach that avoids recursion at the expense of single-AMP, single-row access. We can use this approach to build both a wide date range as well as a narrow one. We can also go beyond the limits of the Teradata SYS_CALENDAR table. The latter ends on 2100-12-31, which might be closer than we think :)).

With no further ado, here is the template query building atop EXPAND ON clause, a Teradata extension to the ANSI SQL:2011 standard.

FROM dbc.dbcinfov WHERE infokey = 'VERSION'
EXPAND ON Period(DATE '2101-01-01', DATE '2101-02-01') AS thePeriod BY ANCHOR PERIOD DAY;

The execution plan confirms the promise of single-AMP, single-row access. Teradata efficiently calculates the equivalent of an SQL recursive statement internally.

teradata sys_calendar

Using this template query as a starting point, one can adjust the EXPAND ON parameters to efficiently extract more fine periods (e.g., the first day of the month or specific weekdays) and better serve the needs of various business calendars or dates ranges.

  • Avatar
    Nikita Popkov says:

    Hello! There is a trick to do this without table access at all.
    Normally, it is prohibited to do EXPAND without volatiletempperm table reference, even if there is pseudo-table, so normally CTEs won’t help.
    But if we add any non-syntax sugar function anywhere in the request, it, suddenly, works. Like this:

    (SELECT PERIOD(Trunc(DATE ‘2020-09-01’, ‘DD’), Last_Day(DATE ‘2020-10-01’) + 1) AS prd)
    SELECT prd_ex FROM CTE t
    EXPAND ON prd AS prd_ex;


    (SELECT PERIOD(DATE ‘2020-09-01’, DATE ‘2020-10-31’ + 1) AS prd
    ,Greatest(1,1) AS Fake)
    SELECT prd_ex FROM CTE t
    EXPAND ON prd AS prd_ex;

    Or this:

    SELECT prd_ex
    FROM (SELECT 1 AS A) t
    WHERE Nvl(1,1) = 1
    EXPAND ON PERIOD(DATE ‘2020-09-01’, DATE ‘2020-10-31’ + 1) AS prd_ex;

  • Thank you, Artemios! Very useful 🙂

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like