Lowest fraction Value In Oracle

Question

I am trying to create a function to return lowest fraction value. the sample code is here :

create or replace function fraction_sh(x number) return varchar2
is
    fra1 number;
    pwr number;
    intprt number;
    v4 number;
    numer number;
    denom number;
    gcdval number;
    frac varchar2(50);
    begin
       if x <> 0 then  
           fra1 := mod(x,1);
           pwr := length(mod(x,1))-1;
           intprt := trunc(x);
           numer :=mod(x,1)*power(10,length(mod(x,1))-1);
           denom :=power(10,length(mod(x,1))-1);
           gcdval := gcdnew(power(10,length(mod(x,1))-1),mod(x,1)*power(10,length(mod(x,1))-1));
               if intprt = 0 then 
                       frac := to_char(trunc(numer/gcdval))||'/'||to_char(trunc(denom/gcdval));
                DBMS_OUTPUT.put_line(1||' '||denom||' '||gcdval||' '||numer);
              else
                    frac := (intprt*to_char(trunc(denom/gcdval)))+to_char(trunc(numer/gcdval))||'/'||to_char(trunc(denom/gcdval));
                   DBMS_OUTPUT.put_line(2||' '||denom||' '||gcdval||' '||numer);
              end if;
        end if;
     return frac;
end;


create or replace function gcdnew (a number, b number, p_precision number default null, orig_larger_num number default null) return number is
v_orig_larger_num number := greatest(nvl(orig_larger_num,-1),a,b);
v_precision_level number := p_precision;
begin
  if a is null or b is null or (a = 0 and b = 0) then return 1; end if;

  if p_precision is null or p_precision <= 0 then
      v_precision_level := 4; 
  end if;

  if b is null or b = 0 or (b/v_orig_larger_num <= power(10,-1*v_precision_level) and greatest(a,b) <> v_orig_larger_num) then
      return a;
  else
      return (gcdnew(b,mod(a,b),v_precision_level,v_orig_larger_num));
  end if;

end;

Inmost cases it works, but when i try to pass 2/11 it returns 2/10.

Any help appreciated.

The function returns lowest common fraction. eg. 9/12 returns 3/4. Problem is 2/11 is supposed to return 2/11, but returns 2/10. — Vikrant Jain, Feb 19 '18 at 11:49
what is X value? I mean you send it to function like "2/11" or "0,18181818181818181818181818181818" — CompEng, Feb 19 '18 at 12:15
so you can give that number as dividing ,divided Pls look my answer below — CompEng, Feb 19 '18 at 12:29
You're passing in a decimal number, not a fraction; which do you actually need to do? Are you trying to simplify a fraction, or convert a decimal to a fraction (as a string)? — Alex Poole, Feb 19 '18 at 12:35
Actually Alex, you are right, I used to pass value as fraction but the function automatically convert it to decimal (using simple mathematics). — Vikrant Jain, Feb 19 '18 at 13:07

score 0 · Answer 1 · answered Feb 19 '18 at 12:28

0

you can use like this:

create or replace function fraction_sh(dividing number,divided number) return varchar2
is 
dividing2 number;
divided2 number;
frac varchar2(100 char); 
temp number;
loop_value boolean;
    begin
     loop_value:=true;
     dividing2:=dividing;
     divided2 :=divided;
       if dividing <> 0 then  
           while loop_value
           loop
           if gcd(dividing2,divided2)<> 1 then
           temp:=gcd(dividing2,divided2);
            dividing2:=dividing2/temp;
            divided2 :=divided2/temp;
            frac:=dividing2||'/'||divided2; 
            else
            loop_value:=false;
            frac:=dividing2||'/'||divided2; 
           end if;          
           end loop;
        else
        frac:='0';
        end if;
     return frac;
end;

gcd func:

create or replace function gcd(a number, b number)
  return number is
  begin
     if b = 0 then
        return a;
     else
        return gcd(b,mod(a,b));
     end if;
  end;

answered Feb 19 '18 at 12:28

