Re: Strange precision related math errors...

In article <9ot325$vcj$03$1_at_news.t-online.com>, Andreas says...
>
>Hi,
>
>i have a table test with
> a number(14,2)
> b number(14,2)
> c number(16,4)
>
>
>If i (example from mind - numbers may be too small)
>
>select a,b,c,a/b,(a/b)/c,1-((a/b)/c) from test
>
>i get for example
>
>19558001,1000000,1.9558,1.9558001,1.000000051129,-5.1129
>
>well, all but the last value is correct.
>As soon as ((a/b)/c) has more than 4 0's before relevant
>digits, 1-((a/b)/c) does neither return -0.0000000051129
>nor 0 (what i would have expected), but shifts the
>result left til it is larger(abs) than 1 !!!
>
>This happens on both 8.0.4 and 8.1.7 ...
>
>What is the best way to get correct results without
>redefining the tables.
>
>
>
>--
> Andreas
>Who doesn't live can never die
>

I had to change your numbers (you seem to be missing a ZERO in b above). Once I did that I get:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( a number(14,2), b number(14,2), c number(16,4) );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values (
19558001,10000000,1.9558 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select a,b,c,a/b,(a/b)/c,1-((a/b)/c) from t;

         A          B          C        A/B    (A/B)/C 1-((A/B)/C)
---------- ---------- ---------- ---------- ---------- -----------
  19558001   10000000     1.9558  1.9558001 1.00000005  -5.113E-08

So 1-((a/b)/c) is not -5.1129 -- you seem to have chopped off the E-08 from that.

Lets see a cut and past from sqlplus like the above -- you are missing something -- what is the client program here?

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp