Example: SHOW for an Updatable DATE PPI Table - Advanced SQL Engine - Teradata Database

Suppose you create the following insurance customer table that is partitioned into historical (expired) policies and current policies.

     CREATE TABLE customer (
       cust_name              CHARACTER(8),
       policy_number          INTEGER,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX (cust_name, policy_number)
     PARTITION BY CASE_N(policy_expiration_date>=CURRENT_DATE, NO CASE);

You then submit a SHOW DML request for the following SELECT request against customer.

     SHOW SELECT * 
          FROM customer;

The request returns the following DDL.

     CREATE SET TABLE MOVEDATE.customer ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           cust_name CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC,
           policy_number INTEGER,
           policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX ( cust_name ,policy_number )
     PARTITION BY CASE_N(policy_expiration_date >= DATE, NO CASE);

The output of this report displays the original CURRENT_DATE expression as a DATE expression.

You then submit a qualified SHOW DML request for the following SELECT request against customer.

     SHOW QUALIFIED SELECT * 
                    FROM customer;

The request returns the following DDL.

     CREATE SET TABLE MOVEDATE.customer ,NO FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           cust_name CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
           policy_number INTEGER,
           policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX ( cust_name ,policy_number )
     PARTITION BY CASE_N(
       policy_expiration_date >= DATE '2007-04-17', NO CASE);

The output of this report displays the partitioning expression with the original CURRENT_DATE expression replaced by the resolved date.