Why I am not able to use a column alias in WHERE clause?

Logical query processing is the conceptual interpretation of the query that defines the correct result, and unlike the keyed-in order of the query clauses, it starts by evaluating the FROM clause. Understanding logical query processing is crucial for correct understanding of T-SQL.

The main statement used to retrieve data in T-SQL is the SELECT statement. Following are the main query clauses specified in the order that you are supposed to type them (known as “keyed-in order”):

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

But as mentioned, the logical query processing order, which is the conceptual interpretation order, is different. It starts with the FROM clause. Here is the logical query processing order of the six main query clauses:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

You can use a CTE :

WITH CTE1 AS (

SELECT * FROM DATAFRAME

WHERE DATE>=CURRENT_DATE-90

CTE2 AS (

SELECT

,CountEmp

,LAG(CountEmp) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE

,DATE

FROM CT2

)

SELECT * FROM CT2

WHERE CountEMP <> PREV_NUMBER_OF_EMP

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi5y ago
Accepted answer
Logical query processing is the conceptual interpretation of the query that defines the correct result, and unlike the keyed-in order of the query clauses, it starts by evaluating the FROM clause. Understanding logical query processing is crucial for correct understanding of T-SQL.
The main statement used to retrieve data in T-SQL is the SELECT statement. Following are the main query clauses specified in the order that you are supposed to type them (known as “keyed-in order”):
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
But as mentioned, the logical query processing order, which is the conceptual interpretation order, is different. It starts with the FROM clause. Here is the logical query processing order of the six main query clauses:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
You can use a CTE :
WITH CTE1 AS (
SELECT * FROM DATAFRAME
WHERE DATE>=CURRENT_DATE-90

),
CTE2 AS (
SELECT
ID
,CountEmp
,LAG(CountEmp) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
,DATE
FROM CT2
)
SELECT * FROM CT2
WHERE CountEMP <> PREV_NUMBER_OF_EMP