/* 1. Create new table */

SELECT 1 FROM dbc.tables WHERE TableName = 'C0160000010008_CARD_new' AND databasename = 'SANDBOX';

.IF ActivityCount = 1 THEN .GOTO Rename_

CREATE MULTISET TABLE SANDBOX.C0160000010008_CARD_new ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      LAST_MODIFIED_DT DATE Format 'YYYY-MM-DD',
      CARD_ID_W4 BIGINT NOT NULL,
      CARD_NUMBER VARCHAR(64) CHARACTER SET Unicode NOT CaseSpecific,
      CONTRACT_ID_W4 BIGINT,
      CARD_CLIENT_ID_W4 BIGINT,
      PRODUCT_ID_W4 VARCHAR(32) CHARACTER SET Unicode NOT CaseSpecific,
      STATUS_ID BIGINT,
      CARD_OPEN_DATE_W4 DATE Format 'YYYY-MM-DD',
      CARD_EXPIRE VARCHAR(4) CHARACTER SET Unicode NOT CaseSpecific,
      CARD_HOLDER VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      ISSUE_NUMBER VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      OSB_TB_NUMBER VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      OSB_NUMBER VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      CARD_VSP_NUMBER VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      ADD_INFO VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      OSB_ID BIGINT,
      MOBILE_IND VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      ADD_INFO_02 VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      NFC_CARD CHAR(1) CHARACTER SET Unicode NOT CaseSpecific,
      INFO_SYSTEM_TYPE_CD CHAR(3) CHARACTER SET Unicode NOT CaseSpecific NOT NULL Compress '016',
      INFO_SYSTEM_INST_CD CHAR(6) CHARACTER SET Unicode NOT CaseSpecific NOT NULL Compress ('000001','130000','130500','400000','400500','540000','540500'),
      INPUT_FILE_NAME VARCHAR(256) CHARACTER SET Unicode NOT CaseSpecific NOT NULL,
      WORKFLOW_RUN_ID BIGINT NOT NULL,
      SK_INFO_SYSTEM SMALLINT,
      SK_INPUT_FILE BIGINT,
      SK_ACCESS_DEVICE BIGINT,
      SK_PARTY_CLIENT BIGINT,
      SK_AGREEMENT BIGINT,
      SK_PARTY_VSP BIGINT,
      SK_PARTY_OSB BIGINT,
      SK_PRODUCT INTEGER,
      IMPORTED_FLAG CHAR(1) CHARACTER SET Unicode NOT CaseSpecific,
      CURRENT_LOAD_FLAG CHAR(1) CHARACTER SET Unicode NOT CaseSpecific,
      SK_ACC_DEV_REASON_TYPE BIGINT Compress ,
      ACTUAL_FROM DATE Format 'yyyy-mm-dd',
      ACTUAL_TO DATE Format 'yyyy-mm-dd',
      PK_RN BYTEINT Compress 1 ,
      SK_CONTR_SUBTYPE_ID BIGINT,
      SK_DRN BIGINT,
      SK_CARD_BIN BIGINT,
      SK_UCFC BIGINT,
      DRN VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      UCFC VARCHAR(255) CHARACTER SET Unicode NOT CaseSpecific,
      CONTR_SUBTYPE_ID BIGINT,
      CARD_BIN VARCHAR(9) CHARACTER SET Unicode NOT CaseSpecific
	  )
PRIMARY INDEX ( CARD_ID_W4 ) ;

.IF Errorcode <> 0 THEN .GOTO Err

/* 2. Insert rows with no duplicates into new table */

