This post about Rounding up and down is one in a series of articles drawing on content that we have provided to the Oracle Intelligent Advisor public Forum. Some of you may know that the historic forum was taken out of service about 10 months ago and replaced with a new system. This does not replace Customer Connect, which requires (as the name suggests) a customer ID. The public forum is a place for people to ask questions, provide feedback, add comments in a spirit of community assistance. Sometimes the Forum provides enough information for the question to be closed, or sometimes it is moved to a Service Request in the Support site. So it is a great place to read about what people are doing with Oracle Intelligent Advisor, and to participate in discussions and help your peers. If you have not already registered, it is free and takes seconds. We thoroughly recommend it.

The question we are looking at today is in relation to a common business requirement in the area of rounding (up / down) numbers in Oracle Intelligent Advisor. There is a standard function Round() which is provided. However this function will provide rounding up or down, automatically, depending on the value of the input. The developer or rule writer has no control over the rounding. If the number provided is above the threshold, it is rounded up. If it is below the threshold it is rounded down.

So for example, if the input value is 3.4 , the output for Round(x, 0) would be 3. The function understands that there should be zero decimals, and rounds down to 3. If the input was 3.5, the output would be 4, the rounding will automatically switch to rounding *up*. There is no way to force the rounding to be in only one direction (for whatever business purpose, such as a rule which says amounts are always rounded *down* to the nearest currency unit, as is common in some tax legislation).

So achieving the desired result is going to need a rule to do it for us. We shall use, for example (this is not the shortest way to achieve the desired result but it is the easiest to demonstrate for learning purposes) :

Trunc() which allows us to truncate a number to remove any decimals. So Trunc(X,0) would produce 3 for any input in the following :3.1, 3.2,3.3,3.4,3.5…3.9999

Once we have that “root” we can create whatever rules we decide meet our requirement for rounding. For example, suppose that we say *anything* above flat 3.0 should be rounded *up* – so 3.1 should be rounded up to 4. We might examine the truncated number and compare it to the original. For example:

Original Number : 3.1

Truncated Number : 3

If (original number – truncated number) = 0 then it was a flat 3.0. The output is the same as the truncated number.

If (original number – truncated number) > 0 then it was 3.x. The output should add 1 to the truncated number to give 4 as the answer.

These are just some examples. Ultimately you could both come up with your own rules and also shorten the process into a single rule. But this simplified multi-step appproach helps you understand the process, and an Interview can help people understand the results of the different steps in the rounding:

You can read more about mathematical functions in Oracle Intelligent Advisor, including the rounding function and the trunc() function in the online help. If you want the Zip file that goes with this example it is free to download in the Shop (filed under Demo Projects).