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?
5
-
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– Sean Lange2018-05-16 18:23:07 +00:00Commented May 16, 2018 at 18:23
-
3Possible duplicate of Referring to a Column Alias in a WHERE Clausesebu– sebu2018-05-16 18:24:49 +00:00Commented May 16, 2018 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 .– Z .2018-05-16 18:24:56 +00:00Commented May 16, 2018 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– Sean Lange2018-05-16 18:29:18 +00:00Commented May 16, 2018 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– pmbAustin2018-05-16 20:13:30 +00:00Commented May 16, 2018 at 20:13
Add a comment
|
4 Answers
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.
1 Comment
Gordon Linoff
I think
VALUES() makes this much more elegant than a second SELECT.You can put your statement in a cte:
; with CTE as (Select .... as X from ...)
Select *
from CTE
where X = 1
Comments
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
Comments
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';
