Remove rows from SQL when "CASE ELSE" happens

Question

This is my sql query:

select case when table.field1 = 1 then 1
            when table.field2 = 3 then 3
            when table.field2 = 4 then 4
            when table.field3 = 1 then 5
            else .... Status //item name
from table

I want that in case the "else" occurs -> the row will be removed from the dataSet. I can't use "Status" in the where clause for some reason.

Ideas?

Answer 1

The most straitforward way is to set conditions for the fields from "when", I.e. field1=1 or field2 in (3,4) or field3=1

Answer 2

You could use a common table expression:

with TempResult (id, status)
as
(
    select primary_key_column,
           case when table.field1 = 1 then 1
                when table.field2 = 3 then 3
                when table.field2 = 4 then 4
                when table.field3 = 1 then 5
                else 0
    from table
)

select id
from TempResult
where status > 0