Sum of Decimal columns of different length

dinesh1985 · #1 (**permalink**) 09-30-10, 08:43

Hello Folks,
I am very new to DB2

PLease help me with the below problem which I am facing

I am trying sum three columns by joining three tables. Two of the columns are from the Table B and the third column is from Table C.
Two columns from Table B are of type Decimal (7,2) and
Column from Table C is of type Decimal (8,2).
The issue is I am not getting the proper sum when I am adding
like this
Sum(Table B.Coulmn1 + Table B.Coulmn2 + Table C.Column3)

Example of query which I am using

Code:

SELECT a.DLR,a.INVOICE,sum (B.amt1+B.misc_amt+C.amt3)  FROM Table1 A,
Table2 B,
Table3 C 
WHERE A.emp_id=B.emp_id 
AND 
B.emp_id=C.emp_id 
AND 
A.record_type IN (1,4,5,7) group by a.dlr,a.invoice having a.dlr='89890' with ur

Please help me with this. Am I wrong doing a sum as above.

tonkuma · #2 (**permalink**) 09-30-10, 11:16

Quote:

The issue is I am not getting the proper sum

What do you mean by "proper sum"?

Quote:

Sum of Decimal columns of different length

You may want to say "different precision and scale".

tonkuma · #3 (**permalink**) 09-30-10, 12:04

IBM DB2 Version 9 for Linux, UNIX, and Windows SQL Reference, Volume 1

Quote:

SUM( expression )

...
If the data type of the argument values is decimal,
the precision of the result is 31 and the scale is the same as the scale of the argument values. ...
...

The result data type of "sum (B.amt1+B.misc_amt+C.amt3)" may be
sum ( DEC(7,2) + DEC(7,2) + DEC(8,2) )
:= sum ( DEC(9,2) )
:= DEC(31,2)

Internet Services

Web Development

Digital Marketing

Consumer Tech