Is there a way to use conditions in case-when statement?

Question

My query(won't run):

SELECT * FROM TABLE A
WHERE (case when mode = 1 then Tonnage > 1000
          when mode = 2 then Tonnage > 5000
          else Tonnage < -1
)

What I am trying to do is to choose different filter critieria (tonnage) based on the other column (Mode).

However, this won't work due to the >.

Is there a way to fix it?

you cannot split the conditions to each select statement the join the dataset by union ? — Sudipta Mondal, Jul 10 '17 at 14:42
Possible duplicate of [SQL Conditional Where](https://stackoverflow.com/questions/353425/sql-conditional-where) — underscore_d, Jul 10 '17 at 14:47

Gordon Linoff · Accepted Answer · 2017-07-10T17:07:03.693

2

You don't need a case:

where (mode = 1 and Tonnage > 1000) or
      (mode = 2 and Tonnage > 5000) or
      ( (mode not in (1, 2) or mode is null) and Tonnage < -1)

edited Jul 10 '17 at 17:07

answered Jul 10 '17 at 14:42

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jul 10 '17 at 14:50

0

You can also do it with a case statement, curious on performance outcomes between the two.

SELECT * 
FROM TABLE A
WHERE (case when mode = 1 and Tonnage > 1000 then 1
          when mode = 2 and Tonnage > 5000 then 1
          when tonnage < -1 then 1
          else 0 end
    ) = 1

answered Jul 10 '17 at 14:50

Jesse

865
10
18

Computing a case and then comparing that is generally less efficient than direct expressions, because the optimizer can't use any filters or statistics. It "has to" compute the outcome per row and then compare that. (It doesn't really have to, but because of the short circuiting logic of `CASE` it would be complicated to optimize.) If you're already doing a table scan, there's not much difference. – Jeroen Mostert Jul 10 '17 at 14:59

Is there a way to use conditions in case-when statement?

2 Answers2