CODEWITHSUNDEEP
sqloracle-databaserounding
dimas
dimas

Reputation: 2597

Custom rounding logic in oracle sql

Hi everyone i am trying to create a custom logic for rounding off a decimal as specified in the rules.

The rule states that any decimal whose value is in between .01 to .05 should have .05 value. Any value which falls between .06 to .09 should be equal to .10.

For example 

1.05 => 1.05
1.84 => 1.85
1.06 => 1.10
1.79 => 1.80

I tried to play with the formula specified in oracle website here. But it didn't work see below code

select 
round(1.05 * power(10,2) + .05) * power(10,-2) as Rounding1
, round(1.84 * power(10,2) + .05) * power(10,-2) as Rounding2
, round(1.06 * power(10,2) + .05) * power(10,-2) as Rounding3
, round(1.79 * power(10,2) + .05) * power(10,-2) as Rounding4
from dual;

Any ideas on how this can be implemented?

Upvotes: 1

Views: 196

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think this logic does what you want:

select ceil(num * 20) / 20 from tablename

Upvotes: 1

Related Questions