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

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 Teradata Vantage™ - 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));