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