17.10 - Example: Current and Historical Partitioning Using CURRENT_DATE in a CASE_N Expression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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