Itâ€™s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Can someone please help me with this. I have a table RT with two columns - ID (VARCHAR2(2)) and rtt (NUMBER). Half of them is marked as EM, half as BI. As first query shows those parts are identical. So why does sum differ?!?

SQL> SELECT rtt

2 FROM RT

3 WHERE ID = 'EM'

4 MINUS

5 SELECT rtt

6 FROM RT

7 WHERE ID = 'BI'

8 /

no rows selected

SQL> SELECT SUM(rtt)

2 FROM RT

3 WHERE ID = 'EM'

4 MINUS

5 SELECT SUM(rtt)

6 FROM RT

7 WHERE ID = 'BI'

8 /

SUM(RTT)

----------

3.04844

Also none of those numbers has more then 41 digits after comma:

SQL> SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 42)

2 /

no rows selected

I'd expect oracle to provide support up to 127 digits after comma. Is there any doc that could explain this or maybe I'm missing something?

In NUMBER reference (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45443) there is:

NUMBER Datatype

The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, then Oracle returns an error.

Specify a fixed-point number using the following form:

NUMBER(p,s)

where:

p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.

s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

Specify an integer using the following form:

NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify a floating-point number using the following form:

NUMBER

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

SQL> SELECT rtt

2 FROM RT

3 WHERE ID = 'EM'

4 MINUS

5 SELECT rtt

6 FROM RT

7 WHERE ID = 'BI'

8 /

no rows selected

SQL> SELECT SUM(rtt)

2 FROM RT

3 WHERE ID = 'EM'

4 MINUS

5 SELECT SUM(rtt)

6 FROM RT

7 WHERE ID = 'BI'

8 /

SUM(RTT)

----------

3.04844

Also none of those numbers has more then 41 digits after comma:

SQL> SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 42)

2 /

no rows selected

I'd expect oracle to provide support up to 127 digits after comma. Is there any doc that could explain this or maybe I'm missing something?

In NUMBER reference (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45443) there is:

NUMBER Datatype

The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, then Oracle returns an error.

Specify a fixed-point number using the following form:

NUMBER(p,s)

where:

p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.

s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

Specify an integer using the following form:

NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify a floating-point number using the following form:

NUMBER

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

SQL> set num 50

SQL> SELECT (SELECT SUM(rtt) FROM RT WHERE ID = 'EM') - (SELECT SUM(rtt) FROM RT WHERE ID = 'BI') diff

2 FROM dual

3 /

DIFF

--------------------------

.0000000000000000000000000

I noticed one thing more: when I remove rows with 42 digits after comma (32 of 172) it matches!

SQL> SELECT SUM(rtt)

2 FROM RT

3 WHERE ID = 'EM'

4 AND rtt NOT IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))

5 MINUS

6 SELECT SUM(rtt)

7 FROM RT

8 WHERE ID = 'BI'

9 AND rtt NOT IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))

10 /

no rows selected

SQL> SELECT SUM(rtt)

2 FROM RT

3 WHERE ID = 'BI'

4 AND rtt IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))

5 MINUS

6 SELECT SUM(rtt)

7 FROM RT

8 WHERE ID = 'EM'

9 AND rtt IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))

10 /

no rows selected

Now I'm totally confused.

Look at the following:

A. No formatting

SQL> select 23.345616534343434343434 from dual;

23.345616534343434343434

------------------------

23,3456165

SQL>

B. With formatting

column K format 999,999,999.99999999999999

select 23.345616534343434343434 K from dual

SQL> column K format 999,999,999.99999999999999

SQL> select 23.345616534343434343434 K from dual

2 /

K

--------------------------

23.345616534343434

Please take a look at two queries in my original question - table has two sets of rows (one marked as 'EM' one as 'BI'). Identical (excluding ID) rows. When I SUM them result differs by 1 * 10^(-38) !!!

I do not believe you can use MINUS selecting group functions so easy.

May be a good approach is:

