Re: TO_NUMBER() and SUM() return DOUBLE
Date: 1998/02/27
Message-ID: <6d7obp$3hs$1_at_nnrp1.dejanews.com>#1/1
Thomas,
Thank you very much for your response.
I have to apologize for the tone I used in my original memo.
It just shows the level of frustration I reached dealing with
this issue.
It may be some uncommon problem, but in fact you were the
only one who responded and I do appreciate that.
Working with Oracle for the last 9 months I've become a
strong believer that Oracle is one of the best database products
on the market now.
However, I want to try and explain my troubles in more details.
Here is a little piece from the Oracle manual:
"Oracle7 Server SQL Reference Manual", Chapter "Elements of Oracle7 SQL" NUMBER Datatype
...
You can specify a fixed point number using the following form: NUMBER(p,s)
...
You specify an integer using the following form: NUMBER(p) is a fixed point number with precision p and scale 0. (Equivalent to NUMBER(p,0).)
...
You specify a floating point number using the following form: NUMBER is a floating point number with precision 38.
...
That means (or at least I read it that way) that if SUM() returns NUMBER datatype, it returns a floating point number which cannot possibly be an integer type in any operating system or programming environment. And the largest possible datatype is a DOUBLE (at least on Unix and Windows OSs).
But you are right: that's for the client application, Oracle doesn't care.
Anyway, when I talk about the type conversion, I mean the following: we add two or more integers and receive a floating point number. I never said that SUM() should return an INTEGER, but for me it sounds like a type conversion.
Just for your information, Sybase does NOT convert the type in SUM() function:
[select sum(user_id/3) from all_users] will return an integer, while
[select sum(user_id/3.0) from all_users] will return a float.
In Sybase.
But that besides the point.
Now, you say that my application is fetching the return values into a double. Let me explain how it works.
My application is a Query service, written in C++ using Rogue Wave
DBTools.h++ library. When it issues a query it receives back an object
of RWDBValue class type. RWDBValue::type() method returns
column type for each column of the underlying schema object.
My understanding is that Rogue Wave DBTools uses Oracle Access Library.
Internally it issues an OCI call to get the schema datatypes.
For this particular column in my view that's a SUM() calculated field
RWDBValue::type() method returns RWDBValue::Double.
In a switch...case statement that goes through all possible types:
::Int, ::Long, ::Float, etc.
As you can see it is NOT fetching the return values into a double.
It doesn't even care about the return values.
In fact, the underlying tables can be empty.
Again, my major problem is that I have a view, not a table. On the view I cannot reinforce datatypes of the columns. Tough luck...
Right now we are already doing what you suggested: Java client that uses the Query service, just ignores type mismatch exceptions and converts everything to character strings and back to the right type.
But it's just a temporary kludge -- in the original design the front end program should not know anything about the database objects, especially column data types.
Well, again, thanks for your response and your time.
I am just curious if this problem (well, may be feature) still exists in Oracle8.
Konstantin Rozenshteyn,
Dow Jones Markets
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Feb 27 1998 - 00:00:00 CET