Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Unhappy Unanswered: Sum of Decimal columns of different length

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The issue is I am not getting the proper sum
    What do you mean by "proper sum"?

    Sum of Decimal columns of different length
    You may want to say "different precision and scale".
    Last edited by tonkuma; 09-30-10 at 13:17.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    IBM DB2 Version 9 for Linux, UNIX, and Windows SQL Reference, Volume 1
    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)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •