Oracle round to nearest decimal places

Posted on 2007-10-19
Last Modified: 2012-06-27
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
Question by:mahjag
    LVL 13

    Expert Comment

    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.
    LVL 9

    Expert Comment

    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.

    Author Comment

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

    Accepted Solution

    I think you outlined what you need in the function already...

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

          -- some math functions, etc...

       WHEN precision = .01 THEN
          v_return := initValue;

          -- some math functions, etc...

          v_return := initValue;
      END CASE;

      RETURN v_return;


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

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
    This video shows how to recover a database from a user managed backup
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now