16.20 - Example: Current and Historical Partitioning Using CURRENT_DATE in a CASE_N Expression - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

This example partitions an insurance company customer table into history and current partitions, where the history partition contains expired policies and the current partition contains 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);

Suppose that customer was created on April 17, 2010.

The output of a SHOW TABLE request displays the user-specified partitioning expression as a DATE in ANSI format.

     SHOW TABLE customer;
     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 a SHOW DML request on customer also displays the user-specified partitioning expression as a DATE in ANSI format.

     SHOW SELECT * FROM customer;
     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 a SHOW QUALIFIED DML request on customer displays the partitioning expression with CURRENT_DATE replaced by the resolved date, which is 2010-04-17 in ANSI format.

     SHOW QUALIFIED SELECT * FROM customer;
     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 '2010-04-17', NO CASE);