Example: Creating a Join Index With a Partitioned PPI Using a CURRENT_DATE Function - Advanced SQL Engine - Teradata Database

Suppose you create the following table.

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX (cust_no) 
     PARTITION BY CASE_N(policy_expiration_date >= CURRENT_DATE,
                         policy_expiration_date <  CURRENT_DATE 
                     AND policy_expiration_date >= CURRENT_DATE-
                                                   INTERVAL '3' MONTH);

This example defines a PPI sparse join index named j_sales on customer to contain only the data of the current quarter:

     CREATE JOIN INDEX j_sales AS 
       SELECT * 
       FROM sales 
       WHERE sale_amt>=2000.00
     PRIMARY INDEX (store_ID)
     PARTITION BY CASE_N(sale_date>=CURRENT_DATE, NO CASE);