SQL Language (MOSC)

MOSC Banner

sort-of rounding fractional numbers at the .5 mark

edited Dec 21, 2011 7:00PM in SQL Language (MOSC) 3 commentsAnswered
 Given:

create table foo (
idno,
num1 number (4,2))

how do I select num1 where n > n.00 and < n.50 are rounded down to n.00, n.50 is left alone, and > n.51 and < n.99 are rounded to n+1?

Basically the following

original   rounded to
1.00       1.00
1.25       1.00
1.50       1.50
1.75       2.00

etc

The normal round function will not work here because it will convert 1.4 or 1.6 to 1.5, which is not what I want. I want everything from 1.01 to 1.49 to be rounded to 1.00, and everything from 1.51 to 1.99 to be rounded to 2:00, and 1.5 left alone.

I've tried a decode like:

select idno, decode(num1 - floor(num1), between 0.01 and .49, floor(num1), between .51 and .99, floor(num1) + 1, num1) from foo

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center