Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with SQL Maths Operators Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 4:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 21, Visits: 115
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
Post #1497763
Posted Tuesday, September 24, 2013 5:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 43,028, Visits: 36,193
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 2008, MVP
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

Post #1497769
Posted Tuesday, September 24, 2013 6:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 21, Visits: 115
Thanks for the response, what do you mean use POWER? I'm not aware of that function.

How would the code look ?
Post #1497790
Posted Tuesday, September 24, 2013 6:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 1,247, Visits: 9,879
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
Post #1497802
Posted Tuesday, September 24, 2013 6:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 43,028, Visits: 36,193
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 2008, MVP
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

Post #1497823
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse