Example: EXPAND ON For an Anchored Interval - Advanced SQL Engine - Teradata Database

This example shows the use of an anchored interval for doing anchor period and anchor point expansions. For an anchor period expansion, the expanded period value must overlap the expanding period, while for an anchor point expansion, the begin value of the expanded period value must be contained in the expanding period, which is a more restrictive condition.

First create the sold_products table.

     CREATE SET TABLE sold_products, NO FALLBACK  (
       product_id       INTEGER,
       product_price    DECIMAL(10,2),
       product_duration PERIOD(DATE))
     PRIMARY INDEX (product_id);

The sold_products table contains the following rows.

The following SELECT statement specifies an anchor period of MONTH_BEGIN. This is an anchor period expansion.

     SELECT product_id, product_price, product_duration, expd
     FROM sold_products
     EXPAND ON product_duration AS expd BY ANCHOR PERIOD MONTH_BEGIN;

The statement returns the following nine rows, with the original two rows highlighted in red:

For an anchor point expansion done on the same data, the shaded rows would not appear, as the following example shows.

The following table describes the difference between anchor point and anchor period expansions.

Submit the following SELECT statement, which differs from the previous statement only in specifying the BEGIN bound function on product_duration instead of simply specifying the column name. This is an anchor point expansion done on the same data as the previous anchor period expansion.

     SELECT product_id, product_price, product_duration, BEGIN(expd)
     FROM sold_products
     EXPAND ON product_duration AS expd BY ANCHOR MONTH_BEGIN;

This statement returns seven rows, rather than nine, with the rows shaded in red from the previous example not appearing in the result set.