Post reply

POWER function in SQL server 2005

  • November 24, 2005 at 5:01 am

    #169797

    I get the following error while trying to calculate the power value of a negative number.

    Ex : SELECT POWER(27.0,1.00/3.00) gives an output of 3.0

    while SELECT POWER((-27),1.00/3.00) results in the following error.

    Can any one help me out? How do i perform a power calculation on a negative number?

    Thanks in Advance

    Msg 3623, Level 16, State 1, Line 1

    A domain error occurred.

     

  • November 24, 2005 at 6:40 am

    #605684

    There are 2 problems with the needed calculation

    1. Even Root of a negative number:

    What is the square root of a negative four ?

    The answer is the imaginary number of 2 but SQL Server does not support imaginary numbers, hence a domain error will occur.

    2. SQL Server Precision

    SQL Server should be able to calculate an odd root of a negative number such as the cube root of -27, which is -3.

    But SQL server does not have a datatype for fractions and instead uses an approximate datatype such as float, where rounding will occur. Float has a maximum precision of 15 digits.

    If you run:

    select CAST (1 as float(53) ) / CAST (3 as float(53) )

    The result is 0.33333333333333331 which has been rounded.

    The solution is to calculate the power of a positive number and then:

    When the number is negative and the root is odd, change to a negative number.

    when the number is negative number and the root is even, set another column to indicate that the result is an imaginary number.

    Try the below SQL as is and then change the value of @denominator to an even number.

    Declare @Value integer

    , @numerator integer

    , @denominator integer

    set @Value = -27

    set @numerator = 1

    set @denominator = 3

    SELECT POWER( ABS(cast ( @Value as float ) ) , CAST (@numerator as float ) / CAST (@denominator as float ) )

    * CASE when @Value < 0.0 AND @denominator % 2 = 0 then cast( 1.0 as float) else cast( -1.0 as float) end

    -- Imaginary Number?

    , CASE when @Value < 0.0 AND @denominator % 2 = 0 then 'Imaginary' else '' end

    SQL = Scarcely Qualifies as a Language

  • November 24, 2005 at 11:58 pm

    #605794

    Hi Carl,

    Thanks for the reply. Your solution solved the problem. Thanks a lot again.

     

  • May 5, 2008 at 8:33 am

    #810720

    Thanks..this works! You saved my day.

  • August 7, 2008 at 9:28 am

    #854541

    This would make a good QotD!

  • August 3, 2010 at 2:49 am

    #1201396

    Hi

    I am new at SQL, so pls bear with me. How do I use the correction made earlier to correct my code below

    select '1' [union] ,type,SHORTHAND,num,denom,[current],past,round([current]-past,1) current_past,

    round((2.0*num+power(3.0902,2)-3.0902*SQRT(power(3.0902,2)+4.0*num*(1-[current])))/(2.0*(denom+power(3.0902,2))),6) lower_l2,

    round((2.0*num+power(3.0902,2)+3.0902*SQRT(power(3.0902,2)+4.0*num*(1-[current])))/(2.0*(denom+power(3.0902,2))),6) upper_l2,

    Peer_Max,Peer_Min,quarter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply