Float type issue while export to a file

Database
Enthusiast

Float type issue while export to a file

I am using TPT selector to export data to a file from a table. 

While exporting FLOAT value , in file the data is written in Exponential format. But I need as a String output. so that we will have as such data ini data file .

I am below SQL to fetch the data 

SyntaxEditor Code Snippet

CAST ((float_column (format '--(32)9.9(6)')) AS VARCHAR(30))

I am getting value as such if we have precision up to 6 digits. 

In case if precision if >6 then the value is getting riunded off.

Could someone please help here to convert float to string in teradata.

 

Output Expected:

 

   Input                                   Output

6.51533347827484   -- 6.51533347827484

 


Accepted Solutions
Junior Contributor

Re: Float type issue while export to a file

Instead of applying a FORMAT you might cast to a NUMBER, which creates a variable number of fractional digits.

1 ACCEPTED SOLUTION
3 REPLIES
Highlighted
Teradata Employee

Re: Float type issue while export to a file

Your FORMAT allows for 32 digits to the left of the decimal and only 6 to the right.

If you want 14 digits to the right, change your FORMAT.

Note that although the range of FLOAT is quite large, the precision is limited to roughly 15 decimal digits.

Junior Contributor

Re: Float type issue while export to a file

Instead of applying a FORMAT you might cast to a NUMBER, which creates a variable number of fractional digits.

Enthusiast

Re: Float type issue while export to a file

Hi dnoeth,

 

I tried using cast to NUMBER for FLOAT type. After extract the expected value is written into a file. But after loading the same into teradata 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.