2 Replies Latest reply: Jan 19, 2014 1:30 AM by BKBK RSS

    Query problem when switching from Access to MS SQL

    weezerboy Community Member
    weezerboy Nov 15, 2013 9:37 AM

      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?       

      • 548 Views
      • Tags:
        • 1. Re: Query problem when switching from Access to MS SQL
          REEDPOWELL Community Member
          REEDPOWELL Dec 1, 2013 1:19 PM (in response to weezerboy)

          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

          • 2. Re: Query problem when switching from Access to MS SQL
            BKBK Community Member
            BKBK Jan 19, 2014 1:30 AM (in response to weezerboy)

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