Insertion Problem

Database

Insertion Problem

INSERT INTO PRCNGAGGRETL_L1F5_WORK_DB.cust_view_mgmt_hierarchy_new

(cust_view_mgmt_nbr,

 parnt_enti_nbr,

 contr_enti_view_flg,

 sale_enti_view_flg,

eff_tmstp, 

expr_tmstp, 

cust_view_mgmt_type_cd,

 globl_enti_nbr,

div_enti_nbr,

 cntry_enti_nbr,

 grp_enti_nbr,

 sbgrp_enti_nbr,

 fac_enti_nbr,

cust_acct_nbr,

 acct_link_resn_cd, 

acct_link_resn_chng_flg,

 globl_enti_chng_flg,

div_enti_chng_flg,

 cntry_enti_chng_flg,

 grp_enti_chng_flg,

 sbgrp_enti_chng_flg,

fac_enti_chng_flg,

 parnt_enti_chng_flg,

 src_sys_cd,

 last_updt_emp_nbr,

db_load_dt_tmstp)

select

cast(concat(trim(c.cust_Acct_nbr),'111') as BIGINT) cust_view_mgmt_nbr,

'111' parnt_enti_nbr,

'Y' contr_enti_view_flg,

 'Y'  sale_enti_view_flg,

 CURRENT_TIMESTAMP(0) eff_tmstp, 

ADD_MONTHS( CURRENT_TIMESTAMP(0),10) expr_tmstp,

'EAN' cust_view_mgmt_type_cd,

'1111111' globl_enti_nbr,

'2222222' div_enti_nbr,

'3333333' cntry_enti_nbr,

'4444444'  grp_enti_nbr,

'5555555' sbgrp_enti_nbr,

'6666666'  fac_enti_nbr,

c.cust_acct_nbr,

'EAN' acct_link_resn_cd,

'N' acct_link_resn_chng_flg,

'N' globl_enti_chng_flg,

 'N' div_enti_chng_flg,

'Y' cntry_enti_chng_flg,

 'N' grp_enti_chng_flg,

'N' sbgrp_enti_chng_flg,

'N'  fac_enti_chng_flg,

'N' parnt_enti_chng_flg,

 'CFAM' src_sys_cd,

 '920540' last_updt_emp_nbr,

 CURRENT_TIMESTAMP(0) db_load_dt_tmstp

from

PRCNGAGGRETL_L1F5_VIEW_DB.cmdm_customer c

while i am running the query i am getting error for insert is ERROR 2020:The format or data contains a bad charcter.

can you suggest any of you guys...

1 REPLY

Re: Insertion Problem

Seems like cust_Acct_nbr contains a character data and thus failing the casting operation. Make sure that cust_Acct_Nbr is numeric only!