Example: Expansion over a UNION Operator - Teradata VantageCloud Lake

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.

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 rows in the expanded result may 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.