RoundUp sqlplus script 1

[Anto318]

Hi all,

I am new to this site and oracle sqlplus in general. I need some help, I need to write a script that will round a figure to the next highest number at a defined number of decimal places

e.g.
(3.2,0) gives 4
(76.9,0) gives 77
(3.14159,3) gives 3.142
(-3.14159,1) gives -3.2
31415.92654,-2) gives 31500

This is really wrecking my head if you could help I would really appreciate it.

Thanks in advance.

AM

 

[Welshbird]

(You might be better asking this is one of the other Oracle forums: 9i or 10g depending on what version you have)

If you want to just round a number then the round function ought to give you what you need:

select round(10.456, 2) from dual;

10.45

Does that help you?



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[carp]

First, round(10.456,2) will give you 10.46, not 10.45.
But I think what AM is after is a function that will always round UP. The ROUND function doesn't do this; round(10.452,2) would yield 10.45, but I believe AM wants 10.46. To my knowledge, Oracle supplies no such function, and a clever bit of SQL to do this does not come readily to mind. I'm working on creating a function to do this; barring a brighter mind providing the SQL, I'll post the results shortly.

 

[Anto318]

No sorry it needs to round UP to so from your example

(10.456,2)

the result will be 10.46

the script needs to round up the value not just round

another example is

(3.14159,3) would be 3.142

I hope that clears it up thats why its driving me mad if it was just a round it would br fine.

Thanks for your quick response back though

 

[Anto318]

thank you very much carp, thats exactly what im looking for I hope you can help I have a very very basic knowledge of this stuff and this is way out of my range.

I hope you can help

 

[carp]

AM -
How about this:

CREATE OR REPLACE FUNCTION roundup(p_number IN NUMBER, 
                                   p_pos IN NUMBER DEFAULT 0) RETURN NUMBER IS
   l_result NUMBER;
BEGIN
   l_result := round(p_number, p_pos);
   IF (l_result < p_number) THEN -- ROUNDED DOWN
      l_result := l_result + (1/power(10,p_pos));
   END IF;
   RETURN l_result;
END;
/

Sample outputs:

SQL> select roundup(3.1452,3) from dual;

ROUNDUP(3.1452,3)
-----------------
            3.146

SQL> select roundup(3.1457,3) from dual;

ROUNDUP(3.1457,3)
-----------------
            3.146

SQL> select roundup(3.1457) from dual;

ROUNDUP(3.1457)
---------------
              4

 

[Anto318]

thank you very much i'll try that and report back.

Thank you again

 

[Anto318]

Works perfectly, thank you. I have one more that I can't get my head around if you have time would you be able to have a look at it carp?!

I don't want to be greedy but if you have time i'd really appreciate it.

Just leave a message if you have time

 

[carp]

AM - lay it on us. We're all here to help whenever we can.