CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Steve Hibbert
Steve Hibbert

Reputation: 2175

SQL : filter on case-statement data-substitution in the where clause

If I have a table called 'Beasties', and I want to do some conditional substitution of the data values in a column, eg Animal, and then filter on that column, I would try to do this...

SELECT 
   (CASE Animal 
    WHEN 'Horse' THEN 'Zebra' 
    ELSE Animal
    END) AS Species
FROM Beasties 
WHERE Species='Zebra'

But T-SQL will not allow the WHERE clause, it would only let me use Animal instead of Species. How can I filter on a renamed (and therefore edited) column value?

Thanks in advance for any help on this.

Upvotes: 1

Views: 7981

Answers (3)

Xophmeister
Xophmeister

Reputation: 9219

You could just stick it in a subquery:

SELECT *
FROM (
    SELECT 
       (CASE Animal 
        WHEN 'Horse' THEN 'Zebra' 
        ELSE Animal
        END) AS Species
    FROM Beasties
)
WHERE Species='Zebra'

Upvotes: 2

Ken Clark
Ken Clark

Reputation: 2530

Do this:

SELECT 
   (CASE Animal 
    WHEN 'Horse' THEN 'Zebra' 
    ELSE Animal
    END) AS Species
FROM Beasties 
WHERE 'Zebra'=(CASE Animal WHEN 'Horse' THEN 'Zebra'  ELSE Animal END)

Upvotes: 5

Panzercrisis
Panzercrisis

Reputation: 4750

I don't know if there's a particularly elegant way to do it, but this should work if there isn't:

WHERE Animal IN ('Horse', 'Zebra')

Upvotes: 1

Related Questions