CODEWITHSUNDEEP
sql
user33484
user33484

Reputation: 568

sql - order of case statements

have the following code:

select a,b,c,d
case when a > 5 or a < 2 then null end as a
case when a is null then null when a = 0 then b else b/a end as e
from table

effectively I want to exclude every single row where a > 5 or a < 2, then perform calculations on that only. The above code fails in the second case as it does not consider the first case when

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

effectively I want to exclude every single row where a > 5 or a < 2, then perform calculations on that only.

You don't filter rows with case. You filter rows with where:

select a, b, c, d,
       (case when a = 0 then b else b/a end) as e
from table
where a >= 2 and a <= 5;

Of course, given your constrains, a cannot be 0, so the case is superfluous.

If you just want all the rows but the calculation for certain values of a, simplify to:

select a, b, c, d,
       (case when a >= 2 and a <= 5 then b/a end) as e
from table;

Upvotes: 1

Related Questions