Re: Floats

Hello! I noticed the same 'problem', too. Below is the answer I was given by Informix Norway! Have a nice day! SoM In article <1994Jan17.172959.5775@ccd.harris.com> rrl@ccd.harris.com (Ron (Jose) Larson) writes: >John Shoemaker (fms2000!johns@uunet.UU.NET) wrote: > >: > > I encounter a precision problem when I want to select a column with >: > > float data type. The value i inserted was "15.20", and when i use >: > > isql to check, it's fine. But if I used a double type host variable >: > > to get its value in my ESQL/C program, it got "15.199999". Could I >: > > get the correct data value, or should I change that column's data type >: > > if the correct value is very important? > >: This is a common problem with FLOAT types - in any language. When you >: inserted the value into the database, the value of 15.199999 was inserted. >: When you query it via isql, isql will *round* it! If you want to look at > > FYI... isql will "round" to the number of decimal places > specified in environment variable DBFLTMASK. It will also > "truncate" any input to this number of decimal points as > well. We had an application which piped input/output to > "isql - - " and noticed that floats were being "rounded/truncated" > to 2 decimal places by default (if DBFLTMASK is not defined). > >: the actual value, you can use an ACE program to print it, specifying >: a using: using "#####.########". > >: If you cannot live with this, you need to use DECIMAL types. > > >: ------------------------------------------------------------------------------ >: John M. Shoemaker internet: shoemaker@tweety.digsys.com >: Daugherty Systems UUCP: uunet!fms2000!johns >: One City Place, Suite 240 FAX: (314) 432-8217 >: St Louis, MO 63141 Voice: (314) 432-8200 >: ------------------------------------------------------------------------------ >: EXEC SQL include "disclaimer.h"; >: ------------------------------------------------------------------------------ > >-- > >*--------------------------------------------------------------------------* >* Ronald R. Larson * >* Staff Engineer * >* * >* Harris Corporation * >* Controls Division Phone: (407) 242-5578 * >* P.O. Box 430, MS 205 Fax: (407) 242-4268 * >* Melbourne, FL 32902-0430 internet: rrl@ccd.harris.com * >* EPRInet: UA4N311 * >*--------------------------------------------------------------------------* Tech Notes 1992, Volume 2, Issue 3 QUESTION What are floating point and floating point DECIMAL data types, and how do they differ? ANSWER Floating Point Data Types Floating point data types are machine-specific and store numbers in "scientific notation", such as, "1.083736473783e12". The size of a number, also known as its magnitude, is the size of the exponent that can be stored. It is important to note that the size of the number stored with a float is host-dependent. Conforming ANSI C implementations include a limits.h file in the /usr/incl directory. This file contains a macro named FLT MAX, which yields the largest finite floating point value. Informix supports two binary floating point data types, SMALLFLOAT and FLOAT. The FLOAT data type is a double precision, binary floating point number that usually takes up 8 bytes. The SMALLFLOAT, also known as REAL, is a single precision floating point number that takes up to 4 bytes. In the example, the decimals and floats have been rounded to two decimal places. Precision is mathematically defined as how close a real number can be represented by the format. If x is a real number which can be exactly represented by the format, and y is the next real number, as exactly represented by the format, any real number between x and y must be approximated by either x or y. The closer x and y are to each other, the higher the precision. For a given scale, precision and the number of significant digits could be used synonymously. The implication is that SMALLFLOATs use the same size components, while FLOATs use a larger width mantissa. Thus, values can be represented in a FLOAT column more precisely than in a SMALLFLOAT column. The magnitude of a representable number mainly depends on the number of exponent bits (digits) used and to some extent, the mantissa (the decimal part of a logarithm). A larger number of exponent bits dramatically increases the magnitude of the maximum number. The number of the significant (decimal digits) is dependent on the width of the mantissa used for the underlying format. For example, SMALLFLOAT contains 6 to 7 significant digits, while FLOAT contains 14 to 26 significant digits. Decimal Floating and Fixed Point Data Types DECIMAL is actually DECIMAL(16) and is a DECIMAL floating point number that will store up to 16 significant digits, or the absolute value between 10-128 to 10+126. While DECIMAL or DECIMAL(p) store the full precision of a value, the SELECT statement in INFORMIX-SQL displays values rounded to two decimal places. DECIMAL(p,s) with two qualifying values, a precision and a scale, is a "fixed point" number, rather than a floating point number as represented by the DECIMAL or DECIMAL(p) formats. Values larger than DECIMAL (p,s) are rounded to scale and then stored. For example, the number 1.856 is actually stored as 1.86 when DECIMAL(8,2) is specified as the data type. DECIMAL(p,s) is sometimes used in commercial accounting applications. The value stored by either king of DECIMAL is a decimal coded value and is machine independent. At the internal C level, a value is represented as a structure. struct decimal { short dec_exp Holds the exponent of the normalized DECIMAL data type number short dec_pos Holds the sign of DECIMAL type number short dec_ndgts Contains the number of base 100 significant digits char dec_dgts [DECSIZE] character array that holds the significant digits of the normalized DECIMAL type number } Because Informix uses this storage mechanism to store DECIMAL values, the values remain machine independent. Advantages of the Individual Data Types When determining to use DECIMAL floating point data types over floating point data types, it is important to consider the machine independence of the values stored by the DECIMAL floating point data type. FLOAT and SMALLFLOAT numbers are stored as the respective C float types and are dependent on the values generated by the hardware. Informix cannot guarantee the