Sum result format is float(?)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pali - 10 Apr 2008 01:55 GMT

I am running following query using Aqua Studio 4.1/AS400 DB2

select sum(amount) from table1
amount column is a decimal(11,2)

I am expecting a numeric total with 2 decimal points but my result
looks like this 5.8801792972E8 (float?)

I tried:
select sum(cast(amount as decimal(12,2)) from table1
select cast(sum(amount) as decimal(12,2)) from table1
and got the same results.

This appears to be something pretty basic that I am missing. Any
suggestions?

Thanks!

Reply to this Message

Dave Hughes - 10 Apr 2008 02:44 GMT

> I am running following query using Aqua Studio 4.1/AS400 DB2
>
[quoted text clipped - 13 lines]
>
> Thanks!

It may be the client itself translating this into floating point. Try
performing the query from the DB2 command line with DESCRIBE to output
the types of the columns in the result, e.g.:

DESCRIBE SELECT SUM(AMOUNT) FROM TABLE1

If this tells you that the result is a DECIMAL, then it's Aqua Studio
doing the conversion (i.e. if it has no decimal fixed-point
capabilities). Unfortunately I've seen this sort of behaviour in
several database clients (Brio springs to mind).

Cheers,

Dave.

Reply to this Message

pali - 10 Apr 2008 09:27 GMT

> > I am running following query using Aqua Studio 4.1/AS400 DB2
>
[quoted text clipped - 28 lines]
>
> Dave.

Thanks Dave. I tried the 'describe' command earlier but it appears
Aqua Data does not support the command. It gave me the following
error.

DESCRIBE SELECT SUM(AMOUNT) FROM TABLE1
Error: [SQL0104] Token SUM was not valid. Valid tokens: INTO.

Do you know of a workaround for DB clients with limited fixed-point
capabilities?

Which DB client would you recommend for use with DB2/400? I like the
interface of Aqua Studio as it is the closest (free) client I've seen
to SQL Server's Query Analyzer which I am most comfortable with.

Thanks!
Pali

Reply to this Message

Karl Hanson - 10 Apr 2008 13:39 GMT

>>> I am running following query using Aqua Studio 4.1/AS400 DB2
>>> select sum(amount) from table1
[quoted text clipped - 36 lines]
> interface of Aqua Studio as it is the closest (free) client I've seen
> to SQL Server's Query Analyzer which I am most comfortable with.

(Fyi, it hasn't been "DB2/400" for many years.. at the moment it's "DB2
for i5/OS", but the OS is now "IBM i", so stay tuned :)

Below is what I get using STRSQL on a green-screen:
===> strsql
* > create table sumtst (amount dec(11,2))
* > insert into sumtst values(588017929.72)
* > select sum(amount) from sumtst
* ....+....1....+....2....+....3....+....4..
* SUM ( AMOUNT )
* 588,017,929.72
* ******** End of data ********

The result for the same query using iSeries Navigator appears:
588017929.72

Could you use the Run SQL Scripts feature of iNav?

--
Karl Hanson

Reply to this Message