15.10 - Example: Changing a CASE_N-Based Row Partitioning 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

You cannot use the ADD and DROP options to modify a partitioning expression for a table when the expression is based on a CASE_N function. For example, create the following table.

     CREATE SET TABLE hyp_prd_tbls.dps_sum, NO FALLBACK,
       NO BEFORE JOURNAL, NO AFTER JOURNAL,CHECKSUM = DEFAULT (
       hyp_month                  INTEGER NOT NULL,
       hyp_year                   INTEGER NOT NULL,
       churn_ctgry VARCHAR(30)    CHARACTER SET LATIN NOT CASESPECIFIC 
                                  NOT NULL,
       scenario VARCHAR(6)        CHARACTER SET LATIN NOT CASESPECIFIC 
                                  NOT NULL,
       in_contract_term           VARCHAR(30) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       promo_type VARCHAR(50)     CHARACTER SET LATIN NOT CASESPECIFIC 
                                  NOT NULL,
       access_tier_lvl1           VARCHAR(50) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       arpu_tier_lvl1             VARCHAR(50) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       hrchy_segmt                VARCHAR(50) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       channel_type               VARCHAR(40) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       ps_mkt_cd                  VARCHAR(6) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       tenure                     VARCHAR(12) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       tot_pplan_access_amt       DECIMAL(15,2),
       tot_feat_access_amt        DECIMAL(15,2),
       tot_occ_amt                DECIMAL(15,2),
       tot_airtime_call_amt       DECIMAL(15,2),
       churn_tot_pplan_access_amt DECIMAL(15,2),
       churn_tot_feat_access_amt  DECIMAL(15,2),
       churn_tot_occ_amt          DECIMAL(15,2),
       churn_tot_airtime_call_amt DECIMAL(15,2))
     PRIMARY INDEX (hyp_month, hyp_year, churn_ctgry, scenario,                     in_contract_term, promo_type, access_tier_lvl1,
                    arpu_tier_lvl1, hrchy_segmt, channel_type,                     ps_mkt_cd, tenure)
     PARTITION BY CASE_N(hyp_month = 200901, hyp_month = 200902,
                         hyp_month = 200903, hyp_month = 200904,
                         hyp_month = 200905, hyp_month = 200906,
                         hyp_month = 200907, hyp_month = 200908,
                         hyp_month = 200909, hyp_month = 200910,
                         hyp_month = 200911, hyp_month = 200912,
                         NO CASE);

You can only use the DROP and ADD options row partitioning expressions that are based on a RANGE function. To alter a partitioning expression based on a CASE_N function, you must use the PARTITION BY clause.

For example, to add the CASE_N options (hyp_month = 201001, hyp_month = 201002, hyp_month = 201003, hyp_month = 201004) to table hyp_prd_tbls.dps_sum, you would use the following ALTER TABLE request using the PARTITION BY clause.

     ALTER TABLE hyp_prd_tbls.dps_sum 
     PARTITION BY CASE_N(hyp_month = 200901, hyp_month = 200902,
                         hyp_month = 200903, hyp_month = 200904,
                         hyp_month = 200905, hyp_month = 200906,
                         hyp_month = 200907, hyp_month = 200908,
                         hyp_month = 200909, hyp_month = 200910,
                         hyp_month = 200911, hyp_month = 200912,
                         hyp_month = 201001, hyp_month = 201002,
                         hyp_month = 201003, hyp_month = 201004,
                         NO CASE);

The table must not contain any rows if you modify the partitioning using this method. You would have to copy the table to a new table definition, for example, using a CREATE TABLE … AS … WITH DATA request or create a new table and use an INSERT … SELECT or MERGE request to copy the rows from the old table to the new table. See “CREATE TABLE (AS Clause) in SQL Data Definition Language - Detailed Topics, B035-1184.

Suppose hyp_prd_tbls_sum was created as the following column-partitioned table.

     CREATE SET TABLE hyp_prd_tbls.dps_sum, NO FALLBACK,
            NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM=DEFAULT (
       hyp_month                  INTEGER NOT NULL,
       hyp_year                   INTEGER NOT NULL,
       churn_ctgry VARCHAR(30)    CHARACTER SET LATIN NOT CASESPECIFIC 
                                  NOT NULL,
       scenario VARCHAR(6)        CHARACTER SET LATIN NOT CASESPECIFIC 
                                  NOT NULL,
       in_contract_term           VARCHAR(30) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       promo_type VARCHAR(50)     CHARACTER SET LATIN NOT CASESPECIFIC 
                                  NOT NULL,
       access_tier_lvl1           VARCHAR(50) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       arpu_tier_lvl1             VARCHAR(50) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       hrchy_segmt                VARCHAR(50) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       channel_type               VARCHAR(40) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       ps_mkt_cd                  VARCHAR(6) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       tenure                     VARCHAR(12) CHARACTER SET LATIN 
                                  NOT CASESPECIFIC NOT NULL,
       tot_pplan_access_amt       DECIMAL(15,2),
       tot_feat_access_amt        DECIMAL(15,2),
       tot_occ_amt                DECIMAL(15,2),
       tot_airtime_call_amt       DECIMAL(15,2),
       churn_tot_pplan_access_amt DECIMAL(15,2),
       churn_tot_feat_access_amt  DECIMAL(15,2),
       churn_tot_occ_amt          DECIMAL(15,2),
       churn_tot_airtime_call_amt DECIMAL(15,2))
     NO PRIMARY INDEX
     PARTITION BY (COLUMN,
                  CASE_N(hyp_month = 200801, hyp_month = 200802,
                         hyp_month = 200803, hyp_month = 200804,
                         hyp_month = 200805, hyp_month = 200806,
                         hyp_month = 200807, hyp_month = 200808,
                         hyp_month = 200809, hyp_month = 200810,
                         hyp_month = 200811, hyp_month = 200812,
                         NO CASE);

The same rules for using the DROP and ADD options for row partitioning based on a RANGE_N or CASE_N function still apply.

For example, to modify the existing row partitioning expression in a column-partitioned version of hyp_prd_tbls.dps_sum with the second level row partitioning based on a CASE_N function to add the CASE_N partitioning (hyp_month = 201001, hyp_month = 201002, hyp_month  = 201003, hyp_month = 201004) to table hyp_prd_tbls.dps_sum, you would use the following ALTER TABLE request.

     ALTER TABLE hyp_prd_tbls.dps_sum 
     MODIFY
     PARTITION BY (COLUMN,
                   CASE_N(hyp_month = 200901, hyp_month = 200902,
                          hyp_month = 200903, hyp_month = 200904,
                          hyp_month = 200905, hyp_month = 200906,
                          hyp_month = 200907, hyp_month = 200908,
                          hyp_month = 200909, hyp_month = 200910,
                          hyp_month = 200911, hyp_month = 200912,
                          hyp_month = 201001, hyp_month = 201002,
                          hyp_month = 201003, hyp_month = 201004,
                          NO CASE));