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.