DataSets can't handle float properly

blewip · Post by **blewip** » Fri Feb 16, 2007 5:21 am

I have been struggling to get my floats (doubles actually) to work correctly.

I think I have found out why, the DataSet can't handle floats correctly.

We have a common framework where an extraction jobs is used to determine which rows are extracted, this is stored in a DataSet then a transformation jobs processes the data. The float is normally converted to a String in the Transformation job, sometimes some calculations are carried out before.

If we do the conversion in the transformation job (DFloatToStringNoExp), the values are not accurate, however if I do them in the extraction job, before they have gone to a DataSet, they are accurate.

I have played around with the length and the scale, but this has made no difference.

Examples of the values, 25548693.134 is the true value, after going through the transformation job (through a DataSet) it looks like 25548700. Also viewing the data in the DataSet it looks like this 2.55487e+07.

Anyone got any suggestions, experience of this? To me this looks like a serious failing, my understanding was that the DataSet was structured like a database table.

kumar_s · Post by **kumar_s** » Fri Feb 16, 2007 6:18 am

Write the dataset into a sequential file and check for the value of the data.

blewip · Post by **blewip** » Fri Feb 16, 2007 8:18 am

That's what I'm doing in the transformation job.

That where I get the value 25548700.

kumar_s · Post by **kumar_s** » Fri Feb 16, 2007 8:56 pm

What if you write it directly as Dfloat (without any conversion to string)?

blewip · Post by **blewip** » Mon Feb 19, 2007 3:15 am

That's what I'm doing in the extraction job.

It shows up as 2.55487e+07

kumar_s · Post by **kumar_s** » Mon Feb 19, 2007 4:24 am

What is your source? How are you getting the original value?

blewip · Post by **blewip** » Mon Feb 19, 2007 8:31 am

The source is a Sybase table, we can verify the value using a variety of tools, SQL Advantage, DBArtisan etc.

The value can also be verified by taking the value and writing it to a sequential file using the DFloatToStringNoExp function.

We can see that after the Double is written to a DataSet however, it loses precsion. Also we are not talking after a few decimal places, this is the main number changing.

To me the difference is unacceptable. It appears that the DataSet is only storing the mantissa in a few digits. If the number is small, it will appear to work okay. It also seems that we have no control over it. Changing the settings for the Double, length or precision makes no difference.

Ultramundane · Post by **Ultramundane** » Mon Feb 19, 2007 10:35 am

Hello,

I just tried using "Sybase Enterprise Stage" on 752 and when I examine the log it doesn't use a float but instead converts it implicitly to an int32 for the datatype even though the datatype was supposed to be float.

I tried using "ODBC Enterprise", got same results you mentioned.

I tried using "Sybase OC", got the same results you mentioned.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 19, 2007 3:02 pm

Has anyone reported this to their support provider?

kumar_s · Post by **kumar_s** » Mon Feb 19, 2007 5:14 pm

As mentioned, if Ultramundane also getting the same effect, it could be a bug.
But once I face similar kind of issue with CFF stage and reported to support, but later we found that the place where we looking for the input value is modified when it is actually given to Datastage.
If possible try to convert it into a varchar field in some other temp table and check for the values.

blewip · Post by **blewip** » Mon Feb 26, 2007 3:45 am

Sorry I was off on vacation at the end of last week.

I'll package up a test case and send it to IBM.

Cheers

Paul