17.00 - Example: Creating an Aggregate Join Index With a Nested Expression in its Select List - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

This example creates an aggregate join index with a nested expression in its select list.

     CREATE JOIN INDEX aji_extract_begin AS
       SELECT EXTRACT(MONTH FROM BEGIN(d1)) AS mth, SUM(b1) AS sumb 
       FROM t1 
       GROUP BY 1;

If you then submit a HELP JOIN INDEX request on aji_extract_begin, the system returns a report like the following:

     HELP JOIN INDEX aji_extract_begin;
Column Name                Type      Comment
-------------------------- --------- ---------------------
CountStar                  F         ?
mth                        I         ?
sumb                       F         ?

Similarly to Example: Creating a Join Index With a BEGIN Bound Function in its Select List and Example: Creating a Join Index with a Multicolumn CASE Expression in its Select List, column mth in this example can be used to map identical query expressions to the join index.