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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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