How to filter data in a WHERE clause based on a derived column name?

Question

I am using SQL Server 2008 R2. I'd like to have a WHERE clause on a column that is derived.

I have something like this:

SELECT ActualColumn1, 
CASE WHEN condition THEN value1 ELSE value2 AS DerivedColumn1
FROM
...
WHERE
DerivedColumn1 <> 'Foo' -- I'd like to filter on the derived column

Running this query reports the error "Invalid column name DerivedColumn1".

Answer 1

You cannot refer to an alias from the WHERE directly (you could from the ORDER BY) , you have to use a sub-query or CTE (or repeat the CASE WHEN in the WHERE):

WITH CTE AS
(
    SELECT ActualColumn1, 
    CASE WHEN condition THEN value1 ELSE value2 AS DerivedColumn1
    FROM
    ...
)
SELECT ActualColumn1, DerivedColumn1 
FROM CTE
WHERE DerivedColumn1 <> 'Foo' 

Related: Reference alias (calculated in SELECT) in WHERE clause

Answer 2

You can't, but you can put case in a subquery or in a common table expression.

Check also this question for more information:

Referencing a calculated column in the where clause SQL

Answer 3

Although the derived column is not available to you directly, you have several ways of achieving the desired result:

1. Use the same expression directly - This is not ideal because you need to repeat yourself, but for small expressions it is often acceptable
2. Wrap a select inside another select - This approach lets you filter on the columns of the inner select in the WHERE clause of the outer select.
3. Use Common Table Expression - This approach is similar to the above in that it lets you wrap a select inside another structure (CTE) on which you can use named columns.

Here is an illustration of the last approach:

WITH CTE (ActualColumn1, DerivedColumn1)
AS
(
    SELECT ActualColumn1,
    CASE WHEN condition THEN value1 ELSE value2 AS DerivedColumn1
    FROM ...
)
SELECT ActualColumn1, DerivedColumn1
FROM CTE
WHERE DerivedColumn = ...

Answer 4

SELECT * FROM (
SELECT ActualColumn1, 
CASE WHEN condition THEN value1 ELSE value2 AS DerivedColumn1
FROM
...
) Z
WHERE
DerivedColumn1 <> 'Foo'

NOTE: If you use subquery , giving ALIAS name to subquery is compulsory as shown above- Z otherwise error will be thrown.

Answer 5

You can't use it directly, but you can do like this:

select * from
(
SELECT ActualColumn1, 
CASE WHEN condition THEN value1 ELSE value2 AS DerivedColumn1
FROM
...
)
WHERE
DerivedColumn1 <> 'Foo' -- I'd like to filter on the derived column

Answer 6

You could also use a CROSS APPLY

SELECT tbl.ActualColumn1, cap.DerivedColumn1
FROM ... as tbl
CROSS APPLY (SELECT CASE WHEN condition THEN value1 ELSE value2 END) AS cap(DerivedColumn1)
WHERE cap.DerivedColumn1 <> 'Foo'