15.10 - Example: Creating an Aggregate Join Index With a Nested Expression in its Select List - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Last Update
Content Type
Programming Reference
Publication ID
English (United States)

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.