Float type output difference between two teradata system

Database
Enthusiast

Float type output difference between two teradata system

To handle exact float precision values
I tried using cast to NUMBER for FLOAT type ad advised in this forum by one of the expert. After extract the expected value is written into a file. But after loading the same value into teradata table using Load TPT operator I am seeing discrepancy between outputs.

Raw Select :
select float_column from float_table;
Source : 6.32488836477988E 001
Target : 6.32488836477987E 001

Select with CAST:
select cast(float_column as NUMBER) from float_table;
Source :63.24888364779875
Target : 63.24888364779875

So Bit confused here. If I select without case I see some minor difference in output. But I am getting same output if I used cast in select statement on both source/target.

Could you please help me to understand what's happening here

4 REPLIES
Enthusiast

Re: Float type output difference between two teradata system

Could someone please help me to understand above issue

Highlighted
Junior Contributor

Re: Float type output difference between two teradata system

SELECT Cast(Cast(0.1 AS FLOAT) + Cast(0.2 AS FLOAT) AS DECIMAL(38,37));
0.3000000000000000444089209850062616169 

SELECT Cast(Cast(0.1 AS FLOAT) + Cast(0.2 AS FLOAT) AS NUMBER);
0.30000000000000007

A Float is a non-exact numeric representation with approx. 15 digits precision,

see What Every Computer Scientist Should Know About Floating-Point Arithmetic

 

A bit easier to read http://floating-point-gui.de/basic/

 

Enthusiast

Re: Float type output difference between two teradata system

Hi dnoeth,

 

Thanks for the note. Here I am not trying any arithmetic calculation.

If I move data using ARCMAIN to the target then I am not facing this difference issue. But while moving data using TPT (cast to Number to varchar) I am seeing this difference.

Is this something related to Teradata Systems issue. 

 

Thanks,

Muthu.

Teradata Employee

Re: Float type output difference between two teradata system

Whenever a non-exact value is converted from one representation to another, there is the possibility of losing some apparent precision. This has nothing specifically to do with Teradata, it's how floating-point works in general.

 

If you use ARCMAIN, there is no conversion - the data is dumped and copied in TD internal representation.