Add values in where clause using case is giving error-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

I assume you are making a typo, can you check this:

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