Dropping and creating case_n partition

Database
Enthusiast

Dropping and creating case_n partition

Hi,

Please help me get the syntax for droping a creating case_n partition on a given table.

The table structure for the same is given below.

CREATE MULTISET TABLE DWS.SALES,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CTRY_CD VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL COMPRESS ('CA','GB','IN','MX','RU','SA','TR','US'),
SYS_ID INTEGER NOT NULL,
CUST_GTMU_ID INTEGER,
CO_CDV VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL DEFAULT 'NA',
DW_CUST_ID BIGINT NOT NULL,
DW_INVC_ID BIGINT NOT NULL,
SLS_ID_NUM VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
DW_ITEM_ID BIGINT NOT NULL,
INVC_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
SLS_DTM TIMESTAMP(0) NOT NULL,
LN_NUM INTEGER NOT NULL,
DW_SLS_ACTVTY_CDV VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL COMPRESS ('SLS','RTRN'),
TX_TYP_CD VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL COMPRESS ('01','02','03','04','05','06','07','08','09','10'),
CCY_CD VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ('CAD','GBP','INR','MXN','RUB','TRY','USD'),
FINCL_CLS_DT DATE FORMAT 'YYYY-MM-DD',
GDW_UOM_CD VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL COMPRESS ('EA','CA','KG','BX','GA','LB'),
INVC_LN_QTY DECIMAL(18,4) COMPRESS (0.0000 ,16.0000 ,1.0000 ,2.0000 ,3.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,8.0000 ,24.0000 ,9.0000 ,10.0000 ,12.0000 ,13.0000 ,-2.0000 ,-1.0000 ),
UPRC DECIMAL(18,4) COMPRESS (0.0000 ,0.7200 ,3.0000 ,2.8000 ,3.0100 ,3.6500 ,2.9600 ,3.1400 ,3.2700 ,1.5300 ,1.0700 ),
NET_UNIT_PRC DECIMAL(18,4) COMPRESS 0.0000 ,
INVC_LN_AMT DECIMAL(18,4) COMPRESS 0.0000 ,
NET_DSCNT_AMT DECIMAL(18,4) COMPRESS 0.0000 ,
ALW_PER_UNIT_AMT DECIMAL(18,4) COMPRESS 0.0000 ,
ALW_AMT DECIMAL(18,4) COMPRESS 0.0000 ,
TAX_AMT DECIMAL(18,4) COMPRESS 0.0000 ,
TOT_8OZ_CASES_QTY DECIMAL(18,4) COMPRESS 0.0000 ,
KG_GRSS_WGHT_MEAS DECIMAL(18,4) COMPRESS 0.0000 ,
LOCL_DPST_PER_UNIT_AMT DECIMAL(18,4) COMPRESS 0.0000 ,
ST_DPST_PER_UNIT_AMT DECIMAL(18,4) COMPRESS 0.0000 ,
DW_CRTD_DTM TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
DW_UPDT_DTM TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
DW_STEP_ID DECIMAL(18,0),
DW_BTCH_ID DECIMAL(18,0))
PRIMARY INDEX NUPI_1_SLS ( DW_CUST_ID )
PARTITION BY CASE_N(
CO_CDV = 'CA140',
CO_CDV = 'CA196',
CO_CDV = 'RU166',
CO_CDV = 'RU167',
CO_CDV = 'RU168',
CO_CDV = 'GB169',
CO_CDV = 'SA169',
CO_CDV = 'TR170',
CO_CDV = 'TR171',
CO_CDV = 'TR172',
CO_CDV = 'MX174',
CO_CDV = 'IN175',
CO_CDV = 'US181',
NO CASE OR UNKNOWN);

Thanks in advace.

3 REPLIES
Enthusiast

Re: Dropping and creating case_n partition

I believe its something like

ALTER TABLE DWS.SALES MODIFY DROP...

But I am not getting it right. Please guide me. Thanks.

Junior Contributor

Re: Dropping and creating case_n partition

There's no way to drop/add a partition to an existing CASE_N, only RANGE_N allows that.

Enthusiast

Re: Dropping and creating case_n partition

oohk. Thank you dnoeth.