Example: Expansion Over a UNION Operator - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.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, Teradata Database first expands the rows in tables tdate and tdate1 and then unions the resulting rows from the queries on the expanded results.

Teradata 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.

Teradata 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