Oracle SQL rounding method 1

[hnhranch2]

How do I get Oracle SQL to use the bankers rounding method instead of the symmetric arithmetic rounding method? Or has anyone written a function to do this in Oracle? MS Visual Basic defaults to this method but Oracle does not.

 

[SantaMufasa]

HNH,

I'm not certain which method you mean by "Bankers' Rounding Method", but Oracle can handle them all:

SQL> select round(1234.56789,2) from dual;

ROUND(1234.56789,2)
-------------------
            1234.57

SQL> select floor(1234.56789) from dual;

FLOOR(1234.56789)
-----------------
             1234
SQL> select ceil(1234.56789) from dual;

CEIL(1234.56789)
----------------
            1235
SQL> select trunc(1234.56789,2) from dual;

TRUNC(1234.56789,2)
-------------------
            1234.56

Let us know which one you meant. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[hnhranch2]

I am using an Oracle 8i database and from what I understand, the round function defaults to using the symmetric arithmetric rounding (or round-half-up) method. I am trying to to use the round-to-even method (or unbiased rounding, convergent rounding, statistician's rounding, dutch rounding, gaussian rounding, or banker's rounding).

Example (select round(45.4500,1) from dual) ==> 45.5

I am looking for 45.4 by round-to-even method.

 

[SantaMufasa]

HNH,

If you can explain, step-by-step, the algorithm you want, you/we can build a function to apply that algorithm. If you post it, we will code. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[jimirvine]

bankers rounding

 

[SantaMufasa]

Thanks, Jim. Given your direction on the full explanation, here is code that HNH could use:

Section 1: User-defined function, "BANKERS_ROUND":

create or replace function bankers_round (val number, rnd_digit number) return number is
begin
    if val = trunc(val,rnd_digit+1) and mod(substr(trunc(val,rnd_digit),-1),2) = 0 then
        return trunc(val,rnd_digit);
    else
        return round(val,rnd_digit);
    end if;
end;
/

Function created.

Section 2 -- Sample invocations:

select bankers_round(123.4550001,2) from dual;

BANKERS_ROUND(123.4550001,2)
----------------------------
                      123.46

select bankers_round(123.4450001,2) from dual;

BANKERS_ROUND(123.4450001,2)
----------------------------
                      123.45

select bankers_round(123.445,2) from dual;

BANKERS_ROUND(123.445,2)
------------------------
                  123.44

select bankers_round(123.455,2) from dual;

BANKERS_ROUND(123.455,2)
------------------------
                  123.46

select bankers_round(123.5,0) from dual;

BANKERS_ROUND(123.5,0)
----------------------
                   124

select bankers_round(124.5,0) from dual;

BANKERS_ROUND(124.5,0)
----------------------
                   124

Let me know if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

Nice function, Santa. Just a few tweaks to make it work exactly the same way the ROUND() function does...

create or replace function bankers_round (val number, rnd_digit number := 0) return number is
    v_rnd_digit  number;
    v_remainder  number;
begin
    v_rnd_digit := trunc(rnd_digit);

    v_remainder := (val - trunc(val,v_rnd_digit)) * power(10,v_rnd_digit + 1);

    if v_remainder < 5
    or (v_remainder = 5 and mod(trunc(val * power(10,v_rnd_digit)),2) = 0) then
        return trunc(val,v_rnd_digit);
    else
        return round(val,v_rnd_digit);
    end if;
end;
/

This version copes with cases where the decimal places parameter is missing, non-integer or negative:

SQL> select bankers_round(14.5) from dual

BANKERS_ROUND(14.5)
-------------------
                 14


SQL> select bankers_round(14.51) from dual

BANKERS_ROUND(14.51)
--------------------
                  15


SQL> select bankers_round(13.5) from dual

BANKERS_ROUND(13.5)
-------------------
                 14


SQL> select bankers_round(14.45, 1.5) from dual

BANKERS_ROUND(14.45,1.5)
------------------------
                    14.4


SQL> select bankers_round(145, -1) from dual

BANKERS_ROUND(145,-1)
---------------------
                  140


SQL> select bankers_round(145.1, -1) from dual

BANKERS_ROUND(145.1,-1)
-----------------------
                    150


SQL> select bankers_round(135, -1) from dual

BANKERS_ROUND(135,-1)
---------------------
                  140

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[gmmastros]

I was just working on a similar function (for Microsoft SQL Server) and a google search led me here. I think there is a slight problem with the function posted by Chris Hunt. It appears to be working correctly for positive numbers, but not for negative numbers. I think this is simple enough to correct by modifying the code slightly.

Now, please understand that I know nothing about Oracle, so the code I post may not be correct. If it's not, then I encourage someone to fix it for me.

create or replace function bankers_round (val number, rnd_digit number := 0) return number is
    v_rnd_digit  number;
    v_remainder  number;
begin
    v_rnd_digit := trunc(rnd_digit);

    v_remainder := (val - trunc(val,v_rnd_digit)) * power(10,v_rnd_digit + 1);

    if [!]Abs([/!]v_remainder[!])[/!] < 5
    or ([!]Abs([/!]v_remainder[!])[/!] = 5 and mod(trunc(val * power(10,v_rnd_digit)),2) = 0) then
        return trunc(val,v_rnd_digit);
    else
        return round(val,v_rnd_digit);
    end if;
end;
/

If I had access to Oracle, I would have tested this myself, but since I don't, I'll need to rely on you guys to test it for me.

If I'm not mistaken...

BankersRound(-123.45, 1) ==> -123.4
BankersRound(-123.55, 1) ==> -123.6
BankersRound(-123.65, 1) ==> -123.6
BankersRound(-123.75, 1) ==> -123.8

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[karluk]

For the record, the following queries confirm that gmmastros's Bankers_Round function does, indeed, return the results he was expecting.

SQL> create or replace function bankers_round (val number, rnd_digit number := 0) return number is
  2      v_rnd_digit  number;
  3      v_remainder  number;
  4  begin
  5      v_rnd_digit := trunc(rnd_digit);
  6  
  7      v_remainder := (val - trunc(val,v_rnd_digit)) * power(10,v_rnd_digit + 1);
  8  
  9      if Abs(v_remainder) < 5
 10      or (Abs(v_remainder) = 5 and mod(trunc(val * power(10,v_rnd_digit)),2) = 0) then
 11          return trunc(val,v_rnd_digit);
 12      else
 13          return round(val,v_rnd_digit);
 14      end if;
 15  end;
 16  /

Function created.

SQL> select Bankers_Round(-123.45, 1) from dual;

BANKERS_ROUND(-123.45,1)
------------------------
                  -123.4

SQL> select Bankers_Round(-123.55, 1) from dual;

BANKERS_ROUND(-123.55,1)
------------------------
                  -123.6

SQL> select Bankers_Round(-123.65, 1) from dual;

BANKERS_ROUND(-123.65,1)
------------------------
                  -123.6

SQL> select Bankers_Round(-123.75, 1) from dual;

BANKERS_ROUND(-123.75,1)
------------------------
                  -123.8