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

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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.