OraFAQ Forum: SQL & PL/SQL » Opposite of TRUNC

Home » SQL & PL/SQL » SQL & PL/SQL » Opposite of TRUNC

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Opposite of TRUNC [message #209894 is a reply to message #209893]

Mon, 18 December 2006 05:48

Littlefoot
Messages: 21826
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?

Report message to a moderator

Re: Opposite of TRUNC [message #209897 is a reply to message #209894]

Mon, 18 December 2006 05:53

Frank
Messages: 7901
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

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

Report message to a moderator

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?

Report message to a moderator

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.

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

Re: Opposite of TRUNC [message #210129 is a reply to message #210089]

Tue, 19 December 2006 09:16

Anto318
Messages: 27
Registered: December 2006

Junior Member

Thank you very much for your help and quick response

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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..

Report message to a moderator

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

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 26 06:46:00 CDT 2025