locked
Simple expression fails. Must be a bug? RRS feed

  • Question

  • Check out this simple query

     

    SELECT     0.172 * (365 / (90 + 91)) * 1.26 AS test1

                   , (0.172 * 365) / (90 + 91) * 1.26 AS test2

                   , 0.172 / ((90 + 91) / 365) * 1.26 AS test3

     

    test1 and test2 should give the same result, but they don't?! test1 gives wrong result; test 2 gives correct result. Furthermore, test3 gives a Divide by Zero???

    This makes no sense.

    (365 / (90 + 91)) is a constant I use many times in my code; only way to procede is to replace it by a number, or have i misunderstood something?

     

    I am running the query on SQL Server 8.0.2040

     

    Regards,

    Martin

    Thursday, September 20, 2007 10:41 PM

Answers

  •  

    SQL does what it can to preserve data types.  When you use (365 / (90 + 91))  SQL assumes that you want the result to be an Integer, becuase you specified all integers.  Consider this:

     

    Code Snippet

    select 90/91, 90.0/91.0

     

     

    You'll see that though the numbers are equal, SQL will evaluate them very differently.

     

    In your example, the differences are because of the combinations of decimal places and parenth-placement.

     

     

    Adding a simple decimal point without any following characters is probably enough to get "close enough." 

     

    You can read a lot about "significant digits."  I used a sort of rule of thumb:  if you want accuracy to two decimal places, then supply two decimal places:  90.00 / 91.00

    Code Snippet

    select 90. / 90, 90/ 90., 90.0 / 91.0, 90. / 91., 90.00 / 91.00

     

     

    Friday, September 21, 2007 1:38 AM

All replies

  • just commenting on the third resultset. divide by zero makes perfect sense and its correct.

     

    By carefully putting the braces around ((90+91)/365), this part will be evaluated first, resulting to (181/365), translates to '0'. Thats why its throwing the divide by zero error.

     

     

    Thursday, September 20, 2007 11:11 PM
  • Without decimal point, numbers 90, 91, and 365 are treated as datatype "int". And division of two ints also produces int, by returning the module only. Therefore the problem.

     

    If you append a decimal point to all the three numbers, you would get what you expected.

    Thursday, September 20, 2007 11:20 PM
  •  

    SQL does what it can to preserve data types.  When you use (365 / (90 + 91))  SQL assumes that you want the result to be an Integer, becuase you specified all integers.  Consider this:

     

    Code Snippet

    select 90/91, 90.0/91.0

     

     

    You'll see that though the numbers are equal, SQL will evaluate them very differently.

     

    In your example, the differences are because of the combinations of decimal places and parenth-placement.

     

     

    Adding a simple decimal point without any following characters is probably enough to get "close enough." 

     

    You can read a lot about "significant digits."  I used a sort of rule of thumb:  if you want accuracy to two decimal places, then supply two decimal places:  90.00 / 91.00

    Code Snippet

    select 90. / 90, 90/ 90., 90.0 / 91.0, 90. / 91., 90.00 / 91.00

     

     

    Friday, September 21, 2007 1:38 AM
  • Thank you all. I will put decimal points on the figures. HMM...

    Friday, September 21, 2007 6:31 AM