community.borland.com

Article #25210: Accuracy of the NUMERIC and DECIMAL data types

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