Re: Pro*C Rounding Solution ***

In article <3c0tga$cp1_at_newsbf01.news.aol.com> lmayhew_at_aol.com "LMayhew" writes:

>
> :culberso_at_tybrin.com (Mike Culberson) writes
 

> :I am working on a SUN 630MP with Oracle 7.1.4.1.0, Pro*C 2.0.4.0.0, and
> :SunOS 4.1.3_U1. The problem I am having is when I fetch a NUMBER column
 from
> :a table the value referenced in the host variable is not the exact
> :number value in table. I am working with an accounting system and numbers
> :need to be exact. Example of code follows.
>
>
> :oracle_table
> :------------
> :
> :val NUMBER(11,2) with a value of 1.55
> :
> :code
> :----
> :
> :#include <stdio.h>
> :
> :EXEC SQL BEGIN DECLARE SECTION;
> : double value;
> : char oracleid = '/';
> :EXEC SQL END DECLARE SECTION;
> :
> :EXEC SQL INCLUDE sqlca;
> :
> :main ()
> :{
> : EXEC SQL CONNECT oracleid;
> :
> : EXEC SQL SELECT val
> : INTO :value
> : FROM oracle_table;
> :
> : printf ("value = %15.30g\n",value);
> :}
> :
> :If val is 1.55 the value printed out would be
 1.55000000000000004440892098501
> :
> :Since I am dealing with multipling rates and calculating totals this
 causes
> :my totals to be off. So far the only way to ensure correct calculations
 are
> :to make all numbers integer within the Pro*C program. Are there any math
> :libraries out there that would solve the problem.
>
>
> First you might try using ROUND(val,2) on the select. So you'd have:
>
> EXEC SQL SELECT ROUND(val,2)
> INTO :value
> FROM oracle_table;
>
> If that doesn't work, you'll need to round the double variable. Enclosed
> is a function that I wrote and use all the time, it uses some C functions
> that I believe are common to most compilers ( this is from a MSC
> Compiler).

    double value;
    EXEC SQL SELECT 1.55 INTO :value FROM TAB;

doesn't work because value is a double and the following doesn't work:

    printf ("value = %15.30g\n",(double)1.55);

So your function cannot work.

    double value;
    EXEC SQL SELECT 1.55 INTO :value FROM TAB;     value = rnd_dble(value,2) /* your function */

Why not? Base conversion and recurring decimals.

Your function:

> #include <stdio.h>
> #include <math.h>
>
> double rnd_dble( double dd, int i )
>
> {
>
> double l;
> double w, x, y, z;
>
> /* Seperate integer and fractional part of double */
> x = modf( dd, &y );
>
> /* Multiply fractional part, making an integer */
> x = x * pow(10.0,i);
>
> /* Test for sign */
>
> l = 1.0;
> if ( x < 0.0 ) l = -1.0;
>
> /* Find integer largest integer less than or equal to fractional part */
> z = floor(x);
> w = x - z;
>
> /* Check difference, so that we can round up or down */
> if ( w > 0.49 ) z = z + 1.0;
>
> /* Shift fractional part and add to original integer for rounded value
> */
> y = y + ( z / pow(10.0,i) );
>
> /* Set sign */
> y = y * l;
>
> return( y );
>
> }
>
> Hope this helps...
>
> Lee
>

Rounding will not always help. The number 0.1 is a recurring decimal in base 2. Disable compiler optimisation to see that (.1/100.*100.) is not zero.

There is only one way to _RELIABLY_ prevent rounding/truncation errors: Avoid base conversions of fractional amounts.

IT IS IMPOSSIBLE TO GET RELIABLY ACCURATE RESULTS WHERE A BASE CONVERSION OF FRACTIONAL AMOUNTS IS CONCERNED. The above function cannot be relied upon becuase no-one can do the impossible. But it is also riddled with bugs (or design errors, if you will).

Example 1: The line:
> if ( w > 0.49 ) z = z + 1.0;

is a joke on its own! What about 0.491?

Example 2: The expression (rnd_dble(x,3)+rnd_dble(-x,3)) is not zero for many x.

To avoid base conversions of fractions either use integers or fetch and manipulate your numbers in character arrays. There exist a number of libraries in the public domain [or with GNU type licenses] to help you do this. One is called apm* and can be found on several archive sites but I don't remember where.

Or do what many do: Manipulate your numbers in VARCHAR using SQL: Oracle never use binary floating point representation for their numbers.

E.g:

    VARCHAR x[21],y[21],z[21];
    .
    .
    EXEC SQL SELECT (:X + 2) * :Y INTO :Z FROM DUAL; Kludgy, inefficient but it works. If you have PL/SQL then use that to you avoid the database access for pure arithmetic.

-- 
Paul Beardsell                          SAM Business Systems Ltd
~~~~~~~~~~~~~~                          21 Finn House, Bevenden St,
pbeardsell_at_cix.compulink.co.uk          London, N1-6BN, UK.
psb_at_sambusys.demon.co.uk                (+44 or 0)71 608-2391