DataSets can't handle float properly
Moderators: chulett, rschirm, roy
DataSets can't handle float properly
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.
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.
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.
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.
Modern Life is Rubbish - Blur
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'