CODEWITHSUNDEEP
sqlsql-server
Jay
Jay

Reputation: 41

Using Alias in Where Clause

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?

Upvotes: 2

Views: 95

Answers (4)

Error_2646
Error_2646

Reputation: 3791

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';

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 2

APH
APH

Reputation: 4154

You can put your statement in a cte:

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

Select *
from CTE
where X = 1

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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.

Upvotes: 2

Related Questions