CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ttt
ttt

Reputation: 6809

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

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?

Upvotes: 1

Views: 1217

Answers (5)

SergeyA
SergeyA

Reputation: 4487

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

Upvotes: 1

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 5

SchmitzIT
SchmitzIT

Reputation: 9552

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

Upvotes: 1

Ctznkane525
Ctznkane525

Reputation: 7465

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

Upvotes: 1

Related Questions