Simple Loading into table ( table to table Load)

Database

Simple Loading into table ( table to table Load)

Hi All,

Wish you a very Happy new Year 2014

I have a very simple requirement of just taking back up of existing table , so I have followed folowing steps

1. created a back-up table very similar to existing table (using SHOW SELECT * FROM <tablename> )

2.Trying to load data from orginal table to back up table , just wanted to restric few records by joining the orginal table with one reference table, it is throwing me error ( positional Aurguments error)

original table ddl

================

CREATE MULTISET TABLE DW2_INVST_TRAN ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

EVENT_ID BIGINT NOT NULL,

REC_TS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

TRAN_AMT DECIMAL(15,2),

TRAN_DT DATE FORMAT 'YYYY-MM-DD',

INCM_CAT_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

TRAN_TYPE_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

CRNCY_CDE CHAR(3) CHARACTER SET LATIN CASESPECIFIC DEFAULT ' ',

DIV_NET_AMT DECIMAL(15,2),

TRN_TRD_CCY_NT_AMT DECIMAL(15,2),

TRAN_UPDT_DT DATE FORMAT 'YYYY-MM-DD',

CMSSN_YTD_AMT DECIMAL(15,2),

TRAN_CAPTR_DTTM TIMESTAMP(6),

SCRIB_EFF_DT DATE FORMAT 'YYYY-MM-DD',

CRNCY_CONV_MTH_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

REASON_TRAN_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

TRAN_DT_TM TIMESTAMP(6),

TRAN_CLAS_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

SRCE_REF_NUM CHAR(10) CHARACTER SET LATIN CASESPECIFIC,

POST_SEQ_NUM INTEGER,

REASON_CORS_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

SUPRS_TRN_STMT_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

VAT_AMT DECIMAL(15,2),

FEE_TTL_AMT DECIMAL(15,2),

OVRID_FEE_CDE CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

TRAN_CANC_SEQ_NUM INTEGER,

SRCE_SYS_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

EXCHG_RATE DECIMAL(7,5),

PLAN_CANC_OPT_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

EXMPT_VAT_FEE_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

IP_ID_TPTY_REF_NUM BIGINT,

PTYP_ID_SEC_INVST BIGINT,

ARRG_ID BIGINT NOT NULL,

CTAS_CONT_NUM CHAR(14) CHARACTER SET LATIN CASESPECIFIC COMPRESS )

PRIMARY INDEX ( ARRG_ID )

UNIQUE INDEX PKCols ( EVENT_ID )

INDEX XIE2Transaction ( SRCE_SYS_CDE );

backup table ddl

=============

CREATE MULTISET TABLE DW2_INVST_TRAN_R2 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

EVENT_ID BIGINT NOT NULL,

REC_TS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

TRAN_AMT DECIMAL(15,2),

TRAN_DT DATE FORMAT 'YYYY-MM-DD',

INCM_CAT_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

TRAN_TYPE_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

CRNCY_CDE CHAR(3) CHARACTER SET LATIN CASESPECIFIC DEFAULT ' ',

DIV_NET_AMT DECIMAL(15,2),

TRN_TRD_CCY_NT_AMT DECIMAL(15,2),

TRAN_UPDT_DT DATE FORMAT 'YYYY-MM-DD',

CMSSN_YTD_AMT DECIMAL(15,2),

TRAN_CAPTR_DTTM TIMESTAMP(6),

SCRIB_EFF_DT DATE FORMAT 'YYYY-MM-DD',

CRNCY_CONV_MTH_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

REASON_TRAN_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

TRAN_DT_TM TIMESTAMP(6),

TRAN_CLAS_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

SRCE_REF_NUM CHAR(10) CHARACTER SET LATIN CASESPECIFIC,

POST_SEQ_NUM INTEGER,

REASON_CORS_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

SUPRS_TRN_STMT_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

VAT_AMT DECIMAL(15,2),

FEE_TTL_AMT DECIMAL(15,2),

OVRID_FEE_CDE CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

TRAN_CANC_SEQ_NUM INTEGER,

SRCE_SYS_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

EXCHG_RATE DECIMAL(7,5),

PLAN_CANC_OPT_CDE CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

EXMPT_VAT_FEE_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

IP_ID_TPTY_REF_NUM BIGINT,

PTYP_ID_SEC_INVST BIGINT,

ARRG_ID BIGINT NOT NULL,

CTAS_CONT_NUM CHAR(14) CHARACTER SET LATIN CASESPECIFIC COMPRESS )

PRIMARY INDEX ( ARRG_ID )

UNIQUE INDEX PKCols ( EVENT_ID )

INDEX XIE2Transaction ( SRCE_SYS_CDE );

FOLLOWING IS THE SQL I AM USING TO LOAD DATA INTO BACKUP TABLE

====================================================

INSERT INTO DW2_INVST_TRAN_R2

SELECT * FROM DW2_INVST_TRAN T1

INNER JOIN

DW2_EVENT_XREF T2

ON T1.EVENT_ID=T2.EVENT_ID

AND (T2.EVENT_NTRL_KEY_ID LIKE '%GHS%'

OR T2.EVENT_NTRL_KEY_ID LIKE '%PNX%'

OR T2.EVENT_NTRL_KEY_ID LIKE '%CUT%' );

ERROR MESSAGE

====================

 Failed. 3813:  The positional assignment list has too many values. 

Can someone please help?

Regards

Swathi Reddy

Tags (1)
2 REPLIES

Re: Simple Loading into table ( table to table Load)

DW2_INVST_TRAN_R2 is the back up table , which is exact replica of DW2_INVST_TRAN

N/A

Re: Simple Loading into table ( table to table Load)

Your query tries to insert all rows from both tables DW2_INVST_TRAN and DW2_EVENT_XREF.

INSERT INTO DW2_INVST_TRAN_R2
SELECT T1.* FROM DW2_INVST_TRAN T1
INNER JOIN
DW2_EVENT_XREF T2
....