Skip to content
Advertisement

SQL Server LEAD function

I am getting the following error when I run this query.

[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘NEXT_DATE’. (207) (SQLExecDirectW)

This is a leetcode medium question 550. Game Play Analysis IV. I wanted to know why it can’t identify the column NEXT_DATE here and what am I missing? Thanks!

Advertisement

Answer

The problem is in this CTE:

Note that you are creating NEXT_DATE as a column alias in this CTE but also referring to it in the WHERE clause. This is invalid because by SQL clause-ordering rules the NEXT_DATE column alias does not exist until you get to the ORDER BY clause which is the last evaluated clause in a SQL query or subquery. You don’t have an ORDER BY clause in this subquery, so technically the NEXT_DATE column alias only exists to [sub]queries that both come after and reference your CTE_CONSEC_PLAYERS CTE.

To fix this you’d probably want two CTEs like this (untested):

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement