Using Alias in Where Clause

Question

I have a very large case in my select statement, that ends as either 1 or 0, and has an alias name "x". I want to check if "x" = 1 in my WHERE statement, but I know that aliases cannot be used in the where statement. Is my only way of checking for this condition to include the original case statement in the WHERE clause?

To be more precise, you can use aliases in the where clause as long as it is a table alias. You are referring to a column alias. — Sean Lange, May 16 '18 at 18:23
Possible duplicate of [Referring to a Column Alias in a WHERE Clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) — sebu, May 16 '18 at 18:24
if it is only 1 or 0 and you have it in the where clause, why do you need to select it? — Z ., May 16 '18 at 18:24
Since this is essentially returning a bit why not skip that column and just have a where clause? Seems you don't really need it in the return values, or at least not as the result of your case expression. — Sean Lange, May 16 '18 at 18:29
Put your main SELECT into a Common Table Expression (CTE) and then SELECT from that, and put your WHERE clause there. — pmbAustin, May 16 '18 at 20:13

score 2 · Answer 1 · answered May 16 '18 at 18:21

2

You could use CROSS/OUTER APPLY:

SELECT *
FROM tab t
CROSS APPLY (SELECT CASE WHEN t.col ... THEN
                          -- very complex conditions
                    END
            ) sub(c)
WHERE sub.c = ?;

This approach allows you to avoid nested subqueries.

answered May 16 '18 at 18:21

Lukasz Szozda

162,964
23
234
275

I think `VALUES()` makes this much more elegant than a second `SELECT`. – Gordon Linoff May 16 '18 at 19:19

score 2 · Answer 2 · answered May 16 '18 at 18:23

2

You can put your statement in a cte:

; with CTE as (Select .... as X from ...)

Select *
from CTE
where X = 1

answered May 16 '18 at 18:23

APH

4,109
1
25
36

score 2 · Answer 3 · answered May 16 '18 at 18:33

How about even simpler? Your case expression is returning a bit. Seems to me that if you need a where clause there is no need to run the case expression more than once.

select MyReturn = 1
from SomeTable
where case with a whole bunch of logic end = 1

Or if you need it to be parameterized something like this.

select MyReturn = @MyBit
from SomeTable
where case with a whole bunch of logic end = @MyBit

Error_2646 · Answer 4 · 2018-05-16T18:35:12.437

1

Doesn't a subquery work just fine?

SELECT ST.*
  FROM (SELECT TBL.*,
               CASE WHEN ComplexCondition THEN 'Something' 
                    ELSE 'SomethingElse'
                END AS aliasedColumn
          FROM SomeTable
       ) ST
 WHERE ST.aliasedColumn = 'Something';

edited May 16 '18 at 18:35

answered May 16 '18 at 18:28

Error_2646

2,555
1
10
22

Using Alias in Where Clause

4 Answers4