×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

RoundUp sqlplus script

RoundUp sqlplus script

RoundUp sqlplus script

(OP)
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

RE: RoundUp sqlplus script

(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:

CODE

select round(10.456, 2) from dual;

10.45

Does that help you?

Fee

The question should be Is it worth trying to do? not Can it be done?

RE: RoundUp sqlplus script

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.

RE: RoundUp sqlplus script

(OP)
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

RE: RoundUp sqlplus script

(OP)
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

RE: RoundUp sqlplus script

AM -
How about this:

CODE

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:

CODE

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

RE: RoundUp sqlplus script

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

Thank you again

RE: RoundUp sqlplus script

(OP)
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

RE: RoundUp sqlplus script

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close