Home » SQL & PL/SQL » SQL & PL/SQL » Final Script Round
Final Script Round [message #210134] Tue, 19 December 2006 09:27 Go to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Hi all,

I've been landed with one last special scripts but I cannot figure out this one.

I hope ye can help me as I am pulling out my hair and this script is too advanced for my basic knowledge.

I have to write a script that will:

Round and display a figure to the next highest number at a defined number of significant figures

Example:
Round up to 2 significant figures the following
0.0215 answer 0.022
0.215 answer 0.22
2.15 answer 2.2
21.5 answer 22
215.00 answer 220
2150.00 answer 2200
2149.00 answer 2200

The closest I got has been:
Select to_number(value), round(to_number(value),-1*(floor(log(10,to_number(value)))-1)) from dual;


But with this code it doesn't round the way I need it to.

I hope ye can help.

Thank you in advance.
AM
Re: Final Script Round [message #222707 is a reply to message #210134] Tue, 06 March 2007 01:30 Go to previous messageGo to next message
ptgeorge
Messages: 19
Registered: April 2005
Junior Member
modifying your sql,

TO_NUMBER(VALUE)    ROUNDED
---------------- ----------
           .0215       .022
            .215        .22
            2.15        2.2
            21.5         22
             215        220
            2150       2200
            2149       2200
            2101       2200
            2175       2200


select to_number(value),
       round(to_number(value) +
               5 * power(10, floor(log(10,to_number(value)) - 2)),
               -1*(floor(log(10,to_number(value)))-1)) as rounded from dual



Just adding half the value of the highest place so it always rounds properly
Re: Final Script Round [message #222711 is a reply to message #222707] Tue, 06 March 2007 01:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
this would round 2100 to 2200.
Re: Final Script Round [message #222717 is a reply to message #222711] Tue, 06 March 2007 02:29 Go to previous message
ptgeorge
Messages: 19
Registered: April 2005
Junior Member
yup. Missed that. Multiply by 4.9 rather than 5. Or 4.9999999999 depending on the maximum no. of decimal places

or instead

     VALUE    ROUNDED
---------- ----------
     .0215       .022
      .215        .22
      2.15        2.2
      21.5         22
       215        220
      2150       2200
      2149       2200
      2101       2200
      2175       2200
      2100       2100
      2100       2100
      2199       2200
   2100.12       2200

select value,
       ceil(value * power(10,-1 * floor(log(10, value)-1)))
        * power(10,floor(log(10, value)-1)) as rounded
       from test2

[Updated on: Tue, 06 March 2007 03:14]

Report message to a moderator

Previous Topic: Datatype BLOB,CLOB in oracle
Next Topic: How to return recordset from procedure
Goto Forum:
  


Current Time: Fri Dec 09 13:29:45 CST 2016

Total time taken to generate the page: 0.08758 seconds