comparing decimal numbers [message #150487] |
Thu, 08 December 2005 02:29  |
murad_tamimi
Messages: 12 Registered: December 2005
|
Junior Member |
|
|
Hi,
I need to compare decimal numbers in oracle. I know that comparing by equal sign does not yield expected results.
How do i go about doing this?
What if i wanted to compare a number that is just close to another number? (not exactly equal, but just close, maybe the difference could be the 6th decimal digit eg 45.6678547 and 45.6678549 - i would like the comparison to say that these two numbers are equal.
Is there a way to do that?
Thanks.
Murad
|
|
|
Re: comparing decimal numbers [message #150497 is a reply to message #150487] |
Thu, 08 December 2005 03:31   |
 |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
Hope this will help you.
declare
i number:=45.0;
j number:=45.0000001;
begin
if i-trunc(i) = 0 then -- take care if anyone is integer
i := i + 0.0000001;
end if;
if j-trunc(j) = 0 then
j := j + 0.0000001;
end if;
if trunc(i) = trunc(j) and substr(rpad(i-trunc(i),7,'0'),2,7) =
substr(rpad(j-trunc(j),7,'0'),2,7) then
dbms_output.put_line('equal');
else
dbms_output.put_line('not equal');
end if;
end;
/
I thought I could do that using abs but it didn't work.
By
Vamsi
|
|
|
|
|
|
Re: comparing decimal numbers [message #150587 is a reply to message #150487] |
Thu, 08 December 2005 10:19  |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Both round and trunc let you specify a position. I view the difference as a business answer goal sort of thing; depends on what you want the result to be in different situations:
MYDBA > select round(45.6673419,6), trunc(45.6673419,6) from dual;
ROUND(45.6673419,6) TRUNC(45.6673419,6)
------------------- -------------------
45.667342 45.667341
|
|
|