Case in where clause

[former_SQLA_member1694869]

This does not work. I get syntax error. How do you do this in the best way?

cc_chek1 is a parameter and it always gives 0 or 1.

WHERE
        CASE WHEN cc_check1 = 0 THEN
            cc_mtrl BETWEEN 1 and 550
        ELSE
            cc_mtrl NOT BETWEEN 1 and 550
        END

[VolkerBarth · Accepted solution]

Based on Rolle's further comments and his sample beneath my previous answer, here is another attempt - I have modified the third range (14-20) to have an intersection with the second range (11-15).

Note that the NOT BETWEEN conditions are ANDed with the other ones, and must therefore behave "neutrally" by evaluating to TRUE when the NOT BETWEEN condition shall not be applied.

SELECT 
    row_num as cc_mtrl, 
    'Text' as invalue, 
    'Text' as invalue2, 
    'Text' as invalue3, 
    0 as cc_check, 
    1 as cc_check2, 
    0 as cc_check3 
FROM
     sa_rowgenerator(1, 30)

WHERE
   -- filter "positively" on cc_checkX = 0 and BETWEEN ranges, as disjunctions
   (   (len(coalesce(invalue,  '')) > 0 AND cc_check  = 0 AND cc_mtrl BETWEEN  1 AND  6)
    OR (len(coalesce(invalue2, '')) > 0 AND cc_check2 = 0 AND cc_mtrl BETWEEN 11 AND 15)
    OR (len(coalesce(invalue3, '')) > 0 AND cc_check3 = 0 AND cc_mtrl BETWEEN 14 AND 20))

   -- filter "negatively" on cc_checkX = 1 and NOT BETWEEN ranges, as conjunctions,
   -- and ignore the filter when the pre-condition does not apply
   -- (i.e. when input string is empty and/or cc_checkX = 0)
   AND (    len(coalesce(invalue,  '')) = 0 OR cc_check  = 0
        OR (len(coalesce(invalue,  '')) > 0 AND cc_check  = 1 AND cc_mtrl NOT BETWEEN  1 AND  6))
   AND (    len(coalesce(invalue2, '')) = 0 OR cc_check2 = 0
        OR (len(coalesce(invalue2, '')) > 0 AND cc_check2 = 1 AND cc_mtrl NOT BETWEEN 11 AND 15))
   AND (    len(coalesce(invalue3, '')) = 0 OR cc_check3 = 0
        OR (len(coalesce(invalue3, '')) > 0 AND cc_check3 = 1 AND cc_mtrl NOT BETWEEN 14 AND 20))

   -- don't filter when all input strings are empty/null
   OR
      (    len(coalesce(invalue, '')) = 0
       AND len(coalesce(invalue2, '')) = 0
       AND len(coalesce(invalue3, '')) = 0) -- 1, 2, 3, 4, 6, 17, 18, 19, 20

will return rows with cc_mtrl in the ranges 1-6 and 16-20.

I guess it's basically a variation of DeMorgan's laws: When you want to negate an OR'ed condition, you have to AND the negation...