Rounding Script 1

[Anto318]

Hi all,

I need to write a script that will round a figure to the next highest number at a defined number of significant figures

examples of what I am looking for are:

0.0215 gives .022
0.215 gives 0.22
2.15 gives 2.2
21.5 gives 22
215.00 gives 220
2150.0 gives 2200
2149.0 gives 2200
21500.00 gives 22000

I am completely stumped with this.

If you can help I would really appreciate it.
Thank you in advance.
AM

 

[carp]

AM -
You already have this from your previous post:

  1* select roundup(.0215, 3) from dual
10:51:52 SQL> /

ROUNDUP(.0215,3)
----------------
            .022

10:51:53 SQL> select roundup(.215,2) from dual;

ROUNDUP(.215,2)
---------------
            .22

10:52:13 SQL> select roundup(2.15,1) from dual;

ROUNDUP(2.15,1)
---------------
            2.2

10:52:25 SQL> select roundup(21.5) from dual;

ROUNDUP(21.5)
-------------
           22

  1* select roundup(215.00, -1) from dual
10:53:19 SQL> /

ROUNDUP(215.00,-1)
------------------
               220

10:53:20 SQL> select roundup(2150,-2) from dual;

ROUNDUP(2150,-2)
----------------
            2200

10:53:34 SQL> select roundup(2149,-2) from dual;

ROUNDUP(2149,-2)
----------------
            2200

10:54:00 SQL> c/um/up
  1* select roundup(21500,-3) from dual
10:54:04 SQL> /

ROUNDUP(21500,-3)
-----------------
            22000

 

[Anto318]

I modified the code you gave me yesterday to work with my stuff and it came out like this

Select Case when round(3.14159, 3) < 3.14159 then round(3.14159, 3) + (1/power(10,3)) else round(3.14159, 3) end from dual;

Sorry to be annoying you but I am still completely lost, I can't modify the script to incorporate it into my script.

If you not too much too ask could you have a look at it, my head cant get into this today.

I hope I am not asking for too much, this is my last script to get done and i'd really appreciate it if you could help out.

Thank you very much for your help.
AM

 

[carp]

Not to worry - you're not being annoying; this is why this site exists. However, since the SQL you posted works it's clear you see how this works. So I have to assume the problem is how to transfer this technique to your script. Since you have not explained what problem you are encountering and you haven't posted the part of your script that is giving you trouble, there isn't much I can do for you.
Exactly what is the problem you're encountering?

 

[Anto318]

The problem I am having is trying to transfer the technique used into my script

There is one thing though.

The script that I need has to round to the next highest number at a defined number of significant numbers

Each of the examples I put down are rounded to 2 significant figures:

e.g.
Round 0.0215 to 2 significant figures answer 0.022
Round 0.215 to 2 significant figures answer 0.22
Round 2.15 to 2 significant figures answer 2.2
Round 215 to 2 significant figures answer 220
Round 2150 to 2 significant figures answer 2200

I hope you can help, it is a bit different to the code you gave above.

Thank you again.
AM

 

[carp]

I would suggest a second function (e.g., sig_posn) that you can pass a number to and it will determine the position of the second significant figure. Your invoking code could then pass the number and the position to the roundup function to produce the final value. I would also recommend that you make the sig_posn function more generic for greater flexibility. To do this, you would have a second argument that accepts a positive integer as the number of significant digits you were seeking. Once implemented, your invoking code might look something like this:

DECLARE
   l_number NUMBER := 227.153;
   l_posn NUMBER;
   l_rounded NUMBER;
BEGIN
   -- FIND POSITION OF SECOND SIGNIFICANT DIGIT
   l_posn := sig_posn(l_number, 2); 
   -- ROUND UP AT THE SECOND SIGNIFICANT DIGIT
   l_rounded := roundup(l_number, l_posn);
   -- OR THE MORE COMPACT VERSION
   -- ROUND UP AT THE THIRD SIGNIFICANT POSITION
   l_rounded := roundup(l_number, l_posn(l_number, 3));
END;

 

[carp]

Or, if you're working directly from SQL,

SELECT roundup(my_column, sig_posn(my_column,2))
FROM my_table;