Home » SQL & PL/SQL » SQL & PL/SQL » Opposite of TRUNC
Opposite of TRUNC Mon, 18 December 2006 05:37
 Anto318 Messages: 27Registered: 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
 Littlefoot Messages: 21102Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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
 Frank Messages: 7880Registered: March 2000 Senior Member
That is obvious:
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
 Anto318 Messages: 27Registered: 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: 27Registered: 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: 7880Registered: 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: 27Registered: 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

Sorry again for being vague.

AM
Re: Opposite of TRUNC [message #210072 is a reply to message #209923] Tue, 19 December 2006 04:10
 ab_trivedi Messages: 460Registered: 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: 69Registered: 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: 2107Registered: 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.
`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
 Anto318 Messages: 27Registered: 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
 Anto318 Messages: 27Registered: 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: 1606Registered: 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: 7880Registered: 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: 2107Registered: 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: Fri May 26 16:04:56 CDT 2017

Total time taken to generate the page: 0.44726 seconds