Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: How to specify precision of number in SQL

    Hi,

    I have the value +3.84000020000000E+001 and would like it to be displayed as 3.84 so basically I would like to round it off to the 2nd decimal place and specify precision for the number of digits to display.. I found the ROUND function but can't find anything to specify the precision.. any thoughts? Thanks!

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2user, the ROUND function has two parameters. The first is the number you want to round and the second is the number of places to the right of the decimal point (the precision).

    Ex. ROUND(3.8451, 2) would return 3.85

    PS I am not sure if +3.84000020000000E+001 would be returned as a Decimal. You may need to use CAST to convert it if you want a different datatype.

  3. #3
    Join Date
    Dec 2002
    Posts
    123
    thanks for the reply -- this is what I get if I use the ROUND function --

    round(READINGVALUE, 2) = +3.84000000000000E+001

    doesn't seem to remove the scientific notation

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post

    Code:
    select decimal(+3.84000020000000E+001, 31, 2) 
    from sysibm.sysdummy1
    Result:

    38.40
    Lenny

  5. #5
    Join Date
    Dec 2002
    Posts
    123
    thanks, you're quite a db2 pro!

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2user, That is what the PS was for. I am not at a place where I could test to be sure so I tried to indicate you might need to convert it buy using either the CAST or DECIMAL function (as Lenny showed).

    By the way, Lenny77, will that Round or Truncate to 2 decimal places. What if the value was +3.84050020000000E+001? Would you get 38.4 or 38.5?

  7. #7
    Join Date
    Dec 2002
    Posts
    123
    thanks Stealth_DBA.. I just wasn't sure how to use the decimal function.. but you ask a good question since i want the number to be rounded and then cut off..

  8. #8
    Join Date
    Dec 2002
    Posts
    123
    I think this should do it in any case --

    decimal(ROUND(READINGVALUE,2), 31, 2)


    It turns out that this works just fine when I run it from the command line.. but I really want this value returned as a part of a stored procedure. I have an OUT parameter READINGVAL which is of DOUBLE type and decimal(ROUND(+3.84000020000000E+001,2), 31, 2) is assigned to it and returned.

    When I run the procedure, I still get it in the long scientific format --
    Parameter Name : READINGVAL
    Parameter Value : +3.84000000000000E+001

    Any way around this? Should I return it as a string instead?

    I tried it with the output parameter as DECIMAL type where I'm doing something like --

    SET READINGVAL = DECIMAL(ROUND(+3.84000020000000E+001,2), 31, 2); and it returns "38." instead of "38.40"

    Thanks!
    Last edited by db2user; 10-19-09 at 14:58.

  9. #9
    Join Date
    Dec 2002
    Posts
    123
    I figured it out.. by setting the output parameter as DECIMAL(31,2) instead of DECIMAL..

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Good job

    Quote Originally Posted by db2user
    I figured it out.. by setting the output parameter as DECIMAL(31,2) instead of DECIMAL..
    Good job for user !

    Lenny

Posting Permissions

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