Why does Oracle give decimal 9999.99999999999 not 10000 instead?

user_16916007 0
Viewed 158
SUM(DECODE(cod_drcr, 'D', -amt_txn, amt_txn)) OVER(ORDER BY dat_txn RANGE UNBOUNDED PRECEDING) "Balance"

Given this query the result gives 9999.99999999999 while the result should be 10000. The final result returns -1E-9.

Below is part of the DDL

  CREATE TABLE "SCHM"."TRANSACTION"    (   "DAT_TXN" DATE,
       "COD_DRCR" CHAR(1),
       "AMT_TXN" NUMBER DEFAULT 0,
        CHECK (cod_drcr     IN ('C', 'D')) ENABLE NOVALIDATE    ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 90 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 1048576 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "CASEDATA3"

What is the root cause?

Currently, I use the database 19c version, but the data inserted into this table in 2012.

Image of problem

Data dump on another table

And also, when I'm trying select amt_txn, asciistr(amt_txn) from name_table the result of the error number is 17910295.11, 17910295.110000001 .

Another question is, "Why does the number can be inserted like this into the database"?

oracle sql
1 Answers

Don't store numbers as BINARY_DOUBLE or BINARY_FLOAT; store them using NUMBER.

CREATE TABLE table_name (
  cod_drcr VARCHAR2(1),
  amt_txn  BINARY_DOUBLE,
  dat_txn  DATE
);

INSERT INTO table_name (cod_drcr, amt_txn, dat_txn)
SELECT 'C',   10000,    DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 'C', 1510000,    DATE '2021-01-02' FROM DUAL UNION ALL
SELECT 'D', 1509364.59, DATE '2021-01-03' FROM DUAL UNION ALL
SELECT 'D',     635.41, DATE '2021-01-04' FROM DUAL UNION ALL
SELECT 'C',  300000,    DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 'D',  300000,    DATE '2021-01-06' FROM DUAL UNION ALL
SELECT 'C',  300000,    DATE '2021-01-07' FROM DUAL UNION ALL
SELECT 'D',  300000,    DATE '2021-01-08' FROM DUAL UNION ALL
SELECT 'C',  800000,    DATE '2021-01-09' FROM DUAL UNION ALL
SELECT 'D',  800000,    DATE '2021-01-10' FROM DUAL UNION ALL
SELECT 'C', 1000000,    DATE '2021-01-11' FROM DUAL UNION ALL
SELECT 'D', 1000000,    DATE '2021-01-12' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-13' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-14' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-15' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-16' FROM DUAL UNION ALL
SELECT 'D',    2000,    DATE '2021-01-17' FROM DUAL;

Then:

SELECT DECODE(cod_drcr, 'D', NULL, amt_txn) AS credit,
       DECODE(cod_drcr, 'D', amt_txn, NULL) AS debit,
       SUM(DECODE(cod_drcr, 'D', -amt_txn, amt_txn))
         OVER(ORDER BY dat_txn RANGE UNBOUNDED PRECEDING) "Balance"
FROM   table_name;

Outputs:

CREDIT DEBIT Balance
1.0E+004 1.0E+004
1.51E+006 1.52E+006
1.5093645900000001E+006 1.0635409999999916E+004
6.3540999999999997E+002 9.9999999999999163E+003
3.0E+005 3.0999999999999994E+005
3.0E+005 9.9999999999999418E+003
3.0E+005 3.0999999999999994E+005
3.0E+005 9.9999999999999418E+003
8.0E+005 8.1E+005
8.0E+005 1.0E+004
1.0E+006 1.01E+006
1.0E+006 1.0E+004
2.0E+003 8.0E+003
2.0E+003 6.0E+003
2.0E+003 4.0E+003
2.0E+003 2.0E+003
2.0E+003 0

If you use NUMBER:

CREATE TABLE table_name (
  cod_drcr VARCHAR2(1),
  amt_txn  NUMBER(15,2),
  dat_txn  DATE
);

With the same INSERT then the same query outputs:

CREDIT DEBIT Balance
10000 10000
1510000 1520000
1509364.59 10635.41
635.41 10000
300000 310000
300000 10000
300000 310000
300000 10000
800000 810000
800000 10000
1000000 1010000
1000000 10000
2000 8000
2000 6000
2000 4000
2000 2000
2000 0

db<>fiddle here

user_15092640
Related