Example: Expansion Over a UNION Operator - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Suppose you create a table named tdate1 with the following definition.

          CREATE SET TABLE tdate1 (
       id       INTEGER,
       quantity INTEGER,
       pd       PERIOD(DATE))
     PRIMARY INDEX (id);

Table tdate1 contains the following rows.

id quantity pd
12 120 2006-02-03, 2006-06-20
13 130 2005-01-03, 2005-05-20

You now submit the following unioned SELECT statement against tdate, as defined in Example: Expansion on an Anchor Point Using WEEK_DAY, and tdate1 that specifies an EXPAND ON clause for both statements, each having a one month interval granularity.

     SELECT id, quantity, expd
     FROM tdate
     EXPAND ON pd AS expd BY INTERVAL '1' MONTH
     UNION
     SELECT id, quantity, expd
     FROM tdate1
     EXPAND ON pd AS expd BY INTERVAL '1' MONTH;

In this example, the database first expands the rows in tables tdate and tdate1 and then unions the resulting rows from the queries on the expanded results.

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

The database returns tdate1 details for each month of a given period for these two queries and then unions the result rows as follows.

id quantity expd
11 110 2005-02-03, 2005-03-03
11 110 2005-03-03, 2005-04-03
11 110 2005-04-03, 2005-05-03
11 110 2005-05-03, 2005-06-03
11 110 2005-06-03, 2005-06-20
10 100 2004-01-03, 2004-02-03
10 100 2004-02-03, 2004-03-03
10 100 2004-03-03, 2004-04-03
10 100 2004-04-03, 2004-05-03
10 100 2004-05-03, 2004-05-20
12 120 2006-02-03, 2006-03-03
12 120 2006-03-03, 2006-04-03
12 120 2006-04-03, 2006-05-03
12 120 2006-05-03, 2006-06-03
12 120 2006-06-03, 2006-06-20
13 130 2005-01-03, 2005-02-03
13 130 2005-02-03, 2005-03-03
13 130 2005-03-03, 2005-04-03
13 130 2005-04-03, 2005-05-03
13 130 2005-05-03, 2005-05-20