Decimal values getting expanded

mac4rfree85 · Post by **mac4rfree85** » Wed Jul 04, 2012 2:13 pm

Hi Guys,

My source is a flat file. My target is a oracle table. The column in question is of metadata Number(22,12) in oracle.

In datastage, i am using the metadata float 22,12.

Now the problem is, in the source file iam getting the value 46.536. But in the oracle table that value is getting inserted as 46.5359993.

Also , the source file having a value 3.608 is getting inserted as 3.60800004.

If i keep a sequential file, it is matching (46.536,3.608).

Can somebody help me out here.

Cheers!!!!

ray.wurlod · Post by **ray.wurlod** » Wed Jul 04, 2012 4:28 pm

Floating point numbers can not be stored accurately in computers; this relates to the fixed size of the binary mantissa and exponent. Review the relevant IEEE standards to understand more. Meanwhile, try using Decimal rather than Float as the data type.

mac4rfree85 · Post by **mac4rfree85** » Thu Jul 05, 2012 5:27 am

So, Ray there is no solution to this problem?

Thanks!!!!

chulett · Post by **chulett** » Thu Jul 05, 2012 5:56 am

Of course there is!

1. Get a Premium account so you can read Ray's reply in its entirety.
2. Stop using Float as a data type.

mac4rfree85 · Post by **mac4rfree85** » Thu Jul 05, 2012 8:40 am

First one,yes i am working on it.

When we used decimal, decimal values are not getting inserted. Hence only we have used float. So can you suggest which metadata needs to be used for a decimal value.

The issue is fixed. changed the metadata from Float to Double.

Thanks !!!!!

ArndW · Post by **ArndW** » Thu Jul 05, 2012 8:57 am

As stated several times before - avoid floating point. Your data in the sequential source is in decimal format and should be read, and stored, as such. Sequential files can read decimal data, so if you are not getting values then there is a problem in the column definitions that you should correct.
What have tried with reading decimal values and what exactly has happened?

chulett · Post by **chulett** » Thu Jul 05, 2012 9:29 am

mac4rfree85 wrote:When we used decimal, decimal values are not getting inserted. Hence only we have used float.

I too would be curious what this means.

mac4rfree85 · Post by **mac4rfree85** » Thu Jul 05, 2012 9:46 am

The data was not getting inserted properly.

For an example, if data was 8.56, it was getting inserted as 0000000.

Not sure why, but with float, we were somewhat getting (8.5600001).

But now with double, i am getting what i want (8.56)

jwiles · Post by **jwiles** » Thu Jul 05, 2012 9:54 am

FYI...Double is still a floating point data type, so you are at risk of seeing the same type of issue arise again.

If you were seeing 0 inserted instead of the actual values when the source was defined as a decimal, that indicated that the value was not imported most likely due to some formatting issues, such as leading spaces (which could be the case with a fixed-width, right-justified column). Examine your source data to see what it actually contains. You may need to instead import it as char or varchar and then clean it and convert to decimal within a transformer instead.

Regards,