INSERT INTO SANDBOX.C0160000010008_CARD_new (
	LAST_MODIFIED_DT              
	, CARD_ID_W4                    
	, CARD_NUMBER                   
	, CONTRACT_ID_W4                
	, CARD_CLIENT_ID_W4             
	, PRODUCT_ID_W4                 
	, STATUS_ID                     
	, CARD_OPEN_DATE_W4             
	, CARD_EXPIRE                   
	, CARD_HOLDER                   
	, ISSUE_NUMBER                  
	, OSB_TB_NUMBER                 
	, OSB_NUMBER                    
	, CARD_VSP_NUMBER               
	, ADD_INFO                      
	, OSB_ID                        
	, MOBILE_IND                    
	, ADD_INFO_02                   
	, NFC_CARD                      
	, INFO_SYSTEM_TYPE_CD           
	, INFO_SYSTEM_INST_CD           
	, INPUT_FILE_NAME               
	, WORKFLOW_RUN_ID               
	, SK_INFO_SYSTEM                
	, SK_INPUT_FILE                 
	, SK_ACCESS_DEVICE              
	, SK_PARTY_CLIENT               
	, SK_AGREEMENT                  
	, SK_PARTY_VSP                  
	, SK_PARTY_OSB                  
	, SK_PRODUCT                    
	, IMPORTED_FLAG                 
	, CURRENT_LOAD_FLAG             
	, SK_ACC_DEV_REASON_TYPE        
	, ACTUAL_FROM                   
	, ACTUAL_TO                     
	, PK_RN                         
	, SK_CONTR_SUBTYPE_ID           
	, SK_DRN                        
	, SK_CARD_BIN                   
	, SK_UCFC                       
	, DRN                           
	, UCFC                          
	, CONTR_SUBTYPE_ID              
	, CARD_BIN                      
	)
SELECT 
		LAST_MODIFIED_DT              
		, CARD_ID_W4                    
		, CARD_NUMBER                   
		, CONTRACT_ID_W4                
		, CARD_CLIENT_ID_W4             
		, PRODUCT_ID_W4                 
		, STATUS_ID                     
		, CARD_OPEN_DATE_W4             
		, CARD_EXPIRE                   
		, CARD_HOLDER                   
		, ISSUE_NUMBER                  
		, OSB_TB_NUMBER                 
		, OSB_NUMBER                    
		, CARD_VSP_NUMBER               
		, ADD_INFO                      
		, OSB_ID                        
		, MOBILE_IND                    
		, ADD_INFO_02                   
		, NFC_CARD                      
		, INFO_SYSTEM_TYPE_CD           
		, INFO_SYSTEM_INST_CD           
		, INPUT_FILE_NAME               
		, WORKFLOW_RUN_ID               
		, SK_INFO_SYSTEM                
		, SK_INPUT_FILE                 
		, SK_ACCESS_DEVICE              
		, SK_PARTY_CLIENT               
		, SK_AGREEMENT                  
		, SK_PARTY_VSP                  
		, SK_PARTY_OSB                  
		, SK_PRODUCT                    
		, IMPORTED_FLAG                 
		, CURRENT_LOAD_FLAG             
		, SK_ACC_DEV_REASON_TYPE        
		, ACTUAL_FROM                   
		, ACTUAL_TO                     
		, PK_RN                         
		, SK_CONTR_SUBTYPE_ID           
		, SK_DRN                        
		, SK_CARD_BIN                   
		, SK_UCFC                       
		, DRN                           
		, UCFC                          
		, CONTR_SUBTYPE_ID              
		, CARD_BIN                      
FROM SANDBOX.C0160000010008_CARD_arch 
QUALIFY ROW_NUMBER() OVER (PARTITION BY CARD_ID_W4 ORDER BY LAST_MODIFIED_DT DESC) = 1;

.IF Errorcode <> 0 THEN .GOTO Err

/* 4. Rename new table as main T-table */

.LABEL Rename_

SELECT 1 FROM dbc.tables WHERE TableName = 'C0160000010008_CARD' AND databasename = 'SANDBOX';

.IF ActivityCount = 1 THEN .GOTO End_

RENAME TABLE SANDBOX.C0160000010008_CARD_new AS SANDBOX.C0160000010008_CARD ;

.IF Errorcode <> 0 THEN .GOTO Err

/* 3. Collect stats */

COLLECT STATISTICS ON SANDBOX.C0160000010008_CARD FROM SANDBOX.C0160000010008_CARD_arch ;

.IF Errorcode <> 0 THEN .GOTO Err

SELECT 'Table changed, all OK' status;  

.GOTO End_

.LABEL Err

SELECT 'Error on table change' status;

.LABEL End_


