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 |