Problem with floats

We have noticed an annoying feature (?) in float data type. We have a table which has float column used only with large integer values, i.e. integers from 10^6 up to 10^10. Sometimes Informix changes the values a little, for example 102658875,0 becomes 102658874,9999999. We haven't found any common situation where the problem would arise, it just happends few times a year. The table is not updated very frequently, only few times a week. The float type is used because of historical reasons, and changing it to integer is not the worth of the work. Anyone seen anything like it before? Could it have something to do with Informix internal presentation of floating point numbers? Thanks, Jussi Paasonen System Specialist Helsinki Exchanges

Jussi Paasonen wrote: > > We have noticed an annoying feature (?) in float data type. We have a > table which has float column used only with large integer values, i.e. > integers from 10^6 up to 10^10. Sometimes Informix changes the values > a little, for example 102658875,0 becomes 102658874,9999999. > > We haven't found any common situation where the problem would arise, > it just happends few times a year. The table is not updated very > frequently, only few times a week. The float type is used because of > historical reasons, and changing it to integer is not the worth of the > work. > > Anyone seen anything like it before? Could it have something to do > with Informix internal presentation of floating point numbers? Yup. Informix store FLOAT as IEEE-754 64-bit floating point, using the native machine's representation. This is a binary data type and is not an exact representation of all values. Remember that while integers ARE exactly representable in binary floats are always normallized to be a scaled fraction with an implied one bit preceding the first stored bit. There are some decimal integers that just cannot be represented exactly this way. Another possibility is that you are FETCHing the data into "C" float types rather than "C" double. The Informix float datatype is the same as "C" double NOT float. Using float you will only get 9 1/2 digits of precision. If the value you give is an example, it is 10 digits, of all of the problematic values then that may be the reason. At any rate, I suggest that you use DECIMAL(10,0) instead it only takes six bytes of storage so it is more space efficient than float for this particular range of values and is probably more efficient for calculations as well. If you need to you can still fetch the data into doubles in your ESQL/C or 4GL programs and the engine will convert. Art S. Kagel

/ Art S. Kagel wrote: / / Another possibility is that you are FETCHing the data into "C" float / types rather than "C" double. The Informix float datatype is the same / as "C" double NOT float. Using float you will only get 9 1/2 / ... / At any rate, I suggest that you use DECIMAL(10,0) instead it only takes / six bytes of storage so it is more space efficient than float for this / particular range of values and is probably more efficient for / calculations as well. If you need to you can still fetch the data into / doubles in your ESQL/C or 4GL programs and the engine will convert. The C data type used is double, so it should not be the problem. Converting the column from FLOAT to DECIMAL sounds reasonable, especially if it is compatible with FLOAT from ESQL/C point of view. Thanks for your help! Jussi Paasonen System Specialist Helsinki Exchanges

Jussi Paasonen wrote: > > / Art S. Kagel wrote: > / > / Another possibility is that you are FETCHing the data into "C" float > > / types rather than "C" double. The Informix float datatype is the > same > / as "C" double NOT float. Using float you will only get 9 1/2 > / ... > / At any rate, I suggest that you use DECIMAL(10,0) instead it only > takes > / six bytes of storage so it is more space efficient than float for > this > / particular range of values and is probably more efficient for > / calculations as well. If you need to you can still fetch the data > into > / doubles in your ESQL/C or 4GL programs and the engine will convert. > > The C data type used is double, so it should not be the problem. > Converting the column from FLOAT to DECIMAL sounds reasonable, > especially if it is compatible with FLOAT from ESQL/C point of view. Compatible, yes, so far as the data values themselves are compatible. Note that if the values are not representable by the engine then unless the engine is running on a different CPU/FPU than your application FETCHing into a double will not help and you will have to deal with DECIMAL in your ESQL/C also using Informix library functions for calcs and output. Art S. Kagel

Hi all, Env: Informix 7.30 The function TRUNC (column_name, 0) in a SQL statement returns the expected value - omitting the mantissa part. Now that if the same function is used in UNLOAD statement I get the integer value + ".0". Why is that? Is there any other way to get only the integer part of a DECIMAL type column to use in UNLOAD statement? Advance thanks. Regards ... Mahesh G. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own