Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    USA
    Posts
    3

    Unanswered: multiplying or dividing two colums

    Hi
    Whenever I try to multiply or divide two columns in the select statement
    like this:

    SELECT col1
    , col2*col3 as Col_Price
    , col4/col5 as Col_Percent

    FROM db2.tableName
    where .....

    I get bad results, most of the time the result numbers are few decimal points off. Instead of 1.33 I would get .33 and etc.
    But it is not consistent.
    Has any one ran into this before?

    thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: multiplying or dividing two colums

    Can you post the contents of the table also ....

    Are you the only user or are there any other ?

    Cheers

    Sathyaram


    Originally posted by netB
    Hi
    Whenever I try to multiply or divide two columns in the select statement
    like this:

    SELECT col1
    , col2*col3 as Col_Price
    , col4/col5 as Col_Percent

    FROM db2.tableName
    where .....

    I get bad results, most of the time the result numbers are few decimal points off. Instead of 1.33 I would get .33 and etc.
    But it is not consistent.
    Has any one ran into this before?

    thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: multiplying or dividing two colums

    Originally posted by netB
    Hi
    Whenever I try to multiply or divide two columns in the select statement
    like this:

    SELECT col1
    , col2*col3 as Col_Price
    , col4/col5 as Col_Percent

    FROM db2.tableName
    where .....

    I get bad results, most of the time the result numbers are few decimal points off. Instead of 1.33 I would get .33 and etc.
    But it is not consistent.
    I think these must be rounding errors. Do you define your columns as INT, DECIMAL, or REAL? If they're INT or DECIMAL I would suggest casting them into REAL before computations, like this:

    REAL (col2) * REAL (col3)

    Hope this helps.

    Nick

  4. #4
    Join Date
    Jul 2003
    Location
    USA
    Posts
    3
    Thanks for reply,
    I keep getting the same error, looks like a rounding problem:
    When i do this:
    REAL(RCOS.QUANTITY_ORDERED) * REAL(RCOS.SALES_PRICE)
    AS ORDER_AMT

    this is what I get:
    Quantity----Price------------Order_Amt
    6------------3.63 ------------21.780000686645508
    6------------3.63------------21.780000686645508
    30------------.11------------33.000011444091797
    18------------.11------------19.800006866455078
    30------------.11------------33.000011444091797
    30------------.11------------33.000011444091797
    12------------.91 ------------10.920000314712524

    It is right for some values, but look at rows where Price = .11

    When it was just:
    RCOS.QUANTITY_ORDERED * RCOS.SALES_PRICE
    AS ORDER_AMT

    This is what I got:
    Quantity----Price---------- Order_Amt
    6------------3.63------------21.78
    6------------3.63 ------------21.78
    30 ------------.11------------.33
    18------------.11------------1.98
    30------------.11 ------------.33
    30------------.11------------.33
    12------------.91------------10.92

    Again look where price = .11, it is still wrong just on the other side

    Quantity is defined as decimal(9,0)
    Pric as decimal(7,2)

    Thanks....
    Last edited by netB; 07-03-03 at 14:32.

  5. #5
    Join Date
    Jul 2003
    Location
    USA
    Posts
    3
    Never mind guys,
    I found the error,
    DB2 is fine,

    I was testing that through query analizer in sql server
    like: select * from openquery(db2 ......)
    for some reasons that brings bad results on rounded data, but when I actually ran this query on db2, it's fine.

Posting Permissions

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