Query problem when switching from Access to MS SQL

Question

I have this query which worked great in Access

SELECT e.wall +l.wall As wall2yr, e.lall +l.lall As lall2yr, l.TEAMID, l.TEAMNAME, l.GY AS LaterGY, l.finalrating AS LaterFinalRating, e.finalrating AS EarlyFinalRating, e.GY AS EarlyGY, LaterFinalRating+EarlyFinalRating AS twoyear FROM TEAMSEASONRATINGINFO AS e INNER JOIN TEAMSEASONRATINGINFO AS l ON l.teamID = e.TeamID AND l.GY = e.GY+1 WHERE l.GY =#url.GY# AND e.finalrating > 0

AND l.DIVISION = #url.DIVISION#

ORDER BY e.finalrating+ l.finalrating DESC

Now I have switched to MS SQL and I am geting this error

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'LaterFinalRating'.

Is there something different the way MSSQL handles INNER JOINS or aliases?

BKBK · Accepted Answer

ReedPowell is right. You could just do something like

SELECT e.wall +l.wall As wall2yr, e.lall +l.lall As lall2yr, l.TEAMID, l.TEAMNAME, l.GY AS LaterGY, l.finalrating, e.finalrating, e.GY AS EarlyGY, l.finalrating + e.finalrating AS twoyear FROM TEAMSEASONRATINGINFO AS e INNER JOIN TEAMSEASONRATINGINFO AS l ON l.teamID = e.TeamID AND l.GY = e.GY+1 WHERE l.GY =#url.GY# AND e.finalrating > 0

AND l.DIVISION = #url.DIVISION#

ORDER BY e.finalrating+ l.finalrating DESC

You should also make your code less vulnerable to hacking by replacing #url.GY# and #url.DIVISION#, respectively, with <cfqueryparam value="#url.GY#" cfsqltype="cf_sql_varchar"> and <cfqueryparam value="#url.DIVISION#" cfsqltype="cf_sql_varchar">.

REEDPOWELL · Answer

MS SQL does not allow you to use aliased columns as operands in other elements of a SELECT statement. Each element of the SELECT list has to be based on an actual column, function result, subquery result, etc.

-reed

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.