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