Skip to content
Advertisement

Round up to the next whole number case it contains decimal point (In SQL Oracle)

I need to retrieve a field from a table and display the results rounded up to the next whole number if is not whole yet.

For example:

I have in my table the field working hours.

I need to create de following logic.

If working hours/15 equals to 4.01 or whatever grater 4.0 then round to 5

Else select as it is (4.0).

Advertisement

Answer

Is this what you want?


with t (n) as (
  select 17.813 from dual
  union all select 20.126 from dual
  union all select 1.000 from dual
  union all select 1.001 from dual
  union all select 1.005 from dual
  union all select 1.009 from dual
  union all select 1.010 from dual
  union all select -1.000 from dual
  union all select 0 from dual
  union all select -1.001 from dual
  union all select -1.005 from dual
  union all select -1.009 from dual
  union all select -1.010 from dual
)
select n, ceil(n) as next_num
from t;

  
N   NEXT_NUM
17.813  18
20.126  21
1   1
1.001   2
1.005   2
1.009   2
1.01    2
-1  -1
0   0
-1.001  -1
-1.005  -1
-1.009  -1
-1.01   -1

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement