Home » SQL & PL/SQL » SQL & PL/SQL » Round Function
Round Function Thu, 07 February 2008 10:32
 amit_kiran Messages: 50Registered: 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
 ThomasG Messages: 3202Registered: 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
 BlackSwan Messages: 25570Registered: 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
 amit_kiran Messages: 50Registered: 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
 ramanajv1968 Messages: 168Registered: 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 ramanajv1968 Messages: 168Registered: 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
 mshrkshl Messages: 247Registered: 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
 ramanajv1968 Messages: 168Registered: 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 amit_kiran Messages: 50Registered: 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 coleing Messages: 213Registered: 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 amit_kiran Messages: 50Registered: 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 coleing Messages: 213Registered: 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
 ThomasG Messages: 3202Registered: 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: Fri Aug 18 22:37:03 CDT 2017

Total time taken to generate the page: 5.04626 seconds