rounding

[rogerio.pelinski]

Hello.

I need a function in Oracle that rounds for two houses
decimals a value from 6 and not from 5 as the Round function does.

Ex1: 12,545 I need to be 12.54
Ex2: 12,546 I need to be 12.55

If anyone can help me I'm very grateful.

[diegolenhardt]

Round (number, 2)

[rogerio.pelinski]

The Round I know.

with Round (12,545.2), I have the return: 12.55, I need
that returns 12.54.

must round up only from 6 ...

[SergioLBJr]

I do not know anything ready.

But I thought of something like.

Select all

SELECT  CASE WHEN (SUBSTR(TO_CHAR(LPAD(&numero,10,0)),10,1) <= 5) THEN TRUNC(&numero,2) ELSE ROUND(&numero,2) END FROM dual

Try aí, LPAD Ali PE to be able to work with larger numbers.

[SergioLBJr]

Only you are limited by your decimal places this way: Cry:

[fsitja]

Just move the rounding subtracting the offset of 0.001

If you want something more flexible, you can change the number of decimal places, simply place a variable in place and use the expression below:

Select all

 
select numero, 
       round(numero - (1 / power(100, &casas_decimais)), &casas_decimais) round 
  from t; 

[fsitja]

Example to round for 2 decimal places:

Select all

 
SQL> with t as ( 
  2  select (level + 12538) / 1000 numero from dual connect by level <= 15) 
  3  -- fim dos dados de teste 
  4  select numero, 
  5         round(numero - (1 / power(100, &casas_decimais)), &casas_decimais) round 
  6    from t; 
  
    NUMERO      ROUND 
---------- ---------- 
    12,539      12,54 
     12,54      12,54 
    12,541      12,54 
    12,542      12,54 
    12,543      12,54 
    12,544      12,54 
    12,545      12,54 
    12,546      12,55 
    12,547      12,55 
    12,548      12,55 
    12,549      12,55 
     12,55      12,55 
    12,551      12,55 
    12,552      12,55 
    12,553      12,55 
  
15 rows selected 
  
SQL>  

[rogerio.pelinski]

Personnel, these last two examples that have been posted did not behave as expected, simulated here:



passing to variable & number = 1.1555
Passing to variable & houses_decimal = 2

has the return of the number = 1.16
and in this case what I need to get is 1.15

thank you very much by all collaboration.

[rogerio.pelinski]

Based on the tips of colleagues, I created the following function, the tests I realized seems to work well:

Select all

-- Author  : ROGERIO P. 
-- Created : 18/02/2010 13:48:50 
-- Purpose : Arredonda para cima a partir do 6 
create or replace function round6(p_vl_x     number 
                                 ,p_nr_casas number) return number is 
  va_vl_x             number       := p_vl_x; 
  va_nr_lpad constant number       := 60; 
  va_valor_string     varchar2(60) := lpad(va_vl_x,va_nr_lpad,0); 
  va_nr_decimais      number       := length(substr(va_valor_string,instr(va_valor_string,'.')+1,20)); 
begin 
  if p_nr_casas                 < 0 or                 -- Se Números de Casas Menor que Zero ou 
     instr(va_valor_string,'.') = 0 or                 -- Se não possuir Decimal ou 
     p_nr_casas                >= va_nr_decimais then  -- Se Número de Casas solicitada maior/igual ao número de Decimais 
     return (va_vl_x);                                 -- Retorna valor sem arredondamento 
  else     
     loop 
         va_nr_decimais := va_nr_decimais - 1; 
         if substr(va_valor_string,va_nr_lpad,1) <= 5 then 
            va_vl_x := trunc(va_vl_x,va_nr_decimais); 
         else 
            va_vl_x := round(va_vl_x,va_nr_decimais); 
         end if; 
         va_valor_string := lpad(va_vl_x,va_nr_lpad,0); 
         exit when va_nr_decimais <= p_nr_casas; 
     end loop; 
     return (va_vl_x); 
  end if; 
end;

[fsitja]

Math Fail For me ...

Test the following Please rogerio:

