Elegant wayt to make a low ROUND in PL/SQL.. 1

[mrcomino]

What i get is something like:
select round(1.5),round(1.49999),round(1.5000001) from dual;

ROUND(1.5) ROUND(1.49999) ROUND(1.5000001)
---------- -------------- ----------------
2 1 2

What I need is something like:
select round(1.5),round(1.49999),round(1.5000001) from dual;

ROUND(1.5) ROUND(1.49999) ROUND(1.5000001)
---------- -------------- ----------------
1 1 2


What can i do?

i dont like the idea of
change sentences
round(number) with round(number - 0.0000000001)
each time i need to round into my PL/SQL procedure.



Thanks in advance
Sorry for my english and whish you a nice day
from Canary Islands
tarko

 

[karluk]

I haven't tested this, but I think converting to a negative number before doing the round would work. Try something like

select 0-round(0-1.5), 0-round(0-1.49999), 0-round(0-1.500001) from dual;

 

[mrcomino]

Nope Karluk ,it doesnt work.
It sounded to nice to be true.
Thanks for the try.

 

[karluk]

Hmm, this could be tricky.

Let's try taking advantage of the fact that the absolute value of round(x)-x reaches its maximum when the fractional part of x is 1/2.

That means that trunc(abs(round(x)-x)+.5) = 0 or 1, and is 1 if and only if the fractional part of x is 1/2. As a result subtracting this expression from round(x) should produce the low round that you are looking for.

I tried this on the values you provide and it seems to work. I don't think anyone would call it "elegant" however.

select round(1.5) - trunc(abs(round(1.5)-1.5)+.5),
round(1.49999) - trunc(abs(round(1.49999)-1.49999)+.5),
round(1.500001) - trunc(abs(round(1.500001)-1.500001)+.5)
from dual;

 

[gsomerville]

An alternative is to use the decode statement.

decode(x-trunc(x),0.5,trunc(x),round(x)) as rounding works
perfectly except for fractional values equal to 0.5

 

[sfvb]

select ceil(1.499 -.5),
ceil(1.500 -.5),
ceil(1.501 -.5)
from dual

This works for positive numbers. It may or may not work for negative numbers depending on what you expect it to do.