Invalid floating point operation error when calculating the logarithm in SQL Server 2008

Question

In Microsoft SQL Server 2008, I have a table, say myTable , containing about 600 thousand rows (in fact, this is the result of joining several other tables, but I believe that it doesn’t matter). One of its columns, for example, value is of the numeric type (6.2).

A simple query SELECT value FROM myTable ORDER BY value returns, of course, about 600 thousand numbers, starting from 1.01 (i.e. the lowest) and ending with 70.00 (the highest); no NULL or other values.

Please note that all of these values ​​are numeric and positive. However, when I call SELECT LOG(value) FROM myTable I get the error message "Invalid floating point operation."

This error always appears about 3 minutes after the request is completed. When copying values ​​of 600 thousand. In Excel and calculating their LN () there is absolutely no problem.

I tried converting value to real or float, which didn't help at all. Finally, I found a workaround: SELECT LOG(CASE WHEN value>0 THEN value ELSE 1 END) FROM myTable . It works. But why, when all values ​​are positive? I tried to take the result and compare the logarithms with the Excel calculations - they are all the same (on some lines there were only differences of the order of 10 ^ (- 15) or less, which almost certainly comes with different accuracy), This means that the condition in the CASE expression is always true , I guess.

Does anyone know why this error occurs? Any help appreciated. Thanks.

Answer 1

You can determine the specific value causing the problem;

 declare @f numeric(6,2), @r float begin try select @f = value, @r = LOG(value) from mytable end try begin catch select error_message(),'value=',@f end catch 

Answer 2

You will get this error - “An invalid floating point operation has occurred” when you execute LOG (0). The value of LOG (zero) is indefinite in the Maths world, therefore, an error.

Greetings.