Error 2620 - The format or data contains a bad character

Database
Highlighted
Fan

Error 2620 - The format or data contains a bad character

Hi,

 

I am trying to create a table with the code below, but receive error 2620.  I suspect this is related to the cast functions but I can't figure out how to resolve.  Please help!

 

SyntaxEditor Code Snippet

create table schema.email_save as (select 
CAST(a.cust_id as DECIMAL(17,0)) as cust_num,a.cmpgn,a.mail_dt,a.creative_id,c.cust_acct_num,c.asof_date,c.acct_balance
from schema.email_customer a,schema.cust_cross_ref b,schema.account_list c
where b.c_num=cast(a.cust_id as DECIMAL(17,0))and b.acct_num=c.acct_num
and a.cust_id is not null
and a.mail_dt is not null
group by 1,2,3,4,5,6,7) with data;
2 REPLIES
Teradata Employee

Re: Error 2620 - The format or data contains a bad character

The way to figure this out is to look up the 2620 error in the Messages manual, available at info.teradata.com.  The book says that either there is a bad format string (not applicable here) or non-numeric data being cast as numeric.  Therefore there must be some non-numeric cust_id's.

You could use the regexp_similar() function to locate all such customers.  (For explanation and examples see the SQL Functions manual.)  Or you could just omit the casts on a.cust_id and just cast the b.c_num to match the character format of a.cust_id in the where-clause - i.e., let the customer ID be character in the table you are creating.

But why do you have a group-by in there?  And it seems odd to include a column like acct_balance in a group-by.  I guess there must be more to the actual SQL statement than you are showing.

Teradata Employee

Re: Error 2620 - The format or data contains a bad character

If b.c_num is character, or if one of b.acct_num or c.acct_num is a numeric type and the other is character, then the optimizer will introduce implicit CASTs to FLOAT (which could fail), in addition to the explicit CASTs of a.cust_id.