Problem: The number stored in the NUMERIC or DECIMAL column has more decimal places than is specified in the scale portion of the column and/or the number stored in the field is a close approximation to the number I wanted to store there. Solution: What is being encountered is the behavior of the IEEE format that is used in storing the numbers in the NUMERIC or DECIMAL columns. The definition of the behavior of the IEEE format numbers is given below: The number that results from reading the IEEE format that is stored in the NUMERIC or DECIMAL column produces a number that contains a whole number portion and a decimal portion (e.g. 3.141592, which is an estimate for pi). The whole and decimal portions of the number are represented by bit patterns. The decimal component is computed by adding up the bits that represent the decimal portion of the number. The bit patterns used for decimal value are represented in the arithmetic series shown below: 1/2 + 1/4 + 1/8 + 1/16 + 1/32 + 64 + 1/128 + . . . Elements of this series are taken and added together to approximate the decimal potion of the number. For example, to approximate the value of 0.33333333 (this is roughly the fractional value of 1/3) the following values would be used: 1/4 = 0.25 + 1/16 = 0.3125 + 1/64 = 0.328125 + 1/256 = 0.33203125 + 1/1024 = 0.3330078125 + 1/4096 = 0.333251953125 + . . . = . . . Most decimal values are approximated, the only values that are exactly computed are those that are the sum of the elements of the series of 1/2 + 1/4 + 1/8 + . . . that is given above. Advantages: The IEEE format is used by the math coprocessor to perform calculations. Using the math coprocessor to perform calculations provides a performance advantage over using math libraries, like floating point libraries or BCD libraries. Calculations involving math libraries are generally much more time-consuming. Disadvantages: In situations where accuracy in the decimal portion of the number is desired (i.e. financial applications) using NUMERIC and DECIMAL values will present a couple of situations to watch out for: - Round off errors: In performing operations like multiplication and division with IEEE numbers, round off errors can occur. To prevent this from happening the number is often converted to a BCD format and the mathematical operation is performed using a function in a BCD library. BCD libraries can be employed on the client-side, or can be called in user-defined functions by stored procedures and triggers to make accurate calculations. The BDE has an alias property called EnableBCD that when set to true will perform BDC calculations on the client-side for NUMERIC columns 4 or greater in width or for DECIMAL columns. - Storing numbers in NUMERIC and DECIMAL columns: While BCD libraries are often used to make accurate calculations in regards to the decimal portion of a number, following the calculation, it is important to consider where to store the value. If the number is stored in a NUMERIC or DECIMAL column then the IEEE approximation of the number will occur. For some applications this is fine, for other applications this is not acceptable. Other schemes involve storing the whole portion of the number in an INTEGER column and the decimal portion of the number in a separate INTEGER column, while another scheme would be to translate the number into a string and store it in a CHAR or VARCHAR column of sufficient width.
Last Modified: 02-OCT-00