Example: Creating a Time Series Using Expansion By an Interval Constant Value - Advanced SQL Engine - Teradata Database

For additional examples of using the EXPAND ON clause in SELECT statements, see EXPAND ON Clause.

Suppose you create a table named employee with this definition.

     CREATE SET TABLE employee (
       emp_id       INTEGER,
       emp_name     CHARACTER(20) CHARACTER SET LATIN NOT CASESPECIFIC,
       job_duration PERIOD(DATE))
     PRIMARY INDEX (emp_id);

You insert 3 rows into the employee table:

1001

Xavier

2002-01-10, 9999-12-31

1002

Ricci

2007-07-01, 9999-12-31

1003

Charles

2006-02-10, 2008-06-01

When you specify an EXPAND ON clause using an interval constant, the database expands each row based on the specified interval value, and the duration in the expanded period is the interval value.

So you now use an EXPAND ON clause to retrieve the employee details specifying an interval constant period.

     SELECT emp_id, emp_name, job_duration AS tsp
     FROM employee
     EXPAND ON job_duration AS tsp BY INTERVAL '1' YEAR
       FOR PERIOD(DATE '2006-01-01', DATE '2008-01-01');

The database returns employee details for each year of a given period, as you specified in the FOR clause of the SELECT statement.

The database returns a warning to the requestor that some rows in the expanded result might have an expanded period duration that is less than the duration of the specified interval.