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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.

product_id

----------

product_price

-------------

product_duration

----------------

1000 100.00 2007-02-15, 2007-08-11
1001 99.99 2007-03-04, 2007-05-01
1001 101.10 2008-05-10, 2009-05-10
1001 1-4.10 2007-07-16, 2008-10-09

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.

product_id pd
1000 2007-02-01, 2007-03-01
1000 2007-03-01, 2007-04-01
1000 2007-04-01, 2007-05-01
1000 2007-05-01, 2007-06-01
1000 2007-06-01, 2007-07-01
1000 2007-07-01, 2007-08-01
1000 2007-08-01, 2007-09-01
1001 2007-03-01, 2007-04-01
1001 2007-04-01, 2007-05-01
1001 2008-05-01, 2008-06-01
1001 2008-06-01, 2008-07-01
1001 2008-07-01, 2008-08-01
1001 2008-08-01, 2008-09-01
1001 2008-09-01, 2008-10-01
1001 2008-10-01, 2008-11-01
1001 2008-11-01, 2008-12-01
1001 2008-12-01, 2009-01-01
1001 2009-01-01, 2008-02-01
1001 2009-02-01, 2009-03-01
1001 2009-03-01, 2009-04-01
1001 2009-04-01, 2009-05-01
1001 2009-05-01, 2009-06-01
1001 2007-07-01, 2007-08-01
1001 2007-08-01, 2007-09-01
1001 2007-09-01, 2007-10-01
1001 2007-10-01, 2007-11-01
1001 2007-11-01, 2007-12-01
1001 2007-12-01, 2008-01-01
1001 2008-01-01, 2008-02-01
1001 2008-02-01, 2008-03-01
1001 2008-03-01, 2008-04-01
1001 2008-04-01, 2008-05-01