CODEWITHSUNDEEP
sqlsql-server
Theguy
Theguy

Reputation: 33

Fractional exponent POWER function error ("An invalid floating point operation occurred.")

 sales_3Y
 0.521
 1.282
 0.71
 1.513
 1.502
 2357
 1.291
 1.393
 0
 0
 2.009

I need to calculate the 1/nth root of the column.

Select POWER(sales_3YR,(1.0/3))

Error - An invalid floating point operation occurred.

Upvotes: 0

Views: 306

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

This suggests that POWER(x,n) for fractional n is implemented internally as exp(n*log(x)), which would fail for all x<0. You can work around it for the special cases of an odd n-th root with a user-defined function like:

create or alter function dbo.CubeRoot(@x float)
returns float
as
begin
  if @x = 0 return 0;

  return sign(@x) * exp(log(abs(@x))/3.0)
end

and

select dbo.CubeRoot(-8)

outputs 

-2

Upvotes: 2

Related Questions