All Forums Database
GRH65 1 post Joined 07/05
10 Oct 2005
Teradata Error 2621 - Bad character in format or data of {table nm} column 7

Can anyone explain to me what the actual definition of this error is? I have taken a look at the data and nothing looks out of the ordinary. I am creating a couple of volatile tables and updating one of them with info from the other. Again nothing from t2 looks erroneous in such a way that the error would not update t1.Code:CREATE Volatile TABLE B2B_Subs,no logAS (SELECTcal.Sor_id, m.area_desc AS Area, m.region_desc AS Region, cal.cust_id, cal.cust_line_seq_id, cal.acct_num, NULL AS NAICS_Nm , NULL AS emp_tot_num/* September 21, 2005 cals.segmt_value AS Segment */, sdct.sls_dist_chnl_type_desc AS Channel, ca.duns_loc_num, NULL AS CntofUltNum /* September 21, 2005 */, count(cal.mtn) AS CntofMTNFROM cust_acct_line_v calINNER JOIN market_v m ON cal.sor_id = m.sor_id AND cal.mkt_cd = m.mkt_cdINNER JOIN cust_acct_v ca ON cal.sor_id = ca.sor_id AND cal.cust_id = ca.cust_id AND cal.acct_num = ca.acct_num AND ca.duns_conf_ind > 5INNER JOIN sales_dist_channel_type_V sdct ON cal.sor_id = sdct.sor_id AND cal.sls_dist_Chnl_type_cd = sdct.sls_Dist_chnl_type_cd AND sdct.sls_dist_Chnl_churn_ctgry = 'Outside'/* September 21, 2005 Pulled due to issue with Segment codes - Customer Segmentation LEFT OUTER JOIN cust_acct_line_segment_v cals ON cal.sor_id = cals.sor_id AND cal.cust_id = cals.cust_id AND cal.cust_line_seq_id = cals.cust_line_seq_id AND add_months(cast('2005-08-01' AS date),1)-1 BETWEEN cals.eff_dt AND cals.exp_dt AND segmt_type_cd = 'HRCHY_SEGMT' */WHERE (cal.line_act_dt BETWEEN 1050801 AND 1050831) AND (cal.line_term_dt is NULL OR cal.line_term_dt > (add_months(cast('2005-08-01' AS date),1)-1)) AND cal.sor_id IN ('I','V') AND cal.rev_gen_ind = 'Y'GROUP BY cal.sor_id, m.area_desc, m.region_desc, cal.cust_id, cal.cust_line_seq_id, cal.acct_num, naics_nm, emp_tot_num, CntofUltNum, sdct.sls_dist_chnl_type_desc, ca.duns_loc_num) WITH DATA PRIMARY INDEX ( Area, Sor_id, Cust_id, Cust_line_seq_id) ON Commit Preserve Rows ;CREATE Volatile TABLE duns,no log AS (SELECT duns1.duns_loc_num duns_loc_num1, duns1.bus_nm bus_nm1, duns1.domestic_emp_tot_num loc_emp_tot, duns2.bus_nm bus_nm2, CASE WHEN duns1.naics_cd1 like ANY ('42%','44%','45%') THEN 'Distribution' WHEN duns1.naics_cd1 like '62%' THEN 'Healthcare' WHEN duns1.naics_cd1 like '53%' THEN 'Real_Estate' WHEN duns1.naics_cd1 like '3254%' THEN 'Pharma' WHEN duns1.naics_cd1 like ANY ('31%' ,'321%' ,'322%' ,'323%' ,'334%' ,'3251%' ,'3252%' ,'3253%' ,'3255%' ,'3256%' ,'3259%', '326%' ,'327%', '33%') THEN 'Indust_Manu' WHEN duns1.naics_cd1 like '51%' THEN 'Media' WHEN duns1.naics_cd1 like '5411%' THEN 'ProfSvc_Legal' WHEN duns1.naics_cd1 like ANY ('5412%' ,'5413%' ,'5414%' ,'5415%' ,'5416%' ,'5417%','5418%' ,'5419%') THEN 'Prof_Svc' WHEN duns1.naics_cd1 like '524%' THEN 'Insurance' WHEN duns1.naics_cd1 like ANY ('521%','522%','523%','525%' )Then 'Invest_Bank' WHEN duns1.naics_cd1 like ANY ('492%','484%') THEN 'Transport' WHEN duns1.naics_cd1 like '22%' THEN 'Utilities' WHEN duns1.naics_cd1 like '61%' THEN 'Education' WHEN duns1.naics_cd1 like '23%' THEN 'Construction' WHEN duns1.naics_cd1 like '922%' THEN 'Safety_Law' WHEN duns1.naics_cd1 like ANY ('921%' ,'923%','924%' ,'925%','926%' ,'927%','928%') THEN 'Government' ELSE 'Other' END NAICS_Desc1 /* Oct 5, 2005 changed again to naics_cd1 Sep. 20, 2005 - changed from sic1_cd to sic1_master_grp_cd */, count(DISTINCT(duns1.duns_ult_num)) ult_num1FROM duns_v duns1LEFT OUTER JOIN duns_v duns2 ON coalesce(duns1.duns_ult_num, duns1.duns_hq_num, duns1.duns_loc_num) = duns2.duns_loc_numWHERE duns1.duns_loc_num IN (SELECT duns_loc_numFROM B2B_Subs) AND duns1.domestic_emp_tot_num BETWEEN 50 AND 499GROUP BY 1,2,3,4,5 )WITH DATA PRIMARY INDEX ( DUNS_LOC_NUM1) ON Commit Preserve Rows ;-----------------Error occurs in this code------------------------UPDATE B2B_SubsFROM (SELECT duns_loc_num1 ,bus_nm2, loc_emp_tot, naics_desc1,ult_num1FROM duns) xSET emp_tot_num = loc_emp_tot, NAICS_NM = NAICS_DESC1, CntofUltNum = ult_num1WHERE duns_loc_num = duns_loc_num1 AND bus_nm2 is NOT NULL;

Greg
nellaivijay 1 post Joined 08/04
02 Jan 2006

Hi,I hope that following modified query would work fine, because you missed out the reference name of the table.UPDATE B2B_SubsFROM (SELECT duns_loc_num1 ,bus_nm2, loc_emp_tot, naics_desc1,ult_num1FROM duns) xSET emp_tot_num = x.loc_emp_tot, NAICS_NM = x.NAICS_DESC1, CntofUltNum = x.ult_num1WHERE B2B_Subs.duns_loc_num = x.duns_loc_num1AND B2B_Subs.bus_nm2 is NOT NULL;Please revert and confirm it.ThanksVijay

You must sign in to leave a comment.