Post reply

Help with SQL Maths Operators

  • September 24, 2013 at 4:56 am

    #298141

    Hello

    I have a calculation in a Access Database that I am trying to change into a SQL Server query; however, I'm getting a error on one of the Maths Operators (^) used. I have looks on MSDN and this seems to be included so I'm unsure why this isn't working.

    Below is the SQL

    SELECT ,MTG_QCA = CASE WHEN ttss_avgeqcapointscore< 1 THEN 0 ELSE

    Round([LAT2011].[Gamma0]+[LAT2011].[Gamma1]*[ttss_avgeqcapointscore]+[LAT2011].[Gamma2]*[ttss_avgeqcapointscore]^2+[LAT2011].[Gamma3]*[ttss_avgeqcapointscore]^3+[LAT2011].[Gamma4]*[ttss_avgeqcapointscore]^4,2) END

    This is the error message that I recieve

    Msg 402, Level 16, State 1, Line 9

    The data types numeric and int are incompatible in the '^' operator.

    Any help would be much appreciated 🙂

  • September 24, 2013 at 5:10 am

    #1652653

    In SQL, ^ is a bitwise exclusive OR. You'll be wanting the POWER function.

    SELECT POWER(2,3) -- 8

    SELECT POWER(5,2) -- 25

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 24, 2013 at 6:07 am

    #1652672

    Thanks for the response, what do you mean use POWER? I'm not aware of that function.

    How would the code look ?

  • September 24, 2013 at 6:20 am

    #1652681

    Roughly speaking, like the following (untested):

    SELECT MTG_QCA = CASE WHEN ttss_avgeqcapointscore < 1 THEN 0

    ELSE ROUND([LAT2011].[Gamma0] + [LAT2011].[Gamma1]

    * [ttss_avgeqcapointscore]

    + [LAT2011].[Gamma2]

    * POWER([ttss_avgeqcapointscore], 2)

    + [LAT2011].[Gamma3]

    * POWER([ttss_avgeqcapointscore], 3)

    + [LAT2011].[Gamma4]

    * POWER([ttss_avgeqcapointscore], 4), 2)

    END

    It's just a syntax difference

  • September 24, 2013 at 6:55 am

    #1652702

    Knives85 (9/24/2013)

    Thanks for the response, what do you mean use POWER? I'm not aware of that function.

    The built-in system function POWER. If you're not familiar with it, look it up in Books Online.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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