Error Using Power Function When The Base Is Negative And Exponent Is Fractional

I'm using SQL Server's POWER function in a user function and when the base is a negative number and the exponent is a fraction I'm getting an error, [MICROSOFT][ODBC SQL SERVER D

Solution 1:

The result of POWER(-.2, 9.7) would be, at best, an imaginary number, and cannot be represented as a single floating-point number.

You could convert the floating-point exponent to a fraction and rewrite it as POWER(-.2,97/10) which is equivalent to POWER(POWER(-.2,97) , 1/10). Since the Nth roots of a negative number are real if N is odd but imaginary if N is even, POWER(-.2,1/10) would be a complex number.

Note that POWER(-.2, 0.8)could be evaluated as a real number since it would just be equivalent to POWER(-.2,4/5) and the 5th root of -.2 is real, but my guess is that SQL doesn't even bother trying to determine if the exponent could be represented as a fraction with an odd denominator and throws an error.

Solution 2:

It appears that SQL Server just does not let you do fractional exponents for negative base numbers.

There's some discussion here: Raising numbers to fractional powers in SQL

I did some testing, just to see what works and what doesn't, using this query:

createtable #test(b float, val float, success bit default (0));

    declare@afloat=-1, @bdecimal(3,2) =0.01

    WHILE @b<2BEGININSERTINTO #test(b) VALUES(@b);

        BEGIN TRY
            UPDATE #test SET val =POWER(@a, @b), success =1WHERE b =@bEND TRY
        BEGIN CATCH
            PRINT @@errorEND CATCH

        SELECT@b+=.01;
    ENDselect*from #test

If you notice, even steps that should work without giving a complex number error out. The only result that works correctly is 1.