Add values in where clause using case is giving error

March 7, 2022

I am not good in sql. But I am trying to do some thing like the following

    where 1 = 1
    and case 
        when first_criterion = max1 or first_criterion = min1 then 1 else 0
        + 
        when second_criterion = max2 or second_criterion = min2 then 1 else 0
        +
        when third_criterion = max3 or third_criterion = min3 then 1 else 0
    end < 2
    order by arbiter_id;

If I just use

where 1 = 1
and case 
        when first_criterion = max1 or first_criterion = min1 then 1 else 0
    end < 2
order by arbiter_id;

Then it works

How can I add values using the first one ?

Thanks

>Solution :

If you want to filter out the rows where more than 1 of the conditions apply then you need 3 separate CASE expressions:

WHERE 1 = 1
  AND CASE WHEN first_criterion = max1 OR first_criterion = min1 THEN 1 ELSE 0 END
      + 
      CASE WHEN second_criterion = max2 OR second_criterion = min2 THEN 1 ELSE 0 END
      +
      CASE WHEN third_criterion = max3 OR third_criterion = min3 THEN 1 ELSE 0 END
      < 2
ORDER BY arbiter_id;

If you actually want the rows where exactly 1 of the conditions apply, you should change < 2 to = 1.