Why SELECT 123456.123456789123456 FROM Dual; returns 123456.123457 ?

Question

Why SELECT 123456.123456789123456 FROM Dual; returns 123456.123457 ?

How can I increase this precision ?

I ask this because I am summing up cost values defined as NUMBER(38,20) and I can notice that errors are accumulated, but since these numbers represent money, rounding up is unacceptable.

It is not only the tool I am using that rounds the number display, but the number itself is rounded, since the reports that are made also contain the accumulated errors .

Answer 1

Well, it doesn't. But as you say, the tool (e.g. SQL Plus) that displays the result may round it for display:

SQL> SELECT 123456.123456789123456 n from dual;

         N
----------
123456.123

SQL> column n format 9999999999.99999999999999999999999999999
SQL> SELECT 123456.123456789123456 n from dual;

                                        N
-----------------------------------------
     123456.12345678912345600000000000000

Here, SQL Plus rounded to 3 decimal places before I specified a format mask.

As for your report that is apparently rounding the data it sums, we probably need to see an example of the SQL and input/output data.

Answer 2

No, the (38,20) definition for the column should not affect the number:

SQL> create table test (x number(38,20));

Table created.

SQL> insert into test values (123456.123456789123456);

1 row created.

SQL> commit;

Commit complete.

SQL> select x - 123456.123456789123456 from test;

X-123456.123456789123456
------------------------
               0

If you are processing the numbers outside of the database you might be accumulating the errors there.

Answer 3

No, I am processing them inside the database, with a query that performs SUM