Example: EXPAND ON and DISTINCT - Teradata Database - Teradata Vantage NewSQL Engine

This example shows how Teradata Database performs the DISTINCT operation after expansion occurs.

     CREATE SET TABLE products (
       product_id       INTEGER,
       product_price    DECIMAL(5,2),
       product_duration PERIOD(DATE))
     PRIMARY INDEX (product_id);

Assume that you have the following rows in products.

When you specify a DISTINCT operator in the query expression and the expanded column in the select list of your query, Teradata Database performs the DISTINCT operation after expansion and removes the duplicate rows from the expanded result (see the example below).

     SELECT DISTINCT product_id, pd
     FROM products
     EXPAND ON product_duration AS pd BY ANCHOR PERIOD MONTH_BEGIN;

This SELECT statement returns the following response set.