"Invalid column name" (use alias inside where clause)

Question

I get this error message from my SQL Server:

Invalid column name 'price'

in Microsoft SQL Server Management Studio.

My SQL statement:

SELECT
    CASE
       WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
          THEN salePrice
          ELSE 
             CASE
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                   THEN salePrice2
                   ELSE normalPrice
             END
    END AS price
FROM 
    prices
WHERE 
    price BETWEEN 1 AND 100

How can I solve this?

`WHERE` clause is executed before `SELECT` statement so `price` doesn't exist when you run your query, use a sub-query or a CTE — Valerica, Jan 22 '18 at 12:53
Possible duplicate of [How to use alias column name in where clause in SQL Server](https://stackoverflow.com/questions/16161938/how-to-use-alias-column-name-in-where-clause-in-sql-server) — Peter B, Jan 22 '18 at 13:02

score 5 · Answer 1 · answered Jan 22 '18 at 12:54

SQL does not allow you to use aliases defined in the SELECT in the WHERE clause (or most other clauses). This is part of the SQL language.

There are various ways to fix this. One method is a subquery. Another is a CTE. SQL Server also offers the lateral join:

SELECT p.*, v.price
FROM prices p OUTER APPLY
     (VALUES (CASE WHEN p.salePriceDate BETWEEN p.salePriceStartDate AND p.salePriceEndDate
                   THEN p.salePrice
                   WHEN p.salePriceDate2 BETWEEN p.salePriceStartDate2 p.AND salePriceEndDate2
                   THEN p.salePrice2
                   ELSE p.normalPrice
              END)
     ) v(price)
WHERE v.price BETWEEN 1 AND 100;

Also note that this simplifies the CASE expression. There is no need to nest CASE.

score 1 · Answer 2 · answered Jan 22 '18 at 12:54

Please try this nested query. In this case, the calculated field will have the where clause rather having to do the case statement twice.

select * from
(
SELECT
    CASE
       WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
          THEN salePrice
          ELSE 
             CASE
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                   THEN salePrice2
                   ELSE normalPrice
             END
    END AS price
FROM 
    prices
) p
where price BETWEEN 1 AND 100

score 1 · Accepted Answer · answered Jan 22 '18 at 12:54

You need to repeat the CASE statement in the WHERE Clause like this:

WHERE 
    CASE
       WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
          THEN salePrice
          ELSE 
             CASE
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                   THEN salePrice2
                   ELSE normalPrice
             END
    END  BETWEEN 1 AND 100

Alternatively, wrap the original query in an inner query:

SELECT * FROM 
( SELECT
    CASE
       WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
          THEN salePrice
          ELSE 
             CASE
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                   THEN salePrice2
                   ELSE normalPrice
             END
    END AS price
FROM 
    prices
) AS a
WHERE 
    price BETWEEN 1 AND 100

score 1 · Answer 4 · answered Jan 22 '18 at 12:54

You could select the query first as a set and apply the where clause.

select price from (SELECT
    CASE
       WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
          THEN salePrice
          ELSE 
             CASE
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                   THEN salePrice2
                   ELSE normalPrice
             END
    END AS price
FROM 
    prices) Records
WHERE 
    price BETWEEN 1 AND 100

score 1 · Answer 5 · answered Jan 22 '18 at 13:00

You can use Common Table Expression to filter by calculated field

with cte as (
    SELECT
        CASE
           WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
              THEN salePrice
              ELSE 
                 CASE
                    WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                       THEN salePrice2
                       ELSE normalPrice
                 END
        END AS price
    FROM 
        prices
)
select * from cte 
where price BETWEEN 1 AND 100

"Invalid column name" (use alias inside where clause)

5 Answers5