All Forums Database
arpit.ubale 9 posts Joined 02/13
19 Nov 2014
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.

arpit.ubale 9 posts Joined 02/13
19 Nov 2014

I believe its something like
ALTER TABLE DWS.SALES MODIFY DROP...
But I am not getting it right. Please guide me. Thanks.

dnoeth 4628 posts Joined 11/04
19 Nov 2014

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

Dieter

arpit.ubale 9 posts Joined 02/13
20 Nov 2014

oohk. Thank you dnoeth.

You must sign in to leave a comment.