Sum result format is float(?)

[pali]

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!

[Dave Hughes]

Re: Sum result format is float(?)

pali wrote:

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!

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.