Re: TO_NUMBER() and SUM() return DOUBLE
Date: 1998/02/26
Message-ID: <34f58be5.5355801_at_192.86.155.100>#1/1
A copy of this was sent to krozen_at_bway.dowjones.com (if that email address didn't require changing) On Wed, 25 Feb 1998 14:51:00 -0600, you wrote:
>Here is the description of the problem we are having with Oracle (ver. 7.3.3).
>
>Our database has two tables that are combined in a certain way in a view
>to be queried by the front-end Java based client.
>This view has some calculated columns, with SUM and TO_NUMBER
>functions applied to them.
>The front-end client totally relies on the database to provide the right
>data type for each column in the view. That means that it doesn't even know
>the data types of these columns.
>Now, the real problem is that Oracle functions SUM() and TO_NUMBER()
>both return a NUMBER data type. This type is specific to Oracle and by
>default (without any precision specifier) it is equivalent to DOUBLE. It is
>implemented in this way to accommodate all possible numeric types.
>For any specific type you have to use the precision: NUMBER(9) is for
>integer, NUMBER(9,2) is for float, etc.
>Unfortunately, SUM() and TO_NUMBER() both return type double.
>Again, we have a view and we cannot enforce a data type on a column
>(like we would be doing it on a table).
>So, the front-end application trying to validate an integer field gets
>an exception of invalid type.
>
Neither sum nor to_number return DOUBLE, they return NUMBER and NUMBER is not
the same as DOUBLE. An Oracle number allows for 38 digits of precision, its
much larger in precision then a double is today on 16, 32, or 64 bit machines.
Your application is fetching them into a double.
Have you considered performing a to_char() on the result of SUM() and dealing with the number as a string in your application?
sum returns a NUMBER -- it can be a floating point number, it can be an integer. Its not the sum() or to_number even return doubles -- its that the front end cilent you are using or building interprets them that way -- Oracle, as you noted, doesn't do doubles natively. Your application is fetching them into a double. Can you convince your application to fetch it into a LONG or an INT?
>Oracle tech support is aware of the problem but is not capable (and I
>mean it) of providing any solution. I guess, for them it means to acknowledge
>that they redefine SQL standard (SUM function is a standard, right?) and
>convert the return type.
>
but sum can (and does) sum 12.12 and 11.5 -- if it returned a truncated integer as a result of adding two numbers with decimal places lots of people would be very upset as well.
there is nothing in the world that says SUM() returns an INTEGER, in fact:
1* select sum(user_id/3) from all_users SQL> / SUM(USER_ID/3)
1797963.33
Converting to a string should fix your issue, select to_char( sum(x) ) from T; If your application needs it as an int, fetch it into an int.
>Any help will be REALLY appreciated.
>
>K.R.
>
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Feb 26 1998 - 00:00:00 CET