Home » SQL & PL/SQL » SQL & PL/SQL » Opposite of TRUNC
Opposite of TRUNC [message #209893] Mon, 18 December 2006 05:37 Go to next message
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 #209894 is a reply to message #209893] Mon, 18 December 2006 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How would you describe "opposite of trunc"? For example, opposite of 'black' is 'white', opposite of 'tall' is 'small', etc. What should that function, actually, do? Examples?
Re: Opposite of TRUNC [message #209897 is a reply to message #209894] Mon, 18 December 2006 05:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
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.

[Updated on: Mon, 18 December 2006 05:54]

Report message to a moderator

Re: Opposite of TRUNC [message #209900 is a reply to message #209897] Mon, 18 December 2006 06:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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 #210129 is a reply to message #210089] Tue, 19 December 2006 09:16 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Thank you very much for your help and quick response
Re: Opposite of TRUNC [message #210132 is a reply to message #210089] Tue, 19 December 2006 09:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Bill B
Messages: 1484
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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

Previous Topic: Merge the Result of two Queries
Next Topic: Date conversion with NCLOB (very urgent)
Goto Forum:
  


Current Time: Thu Dec 08 00:28:02 CST 2016

Total time taken to generate the page: 0.11136 seconds