Implement "not in" to select with case

Question

I have select:

select col1,
   case
    when col2 = 'AB' then col3
    else cols2
   end as colX,
col4,sum(col5) from table 1
where....
group by col1,
case
when col2 = 'AB' then col3
else cols2
end, col4

How to the where add something like: col2 not in ('AA','BB')?

I can't add col2 to my select, because I have couple of joins in my whole select statement.

What's wrong with `where and col2 not in ('AA','BB')`? – Zohar Peled Jun 28 '16 at 06:48 — Zohar Peled, Jun 28 '16 at 06:48
Post your entire SQL query – StackUser Jun 28 '16 at 06:49 — StackUser, Jun 28 '16 at 06:49
when I did col 2 not in....I got error: Invalid column name – 4est Jun 28 '16 at 06:52 — 4est, Jun 28 '16 at 06:52

score 1 · Accepted Answer · answered Jun 28 '16 at 06:57

Put the new condition in the WHERE clause. Also, if you switch to use a derived table, you don't have to write that case expression twice. (Easier to code without errors, easier to read, easier to maintain.)

select col1, colX, col4, sum(col5)
from
(
    select col1,
           case when col2 = 'AB' then col3
                else cols2
           end as colX,
           col4,
           col5
    from table1
    where...
      and col2 not in ('AA', 'BB')
) dt
group by col1, colX, col4

Implement "not in" to select with case

1 Answers1