Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with SQL Maths Operators


Help with SQL Maths Operators

Author
Message
Knives85
Knives85
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 248
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 :-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47171 Visits: 44346
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


Knives85
Knives85
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 248
Thanks for the response, what do you mean use POWER? I'm not aware of that function.

How would the code look ?
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47171 Visits: 44346
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search