Home » SQL & PL/SQL » SQL & PL/SQL » problem with FLOOR function (oracle)
problem with FLOOR function [message #662704] Wed, 10 May 2017 00:57 Go to next message
omidsm
Messages: 6
Registered: September 2005
Location: iran
Junior Member

hi
i have problem with floor function in oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
SQL> SELECT ((122307 / 30964) * 30964) a
     , FLOOR((122307 / 30964) * 30964) b
       FROM DUAL;

         A          B
---------- ----------
    122307     122306
i checked this problem on 11.2.0.1.0 , 10.2.0.4.0 and 12.1.0.2.0
but MSSQL retrieve correct answers

can you describe?

i solved it with below script
DECLARE V_T FLOAT(49);
BEGIN
  SELECT ((122307 / 30964) * 30964) INTO V_T FROM DUAL;
  V_T:=FLOOR(V_T);
  DBMS_OUTPUT.put_line(V_T);
END;
thanks


--moderator update: this post appears to come (through a proxy server) from a location where use of Oracle is illegal under US law.

[Updated on: Wed, 10 May 2017 02:07]

Report message to a moderator

Re: problem with FLOOR function [message #662706 is a reply to message #662704] Wed, 10 May 2017 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT ((122307 / 30964) * 30964) a
  2       , FLOOR((122307 / 30964) * 30964) b
  3         FROM DUAL;
         A          B
---------- ----------
    122307     122306

1 row selected.

SQL> set numwidth 50
SQL> /
                                                 A                                                  B
-------------------------------------------------- --------------------------------------------------
         122306.9999999999999999999999999999999999                                             122306

1 row selected.
SQL*Plus rounds the result to the numeric width (or format) you gave it (default numeric width is 10).

[Updated on: Wed, 10 May 2017 02:23]

Report message to a moderator

Re: problem with FLOOR function [message #662714 is a reply to message #662706] Wed, 10 May 2017 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 13339
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well that explains what floor is doing, but oracle is obviously getting the maths wrong.
Re: problem with FLOOR function [message #662718 is a reply to message #662714] Wed, 10 May 2017 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Since its very first version due to its implementation of numbers.
It is better to always do the multiplications before the divisions to avoid this:
SQL> select ((122307 * 30964) / 30964) a from dual;
                                                 A
--------------------------------------------------
                                            122307

1 row selected.
until you get a number overflow. Sad

Re: problem with FLOOR function [message #662720 is a reply to message #662718] Wed, 10 May 2017 03:38 Go to previous message
omidsm
Messages: 6
Registered: September 2005
Location: iran
Junior Member

thanks
Previous Topic: Decode and To_date
Next Topic: Using CHECK constraint to validate two types of telephone formatted insert data
Goto Forum:
  


Current Time: Sun Nov 18 17:34:33 CST 2018