Round Function [message #298763] |
Thu, 07 February 2008 10:32  |
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   |
ThomasG
Messages: 3212 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 #298776 is a reply to message #298763] |
Thu, 07 February 2008 11:31   |
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 #298895 is a reply to message #298763] |
Fri, 08 February 2008 03:18   |
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   |
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.
|
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: 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 #300416 is a reply to message #298763] |
Fri, 15 February 2008 06:16   |
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 #300457 is a reply to message #298763] |
Fri, 15 February 2008 08:09   |
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 #300477 is a reply to message #298763] |
Fri, 15 February 2008 10:12   |
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 #300494 is a reply to message #300493] |
Fri, 15 February 2008 12:10  |
ThomasG
Messages: 3212 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
|
|
|