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;
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.
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.