Example: SHOW for an Updatable DATE PPI Table - Teradata Vantage

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

     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.