Home » SQL & PL/SQL » SQL & PL/SQL » Round Function
Round Function [message #298763] Thu, 07 February 2008 10:32 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

I would like round a numeric value to 4 decial places but the last decimal should be rounded up to 10th place.

i.e.
Old Value: 1.23456 should be rounded to
New Value: 1.2346

and

Old Value: 1.23451 should be also rounded to
New Value: 1.2346

Is there any predefined oracle function for this?

Your valuable help will be much appreciated.
Thanks a lot.
Amit.
Re: Round Function [message #298764 is a reply to message #298763] Thu, 07 February 2008 10:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could use the ceil() function with appropriate multiplication/division, like :

SELECT Ceil(  1.88881 * 10000) / 10000 FROM dual
union
SELECT Ceil(  1.88880 * 10000) / 10000 FROM dual;
Re: Round Function [message #298765 is a reply to message #298763] Thu, 07 February 2008 10:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
add .00005 to old_value before rounding.
Re: Round Function [message #298767 is a reply to message #298763] Thu, 07 February 2008 11:03 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Thanks a lot.
Re: Round Function [message #298776 is a reply to message #298763] Thu, 07 February 2008 11:31 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
I will try to find the Better approach as i am about to complete my day .
here is one way of doing that by using hell lot of mathematical calculations.


step:1  
 
 select mod((1.2345671)-trunc(1.2345671),10) from dual
 
 step:2 
 
 select length(mod((1.2345671)-trunc(1.2345671),10))-1 from dual
 
 Step3:
 
 select 1.2345671*power(10,length(mod((1.2345671)-trunc(1.2345671),10))-1)  from dual
 
 step4: finding the Mod
 
  select  CASE WHEN mod(1.2345671*power(10,length(mod((1.2345671)-trunc(1.2345671),10))-1),10)< 5
  then round(1.2345671,length(mod((1.2345671)-trunc(1.2345671),10))-1-2)
  else round(1.2345671,length(mod((1.2345671)-trunc(1.2345671),10))-1-1)
  end  from dual


I tried this by taking couple of examples it worked fine
try to use in a function and call this function to achieve what u need.(atleast for now , I guess there will be a better way to do this, will try to work on tomorrow)

select  CASE WHEN mod(1.2345*power(10,length(mod((1.2345)-trunc(1.2345),10))-1),10)< 5
  then round(1.2345,length(mod((1.2345)-trunc(1.2345),10))-1-2)
  else round(1.2345,length(mod((1.2345)-trunc(1.2345),10))-1-1)
  end  from dual



  select  CASE WHEN mod(1.23759*power(10,length(mod((1.23759)-trunc(1.23759),10))-1),10)< 5
  then round(1.23759,length(mod((1.23759)-trunc(1.23759),10))-1-2)
  else round(1.23759,length(mod((1.23759)-trunc(1.23759),10))-1-1)
  end  from dual
Re: Round Function [message #298781 is a reply to message #298763] Thu, 07 February 2008 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
round(number+.00005,4)

Regards
Michel

[Updated on: Thu, 07 February 2008 11:44]

Report message to a moderator

Re: Round Function [message #298895 is a reply to message #298763] Fri, 08 February 2008 03:18 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
what if the number varies .. i.e.

number 1.234 --- ?
number = 1.89765--?

so , need to be modify the solution given by the Michel accrodingly.
Re: Round Function [message #298900 is a reply to message #298763] Fri, 08 February 2008 04:03 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
I would like round a numeric value to 4 decial places but the last decimal should be rounded up to 10th place.




round(number+.00005,4)



i think michel is absolutely right as asked.

Quote:
what if the number varies .. i.e.



may you define how it is varying ?


regards,
Re: Round Function [message #298966 is a reply to message #298763] Fri, 08 February 2008 06:37 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Ok,But what if you want to round numeric value to 6 decial places ,3 decial places....etc..,

I mean rounding of decimal places is based on the number
and if you want to round the last but one to the next value,
then what?

Suppose, In the column if the values are 1.234,
1.456789,
1.20987654

Then ?

this is what i try to explain.
Re: Round Function [message #298972 is a reply to message #298966] Fri, 08 February 2008 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
,But what if you want to round numeric value to 6 decial places ,3 decial places

This is fully meaningless and silly, tell us what can be the business need.

Regards
Michel
Re: Round Function [message #300416 is a reply to message #298763] Fri, 15 February 2008 06:16 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi, My business need is to just round the value upto 4 decimal places but the 4th decimal should be towards higher side of 10.

Eg: 1.23456 should be 1.2346
and 1.23451 Should also be 1.2346

Thanks.
Amit.
Re: Round Function [message #300426 is a reply to message #300416] Fri, 15 February 2008 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And is this not what I gave?

Regards
Michel
Re: Round Function [message #300457 is a reply to message #298763] Fri, 15 February 2008 08:09 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Just be careful that this scenario is what you want:-

select round(1.23451 + 0.00005,4) from dual;
--1.2346
select round(1.23450 + 0.00005,4) from dual;
--1.2346


You might need 1.23450 to stay as it is..
Re: Round Function [message #300460 is a reply to message #300457] Fri, 15 February 2008 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, just add 0.000049999999999999999999999999999.

Regards
Michel
Re: Round Function [message #300477 is a reply to message #298763] Fri, 15 February 2008 10:12 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi, Thanks for your response.

I want my round function to be dynamically rounding values.
I have create this function.
Please let me know if this is a viable solution.

CREATE OR REPLACE FUNCTION Com_Roundtouppervalue_Fun(pi_Number IN NUMBER, pi_howMuchSigniDigits IN NUMBER) RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(CEIL(pi_Number*POWER(10,pi_howMuchSigniDigits))/POWER(10,pi_howMuchSigniDigits));
END Com_Roundtouppervalue_Fun;
/

SELECT Com_Roundtouppervalue_Fun(1.23456, 4) FROM dual
-- 1.2346

SELECT Com_Roundtouppervalue_Fun(1.23451, 4) FROM dual
-- 1.2346

Thanks a lot for your response.
Amit.
Re: Round Function [message #300479 is a reply to message #300477] Fri, 15 February 2008 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is silly to create YOUR function when there already is a built-in one.

Regards
Michel
Re: Round Function [message #300493 is a reply to message #298763] Fri, 15 February 2008 12:00 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
I agree with Michael.

Use the built in's whever possible, they are so much faster and everyone knows how they work.
Re: Round Function [message #300494 is a reply to message #300493] Fri, 15 February 2008 12:10 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One viable argument for a user defined function could however be that there is the need to round values by the same "business rule" in hundreds of places, and you expect this business rule to change in the future.

( And I learned early that you better expect ALL business rules to change sooner or later. )

[Updated on: Fri, 15 February 2008 12:12]

Report message to a moderator

Previous Topic: HOW to retrieve values for 12 hours
Next Topic: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC'
Goto Forum:
  


Current Time: Thu Dec 08 08:16:38 CST 2016

Total time taken to generate the page: 0.12931 seconds