CompEng

7,161
16
68
122

Ersin, if possible can you help me to do same for decimal values. Actually i have used this function in many places with single parameter, and it is a chaos to change in production. – Vikrant Jain Feb 19 '18 at 13:12
if you want to 2/11 . It equals "0,18181818181818181818181818181818" it means 18181818181818181818181818181818/100000000000000000000000000000000 so you can pass this parameter to func FRACTION_SH( 18181818181818181818181818181818,100000000000000000000000000000000) – CompEng Feb 19 '18 at 13:17
Ersin, 18181818181818181818181818181818,100000000000000000000000000000000 this will never give me 2/11. there is always some loss of value when we convert decimal to fraction. – Vikrant Jain Feb 19 '18 at 13:25
but if you pass decimal value you can do it fraction like that other wise how we do know what is 18181818181818181818181818181818/100000000000000000000000000000000 – CompEng Feb 19 '18 at 13:29

score 0 · Accepted Answer · answered Feb 19 '18 at 19:15

The problem with what you're currently doing is precision. With 2/11 the resulting number is 0.1818181... recurring, and the length of that - and therefore the pwr value - end up as 40, which destroys the later calculations.

With modifications to limit the precision (and tidied up a bit, largely to remove repeated calculations when you have handy variables already):

create or replace function fraction_sh(p_float number) return varchar2
is
  l_precision pls_integer := 10;
  l_int_part pls_integer;
  l_frac_part number;
  l_power pls_integer;
  l_numer number;
  l_denom number;
  l_gcdval number;
  l_result varchar2(99);
begin
  if p_float is null or p_float = 0 then
    return null;
  end if;

  l_int_part := trunc(p_float);
  l_frac_part := round(mod(p_float, 1), l_precision);
  l_power := length(l_frac_part);
  l_denom := power(10, l_power);
  l_numer := l_frac_part * l_denom;

  l_gcdval := gcdnew(l_denom, l_numer, ceil(l_precision/2));

  if l_int_part = 0 then 
    l_result := trunc(l_numer/l_gcdval) ||'/'|| trunc(l_denom/l_gcdval);
  else
    l_result := l_int_part * (trunc(l_denom/l_gcdval) + trunc(l_numer/l_gcdval))
      ||'/'|| trunc(l_denom/l_gcdval);
  end if;

  return l_result;
end;
/

Which gets:

with t(n) as (
            select 9/12 from dual
  union all select 2/11 from dual
  union all select 1/2 from dual
  union all select 1/3 from dual
  union all select 1/4 from dual
  union all select 1/5 from dual
  union all select 1/6 from dual
  union all select 1/7 from dual
  union all select 1/8 from dual
  union all select 1/9 from dual
  union all select 1/10 from dual
  union all select 4/3 from dual
  union all select 0 from dual
  union all select 1 from dual
)
select n, fraction_sh(n) as fraction
from t;

        N FRACTION                      
---------- ------------------------------
       .75 3/4                           
.181818182 2/11                          
        .5 1/2                           
.333333333 1/3                           
       .25 1/4                           
        .2 1/5                           
.166666667 1/6                           
.142857143 1/7                           
      .125 1/8                           
.111111111 1/9                           
        .1 1/10                          
1.33333333 4/3                           
         0                               
         1 1/1

So you might want to add some handling for either passing in 1, or the approximation after rounding ending up as 1/1 - presumably just to return a plain '1' in either case.

I've set l_precision to 10 rather arbitrarily, you can make that larger, but will hit problems at some point so test carefully with whatever value you pick.

(And I haven't looked at gdcnew at all; that can probably be simplified a bit too.)

thanks, alex for wonderful work. I have checked the code it works, but a little bit of testing shows me that it works fine upto certain level, eg. for `401/190001` gives me `40/19000`. so increase the precision `level to 30, and it works till 1 billion` in denominator. — Vikrant Jain, Feb 20 '18 at 04:59

Lowest fraction Value In Oracle

2 Answers2