SELECT sum(g.rtt)

FROM

( SELECT rtt

FROM RT

WHERE ID = 'EM'

MINUS

SELECT rtt

FROM RT

WHERE ID = 'BI') g;

I'm pretty sure minus/union/intersect work with aggregates as well as without them. But if You don't like it - see this:

SQL> set num 50

SQL> SELECT (SELECT SUM(rtt) FROM RT WHERE ID = 'EM') - (SELECT SUM(rtt) FROM RT WHERE ID = 'BI') diff

2 FROM dual

3 /

DIFF

--------------------------

.0000000000000000000000000

So if You keep in mind that rows with 'EM' are same as those with 'BI' it's a little strange, isn't it?!? That's why I'm stuck here and have no idea what's happening...

The task I'm trying to accomplish is to check if two sums are identical - and this is my problem; they aren't. But all rows taking part in this sum are perfectly identical! My temporary solution is to round results to 20 digits after comma and then compare them - but of course I'd prefer to compare entire sum.

Your approach can work only with integers. But Oracle NUMBER type in fact always is a special, very special kind of IEEE floating point numbers.

In floating-point world it is suicide to write:

IF x=y then ....

Instead this you have to write:

if abs(x-y) <= 0.000001 then .....

Sorry, this is the numbers life ...

The only thing i can think of is that there if there are more than one value attached to id but a different amount attached to the other....but in total there are the same amount for each ID This could lead to you scenario, see below:

If this does not help, them you have stumped me.

Name Null? Type

----------------------- -------- ----------------

ID VARCHAR2(2)

RTT NUMBER

select * from TEMP_TEMP order by ID ;

ID RTT

-- ----------

BI 1

BI 1

BI 2

BI 2

EM 1

EM 1

EM 1

EM 2

SELECT rtt

FROM TEMP_TEMP

WHERE ID = 'EM'

MINUS

SELECT rtt

FROM TEMP_TEMP

WHERE ID = 'BI';

no rows selected

SELECT SUM(rtt)

FROM TEMP_TEMP

WHERE ID = 'EM'

MINUS

SELECT SUM(rtt)

FROM TEMP_TEMP

WHERE ID = 'BI';

SUM(RTT)

----------

5

No, this is not the case.

SQL> ed

Wrote file afiedt.buf

1 select sum(case id when 'BI' then 1 else 0 end) - sum(case id when 'EM' then 1 else 0 end) diff, rtt

2 from rt

3 group by rtt

4* having sum(case id when 'BI' then 1 else 0 end) - sum(case id when 'EM' then 1 else 0 end) != 0

SQL> /

no rows selected

schwertner:

I'm affraid that finally I'll have to agree with You...I can't find other explanation...but...

...in my specific situation it doesn't make sense! This is how do I see it: Oracle provided some quantity of bytes for storing my value (I mean on disk and in memory). The same quantity for all of those numbers. I've checked one by one - they're identical. When it sums them required precission cannot increase. So it doesn't need more space for result then for each of them. So there is no room for rounding/truncating them. So results for identical sources should be equal. But they are not.

FROM

(SELECT rtt

FROM TEMP_TEMP

WHERE ID = 'EM') a,

(SELECT rtt

FROM TEMP_TEMP

WHERE ID = 'BI') b;

But the query

SELECT SUM(rtt)

FROM TEMP_TEMP

WHERE ID = 'EM'

MINUS

SELECT SUM(rtt)

FROM TEMP_TEMP

WHERE ID = 'BI';

is crystal clear:

SELECT SUM(rtt)

FROM TEMP_TEMP

WHERE ID = 'EM'

selects

5

SELECT rtt

FROM TEMP_TEMP

WHERE ID = 'BI';

selects

6

These are different,

so the query returns 5

The Oracle documentation says that Oracle guarantees precision up to 38 digits.

