Filter a case in a where clause

Question

I want to filter on a case statement in my where clause for example:

Select
    name,
    address,
    case when code in (50000-8113, 512388-8114) then ‘M345’ else ‘N/A’ end as Mucode
Where mucode = ‘M345’

The above does not work.

Does this answer your question? [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) — GSerg, Nov 01 '20 at 14:44
`CASE` **expression**. T-SQL does not support `Case` (`Switch`) statements. — Thom A, Nov 01 '20 at 15:01
"Does not work" is NEVER a useful comment to make in a forum like this. Always provide specific information about how you know it does not work. If an error occurs, post the complete error message - all of it. And in case you did not notice, your code is posted using "smart" quotes. Don't use a word processor to write code. — SMor, Nov 01 '20 at 15:18
In addition, this code `in (50000-8113, 512388-8114)` involves math expressions. E.g. The value `50000-8113` is evaluated mathematically and becomes the integer value of 41887. If this logic is based on a string column (code), then you need to use string literals. — SMor, Nov 01 '20 at 15:21

score 0 · Accepted Answer · answered Nov 01 '20 at 15:04

I assume you have a from clause

Select
    name,
    address,
    case when code in (50000-8113, 512388-8114) then ‘M345’ else ‘N/A’ end as Mucode
From someTable
Where mucode = ‘M345’

If you can't change the columns, then you have to repeat the code:

Select
    name,
    address,
    case when code in (50000-8113, 512388-8114) then ‘M345’ else ‘N/A’ end as Mucode
From someTable
Where case when code in (50000-8113, 512388-8114) then ‘M345’ else ‘N/A’ end = ‘M345’

But whenever I see duplicated code like that, I like to move it to a cross apply:

select       name, address, mucode
from         someTable
cross apply (select mucode = 
                case 
                when code in (50000-8113, 512388-8114) then ‘M345’ 
                else ‘N/A’ 
                end
            ) ap
where       mucode = ‘M345’

Filter a case in a where clause

1 Answers1