15.10 - Example: Current and Historical Partitioning Using CURRENT_DATE in a CASE_N Expression - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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 QUALFIED 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);