Example: Expansion Over a UNION Operator - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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