CODEWITHSUNDEEP
sqlsql-servercasewhere-clause
JustBeingHelpful
JustBeingHelpful

Reputation: 18980

T-SQL alias from CASE statement in SELECT clause not recognized in WHERE clause

Why does the first query work, but not the second? Why doesn't the alias get recognized when using the CASE statement? How can I fix the second query so the WHERE clause works?

SELECT
[a],
[b],
[c],
[d],
[e],
(CASE WHEN (SELECT COUNT(*) FROM Y WHERE Y.a = X.b AND Y.b IS NOT NULL) > 1 then 1 else 0 END) Q
FROM X
--WHERE Q = 1

SELECT
[a],
[b],
[c],
[d],
[e],
(CASE WHEN (SELECT COUNT(*) FROM Y WHERE Y.a = X.b AND Y.b IS NOT NULL) > 1 then 1 else 0 END) Q
FROM X
WHERE Q = 1

Error for 2nd query:

Invalid column name 'Q'

Upvotes: 0

Views: 1399

Answers (2)

shree.pat18
shree.pat18

Reputation: 21757

A column alias cannot be used in the WHERE clause unless it is introduced in the FROM clause. Either repeat the whole expression or put the SELECT in a CTE and then work with the CTE to use the alias.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270181

This is the correct behavior. If you want to use the alias in the where clause, use a subquery or CTE:

SELECT X.*
FROM (SELECT [a], [b], [c], [d], [e],
             (CASE WHEN (SELECT COUNT(*) FROM Y WHERE Y.a = X.b AND Y.b IS NOT NULL) > 1 then 1 else 0
              END) Q
      FROM X
     ) X
WHERE Q = 1;

Upvotes: 3

Related Questions