Opposite of TRUNC [message #209893] |
Mon, 18 December 2006 05:37  |
Anto318
Messages: 27 Registered: December 2006
|
Junior Member |
|
|
Hi all,
Is there an oracle function that will perform the opposite of TRUNC?
I have a code that rounds down a value
e.g.
Select trunc(to_number(aavalue.valuestring),2) from aavalue
Is there a function that will perform the opposite of trunc.
I hope ye can help
|
|
|
|
|
Re: Opposite of TRUNC [message #209900 is a reply to message #209897] |
Mon, 18 December 2006 06:01   |
Anto318
Messages: 27 Registered: December 2006
|
Junior Member |
|
|
An example of what I am talking about is
Round up -3.14159 to 1 decimal place
The result would be -3.2
or
31415.92654 to 2 decimal places would give 31500
|
|
|
Re: Opposite of TRUNC [message #209901 is a reply to message #209897] |
Mon, 18 December 2006 06:02   |
Anto318
Messages: 27 Registered: December 2006
|
Junior Member |
|
|
Frank wrote on Mon, 18 December 2006 11:53 | That is obvious:
It will add decimals!
trunc(1.12) returns 1, so opposite(1) will return 1.12
Seriously: I think the function you are looking for is CEIL(). That rounds up instead of down.
|
Can you round with ceil() to decimal places?
|
|
|
Re: Opposite of TRUNC [message #209920 is a reply to message #209901] |
Mon, 18 December 2006 09:16   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
You know what?
You tell us exactly what it is you want, then we can tell you if Oracle has a function for it.
So, no vague descriptions, but a clear description, together with some examples.
|
|
|
Re: Opposite of TRUNC [message #209923 is a reply to message #209920] |
Mon, 18 December 2006 09:30   |
Anto318
Messages: 27 Registered: December 2006
|
Junior Member |
|
|
Sorry thought I explained it properly.
I need to write a script that will round a value to the next highest number at a defined number of decimal places. Like RoundUp in Excel
Examples are:
Round up 3.2 to 0 decimal places =4
Round up 76.9 to 0 decimal places = 77
Round up 3.14159 to 3 decimal places = 3.142
Round up -3.14159 to 1 decimal place = -3.2
Round up 31415.92654 to -2 decimal places = 31500
I hope this is enough to help you
Sorry again for being vague.
thanks in advance
AM
|
|
|
Re: Opposite of TRUNC [message #210072 is a reply to message #209923] |
Tue, 19 December 2006 04:10   |
ab_trivedi
Messages: 460 Registered: August 2006 Location: Pune, India
|
Senior Member |
|
|
hi,
you can try these :
Quote: | select ceil(3.2) from dual;
select ceil(76.9) from dual;
select round(3.14159,3) from dual;
|
rest send u later .
Bye
Ashu
|
|
|
Re: Opposite of TRUNC [message #210086 is a reply to message #209893] |
Tue, 19 December 2006 05:42   |
rameshuddaraju
Messages: 69 Registered: June 2005 Location: India
|
Member |

|
|
We don't have a function as you said with examples.
But we can create a function. see the following code witch caters your needs even though a small bug is there in it.
create or replace function RoundUp(num number,x number := 0) return number is
n number := num;
rNum number := 1;
begin
if n < 0 then
rNum := -1;
n := abs ( n ) ;
end if;
if x = 0 then
return ( rNum * ceil ( n) );
elsif x < 0 then
n := n + 5 * ( power( 10 , abs(x + 1) ) ) ;
return ( rNum * round ( n,x) );
else
n := n + ( 5 / power(10,x+1) ) ;
return ( rNum * round ( n,x) );
end if;
end;
/
Function created.
SQL> select RoundUp ( 3.2 ) from dual ;
ROUNDUP(3.2)
------------
4
SQL> select RoundUp ( 76.9 ) from dual ;
ROUNDUP(76.9)
-------------
77
SQL> select RoundUp ( 3.14159 , 3 ) from dual ;
ROUNDUP(3.14159,3)
------------------
3.142
SQL> select RoundUp ( -3.14159 , 1 ) from dual ;
ROUNDUP(-3.14159,1)
-------------------
-3.2
SQL> select RoundUp ( 31415.92654 , -2 ) from dual ;
ROUNDUP(31415.92654,-2)
-----------------------
31500
|
|
|
Re: Opposite of TRUNC [message #210089 is a reply to message #210086] |
Tue, 19 December 2006 06:03   |
 |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
You can try this one too
select trunc(x,n) + sign(x) * 1/power(10,n) from dual;
Ex:select trunc(-3.14159,1) + sign(-3.14159) * 1/power(10,1) from dual;
Do you really want to round negative numbers?
Then I think
Round up -3.14159 to 1 decimal place would be -3.
Please cross check.select trunc(x,n) + 1/power(10,n) from dual;
Ex:select trunc(31415.92654,-2) + 1/power(10,-2) from dual; By
Vamsi
|
|
|
|
Re: Opposite of TRUNC [message #210132 is a reply to message #210089] |
Tue, 19 December 2006 09:22   |
Anto318
Messages: 27 Registered: December 2006
|
Junior Member |
|
|
vamsi kasina wrote on Tue, 19 December 2006 12:03 |
Round up -3.14159 to 1 decimal place would be -3.from
Vamsi
|
No if you round -3.14159 to 0 decimal places its -3 to 1 decimal place would be -3.2
|
|
|
Re: Opposite of TRUNC [message #210159 is a reply to message #209893] |
Tue, 19 December 2006 13:27   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Use the round function, it will round to the nearest number. For example
select round(1234.5678,2) from dual;
will return 1234.57
select round(1234.5378,2) from dual;
will return 1234.56
|
|
|
Re: Opposite of TRUNC [message #210242 is a reply to message #210132] |
Wed, 20 December 2006 00:33   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Anto318 wrote on Tue, 19 December 2006 16:22 |
No if you round -3.14159 to 0 decimal places its -3 to 1 decimal place would be -3.2
|
That does not make sense.
If -3.14159 rounds to -3.2 for 1 decimal, it should round to -4 for 0 decimals to be consistent..
|
|
|
Re: Opposite of TRUNC [message #210247 is a reply to message #210132] |
Wed, 20 December 2006 00:40  |
 |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
Quote: | No if you round -3.14159 to 0 decimal places its -3 to 1 decimal place would be -3.2
|
Rounding negative numbers doesn't look same as the positive numbers.
Mathematically, -3 would be greater than -3.14159
Also you are seeking OPPOSITE of TRUNC, so -3 would be the answer for roundup(-3.1459,1) and -2 would be the answer for roundup(-3.1459,0).
Anyway I have no idea about your business requirement, so you can use my first sql for that purpose.
By
Vamsi
[Updated on: Wed, 20 December 2006 00:42] Report message to a moderator
|
|
|