Select all

 
select &numero, 
       round(&numero - (power(10, - 1 * (&casas_decimais + 1))), &casas_decimais) round 
  from t;  

[fsitja]

I'm terrible ... replaces the damn t by dual ...: roll:

Select all

 
select &numero, 
       round(&numero - (power(10, - 1 * (&casas_decimais + 1))), &casas_decimais) round 
  from dual; 

[rogerio.pelinski]

Hello.

This simplified example continues with a problem:

Select all

select &numero,  
       round(&numero - (power(10, - 1 * (&casas_decimais + 1))), &casas_decimais) round  
  from DUAL; 

I passed the number 10.155556 with 2 decimal places.
returned: 10.15, when expected would be 10.16.

Embrace.

[fsitja]

As curiosity, it is working for negative numbers as well, as expected by the round:

Select all

 
SQL> var casas_decimais number 
SQL> exec :casas_decimais := -1 
  
PL/SQL procedure successfully completed 
casas_decimais 
--------- 
-1 
SQL> with t as ( 
  2  select 15 as numero from dual union all 
  3  select 16 from dual) 
  4  -- fim dos dados de teste 
  5  select numero, 
  6         round(numero - (power(10, - 1 * (:casas_decimais + 1))), :casas_decimais) round 
  7    from t; 
  
    NUMERO      ROUND 
---------- ---------- 
        15         10 
        16         20 
casas_decimais 
--------- 
-1 
  
SQL>  

[rogerio.pelinski]

Posted: Thu, 18 Feb 2010 2:35 pm Subject: Test

-------------------------- -------------------------------------------------- ----

Hello.

This simplified example continues to a problem:

Select all

select &numero,  
round(&numero - (power(10, - 1 * (&casas_decimais + 1))), &casas_decimais) round  
from DUAL; 

I passed the number 10.155556 with 2 decimal places.
returned: 10.15, when expected would be 10.16.

Embrace.

[fsitja]

I already get there ... Carnival back is f *** ... I answer after I have a coffee.

[rogerio.pelinski]

Take your coffee there.
To speak the truth the "Round6" function that I posted is working well,
but I prefer solutions with fewer lines of code.

[fsitja]

Umm ... Well, back to the context ...: -The

face, so I noticed, in this case you are speaking above, 10.155556 rounded in the second house after the comma should not Being 10.16 ... will only round up the second home if the 3rd is 6, as it is not will not.

That way you would be changing the logic to fully and, if that's the case, just putting a loop on the arm of the least significant house to the most significant propagating rounding, as this is not does this way naturally. Observe the example below:

Select all

 
SQL> with t as ( 
  2  select 10.1444445 as numero from dual union all 
  3  select 10.144445 as numero from dual union all 
  4  select 10.14445 as numero from dual union all 
  5  select 10.1445 as numero from dual union all 
  6  select 10.145 from dual) 
  7  -- fim dos dados de teste 
  8  select numero, 
  9         round(numero, 2) round 
 10    from t; 
  
    NUMERO      ROUND 
---------- ---------- 
10,1444445      10,14 
 10,144445      10,14 
  10,14445      10,14 
   10,1445      10,14 
    10,145      10,15 
  
SQL>  

Only if the 3rd decimal house is a 5 he will play 2nd home up.
.

would be something like performing a recursive rounding up. Are you sure that's how you need to be?

[rogerio.pelinski]

knows that he had never been attacking for these rounds of round.

That way your solution seems to work fine.

Thank you very much.

[fsitja]

You're welcome.

I was already losing hair thinking about model ... fortunately you will not need it. : Wink:

[rogerio.pelinski]

Your code is working very well.

As Curiosity, as I had not called me in these
questions of rounding, the function I did the treatment
"If you need" the last house decimal and brings up to the requested always
performing rounding.

Ex.: 1.45555556 with two houses brings to 1.46 so the code was
half extensive.

Thanks! Hug.

[Price]

Use Floor instead of Round.

Select all

select floor (x) from dual

[gremonesi]

Do it like this;

Select all

SELECT (ceil((12.5452) * 100) )/100,  12.5452 FROM