Query problem when switching from Access to MS SQL

[weezerboy]

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?       

[REEDPOWELL]

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

[BKBK · Accepted solution]

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">.