It seems that Oracle, at least in SQL, is rounding the NUMBER datatype to the 40 digit when you haven't specified precision. I'm not sure how your data was stored to hold more than 40 digits even though it's defined as just NUMBER.

My guess is that in your queries Oracle is doing an implicit conversion of your data to NUMBER, thus rounding it to 38 or 40 digits - my tests showed it rounding to 40 digits, but I'm not sure if that's the case on all versions of the database. So the sum is rounded to let's say 40 digits, which causes the difference. You said that if you ignore the rows with 42 digits everything matches, so there probably is a difference in the 42-nd digit for some of the rows.

What makes it even more interesting is that you happened to have a case where the rounded individual numbers match, but the rounded sum doesn't, i.e. the individual numbers end in digits either less than 5 or more than 6, but with the sums one rounds down and the other up.

Here's an example which shows how Oracle rounds NUMBER to the 40-th digit.

I have a table (I won't show the create statements, it's pretty simple) which has one column defined as NUMBER and a unique constraint on it:

SQL> INSERT INTO ttt VALUES (0.12345678901234567890123

1 row created.

SQL> INSERT INTO ttt VALUES (0.12345678901234567890123

INSERT INTO ttt VALUES (0.12345678901234567890123

*

ERROR at line 1:

ORA-00001: unique constraint (X.UQ_TTT_AAA) violated

SQL> INSERT INTO ttt VALUES (0.12345678901234567890123

INSERT INTO ttt VALUES (0.12345678901234567890123

*

ERROR at line 1:

ORA-00001: unique constraint (X.UQ_TTT_AAA) violated

SQL> INSERT INTO ttt VALUES (0.12345678901234567890123

1 row created.

SQL> set num 50

SQL> select * from ttt;

AAA

--------------------------

.1234567890123456789012345

.1234567890123456789012345

SQL> INSERT INTO ttt VALUES (0.12345678901234567890123

1 row created.

SQL> select * from ttt;

AAA

--------------------------

.1234567890123456789012345

.1234567890123456789012345

.1234567890123456789012345

Again, I'm not sure if this answers your question, but I hope it helps!

The native Oracle numbers are not intended to be used for complex calculations, because they are

not native to the processors and also PL/SQL ( the only native Oracle tool for writing procedural programs

is interpreter, not compiler). So Oracle numbers are for limited use in the case of complex calculations.

I have heard that in 10g Oracle has introduced also the native floating point numbers in IEEE

standart. This means that one can write c, C++ programs to do complex calculations.

I understand that this is not the expected answer, but this is the state of the art in my opinion.

I am interested in this. We should be able to find out the reason for that.

could you show me your table definition? and some of your data look like?

let me know if you need help on doing this.

acton

actonwang - table definition is quite simple; to show strange results it's enough

create table t(id number, val number)

Some of my examples don't even need two columns.

Now I'm at home and I'm unable to show You examples that I have found. Anyway I can give brief description of the results. I have strong feeling that this is very rare case of specially prepared data.

One experiment was like that - I put one of those special values into a table. It was sth like 0.012500000000000000023418

When I ran update t set col = col + 0.1; update t set col = col - 0.1; the value remained the same. But when I ran update t set col = col + 1; update t set col = col - 1; it changed it value! (by about 10^(-38)).

Another - I took this special value, it look like 0.087500000000000013461347

I hope that I'll find some time tommorow to show You some copy&paste from sqlplus and those specific numbers that cause problems.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

You make a fundamental error trying to use Oracle numbers for pure "scientific" calculations.

What you try to do belongs to "scientific algebra"!

Believe me! I graduated mathematics and am old Assembler programmer and now the number coding systems and application

of the numbers of all computers beginning with IBM-360.

Oracle numbers are not designed for such calculations.

They are dereivate (modification) of IBM's "packed numbers"

and are designed to work with economics data - mainly integers and with small fraction.

If you want to calculate "scientific data" use C or C++ and native floating point

numbers. I believe they can be stored in the Oracle as RAW columns.