Example: EXPAND ON and DISTINCT - Advanced SQL Engine - Teradata Database

This example shows how Vantage 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, the 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.