?
Solved

Oracle round to nearest decimal places

Posted on 2007-10-19
4
Medium Priority
?
12,623 Views
Last Modified: 2012-06-27
Hi
I am trying to find out if there is any function in oracle that will round off the decimal places, we have a column called price precision and users will enter a value like 0.1 and in calculations the programmer needs to use this value when he does units times unit price and then round off to nearest price precision number

for example let us say the units are 2.22 and unit price is 2 and precision price column is 0.1 then
unit times unit price is 4.44 and by having precision column as 0.1 the result should be 4.50
0
Comment
Question by:mahjag
  • 2
4 Comments
 
LVL 13

Expert Comment

by:rgautier
ID: 20107931
No rounding function that is built into a database product will likely give you 4.50 from 4.44.  Basic rounding functions will give you 4.4.  Apart from that, there should be a round() and trunc() function, where the truncate function never 'rounds up' but merely 'chops off'.  The round() function would round 4.46 to 4.5 however, but I doubt it would add the 0 at the end.

I would suggest writing a user defined function to do what you want to do instead.  It's a fairly simple function that would take two parameters, one for the precision to round to, the second for the precision to return from the function

float specialround(float input, int precision, int returnprecision)

Dealing with floats in databases can be dangerous though - and you may want to deal with numbers as integers (decimals) with a defined decimal place - where the database internally stores the numbers as '446' with a column definition of decimal(10,2), for example.

Floating point precision can end up screwing you if you don't handle the numbers carefully.
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20108105
I think it will involve a special case function that you always return for your price.  Create your function as rgautier suggested, then create a view on the table that returns:

SELECT special_fn( unit_price_column, precision_column ) returnValue, unit_price_column, precision_column, <other_columns>
FROM your_table;

I would then grant SELECT on the view to your developers and revoke select on the table, so no one gets confused or incorrect data.
0
 

Author Comment

by:mahjag
ID: 20109364
I see there is no inbuilt oracle function for this to accomplish, coud you guys outline what the special round function will do?
0
 
LVL 9

Accepted Solution

by:
joebednarz earned 150 total points
ID: 20110235
I think you outlined what you need in the function already...

FUNCTION customRound( initValue NUMBER, precision NUMBER )
RETURN NUMBER
IS
  v_return NUMBER;
BEGIN
   CASE
   WHEN precision = .1THEN
      v_return := initValue;

      -- some math functions, etc...

   WHEN precision = .01 THEN
      v_return := initValue;

      -- some math functions, etc...

   ELSE
      v_return := initValue;
  END CASE;

  RETURN v_return;

END;

How you fill in the "math functions" will be determined by your own rules and needs...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question