OraFAQ Forum: SQL & PL/SQL » Final Script Round

Home » SQL & PL/SQL » SQL & PL/SQL » Final Script Round

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Final Script Round [message #210134]

Tue, 19 December 2006 09:27

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

Report message to a moderator

Re: Final Script Round [message #222707 is a reply to message #210134]

Tue, 06 March 2007 01:30

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

Report message to a moderator

Re: Final Script Round [message #222711 is a reply to message #222707]

Tue, 06 March 2007 01:50

Frank
Messages: 7901
Registered: March 2000

Senior Member

this would round 2100 to 2200.

Report message to a moderator

Re: Final Script Round [message #222717 is a reply to message #222711]

Tue, 06 March 2007 02:29

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 02:01:30 CST 2024