When I run the following code I get this error message ("CREATE TABLE Failed. 3754: Precision error in FLOAT type constant or during implicit conversions."). I believe this is related to the two cast statements but I'm struggling to understand how to resolve this. a.id1 is stored as decimal(18,0) and b.id2 is stored as varchar(30). I've tried to cast these as bigint, numeric, varchar but nothing is working or perhaps I'm doing something wrong.
Any advice on how to proceed? Thanks!
create table schema.table as
cast(a.id1 as integer) as cust_num,
cast(b.id2 as integer) as cust_num2,
a.cust_num,a.acct_status_cd,b.cust_num2,b.email from schema2.table2 a join schema1.table3 b on b.cust_num2=a.cust_num where status in ('01','02','03')
) with data;
Is one of b.cust_num2 or a.cust_num a numeric type and the other character? If so, Teradata will attempt to implicitly convert both to FLOAT.Explicitly CAST the one that's character to some larger precision numeric type (like DECIMAL(18) or BIGINT) for the join condition.
The most flexible numeric data type is NUMBER, so try switching to TO_NUMBER(b.id2)