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...

[justin_willey]

You can't use a case statement expression to modify the query in that way. As I understand it you are trying to say - "see if cc_mtrl BETWEEN 1 and 550 is true IF (but only if) cc_check1 = 0, OTHERWISE see if cc_mtrl NOT BETWEEN 1 and 550 is true"

You need to restructure the where clause something like this (untested):

WHERE (cc_check = 0 and cc_mtrl BETWEEN 1 and 550)
   OR (cc_check != 0 and cc_mtrl NOT BETWEEN 1 and 550)

(corrected per Mark's comment below)

[VolkerBarth]

Nitpicking: It's a CASE expression, not a CASE statement... - but never mind, I fully second your CASE-free solution:)

[justin_willey]

Indeed - I've corrected it now. Nothing like sending people on wild goose chases to the wrong documentation! Thanks, Justin

[MarkCulp]

I think your expression is not quite correct: you need to include "cc_check != 0" in the second part otherwise the where clause will incorrectly evaluate to true when cc_check is zero and cc_mtrl is not between 1 and 550.

WHERE (cc_check = 0 and cc_mtrl BETWEEN 1 and 550)
   OR (cc_check != 0 and cc_mtrl NOT BETWEEN 1 and 550)

[justin_willey]

Of course - as in Rolle's expression the NOT BETWEEN won't get tested unless cc_ckeck=0 is not true. Thanks

[former_SQLA_member1694869]

For various reasons, I need to be able to set this range three times, because sometimes you want to see different ranges. Cc_check1, cc_check2 cc_check3 is a parameter and it always gives 0 or 1. Same as before.

This does not work at all and produces strange values...?

WHERE (cc_check = 0 and len() >0 cc_mtrl BETWEEN 1 and 50)
   OR (cc_check != 0 and cc_mtrl NOT BETWEEN 1 and 50) OR

      (cc_check2 = 0 and cc_mtrl BETWEEN 65 and 80)
   OR (cc_check2 != 0 and cc_mtrl NOT BETWEEN 65 and 80) OR

      (cc_check3 = 0 and cc_mtrl BETWEEN 110 and 180)
   OR (cc_check3 != 0 and cc_mtrl NOT BETWEEN 110 and 180) OR

[VolkerBarth]

Based on your last comment, I assume the following:

• When the string values "invalue", "invalue2" and "invalue3" are not empty, the dependend condition should be applied.
• Within each condition, the according flag ("cc_check", "cc_check2" and "cc_check3") decides whether the values "cc_mtrl" should lie within or outside a certain range.
• What I do not know is whether the 3 string values exclude each other or not (i.e. can "invalue" and "invalue2" both be non-empty?). - Here I assume they do not exclude each other, and the 3 conditions will be ORed.
• What I also do not know is what your desired result is: just a filter that includes the rows that fulfill the conditions, or a particular count (like in Justin's last comment)? - Here is assume the former.
• UPDATE based on Rolle's last comment: In case all three string values are NULL or empty, do include all rows.

Then the following would do:

 
...
WHERE
      (    len(invalue) > 0
       AND (   (cc_check = 0  AND cc_mtrl     BETWEEN 1 AND 550)
            OR (cc_check != 0 AND cc_mtrl NOT BETWEEN 1 and 550)))
   OR
      (    len(invalue2) > 0
       AND (   (cc_check2 = 0  AND cc_mtrl     BETWEEN 65 AND 80)
            OR (cc_check2 != 0 AND cc_mtrl NOT BETWEEN 65 and 80)))
   OR
      (    len(invalue3) > 0
       AND (   (cc_check3 = 0  AND cc_mtrl     BETWEEN 110 AND 180)
            OR (cc_check3 != 0 AND cc_mtrl NOT BETWEEN 110 AND 180)))
   OR
      (    len(isnull(invalue, '') = 0
       AND len(isnull(invalue2, '') = 0
       AND len(isnull(invalue3, '') = 0)

Note, if you have further contitions, you may need to add another set of brackets around the above conditions. Also note that some of the brackets could be omitted (AND has always higher precedence than OR), in case you and your collegues are aware of that...

And a further note: When one of the string values is null, len() will also return NULL, turning the whole condition into UNKNOWN - but that's just fine here, as "UNKNOWN OR x" will evaluate to "x", i.e. the whole condition of that string value will be omitted. Of course, if all three string values would be empty or null, the WHERE clause will not return any rows. UPDATE: Via the fourth condition, in case all three string values are empty/null, all rows are returned.

[former_SQLA_member1694869]

The logic is exactly what I'm looking for. Thanks for the help.

A small detail is that all these three conditions can be empty, then it will return all items and not be empty. In cases where Not Between is used, all rows are displayed? It does not like OR in those cases, it wants AND? How do I solve that?

[VolkerBarth]

I've added such a condition, see above. Feel free to mark that answer as accepted.

[former_SQLA_member1694869]

Thanks! In cases where Not Between is used, all rows are displayed? It does not like OR in those cases, it wants AND?

[VolkerBarth]

Sorry, I do not understand that question. Please try to say it in different words...

[former_SQLA_member1694869]

What I mean is that if one of these three conditions uses Not Between, I always get all rows? I suspect that in cases where Not Between is used, I can not use OR between these three conditions.

[former_SQLA_member1694869]

When cc_check3! = 0 in one of these statements I use NOT BETWEEN 110 AND 180 for example. But it's not working. Whatever I write in my condition when I used Not Between, all rows are displayed. If I only use When cc_check3 = 0, just Between, it works great. What I mean is that when using Not Between, I can not use OR between these three conditions ...?

[VolkerBarth]

There's nothing particular about NOT BETWEEN, i.e. the following will return the values 1, 2, 5 and 10 from all integers between 1 and 10:

select * from sa_rowgenerator(1, 10)
where row_num not between 3 and 9 or row_num = 5

You should not have problems to combine that condition with any other.

You may help yourself and us by showing some sample data to work on.

[former_SQLA_member1694869]

It does not work if you add a Not Between, which may happen in my case. This return all rows:

select * from sa_rowgenerator(1, 10)
where row_num between 5 and 5 or row_num not between 3 and 6 or row_num not between 9 and 10

[VolkerBarth]

It does exactly work as expected: I have listed the according sub-result sets behind each condition, and apparently, the union of them (because that's what OR does) is the set of all numbers from 1 to 10:

select * from sa_rowgenerator(1, 10)
where row_num between 5 and 5 -- 5
 or row_num not between 3 and 6 -- 1, 2, 7, 8, 9, 10
 or row_num not between 9 and 10 -- 1, 2, 3, 4, 5, 6, 7, 8

What result would you expect here? (Apparently ANDing those three connections will return an empty set here.)

[former_SQLA_member1694869]

Ah, I'm probably thinking wrong.. I expect 1, 2, 5, 7, 8.

[VolkerBarth]

So you seem to expect

select * from sa_rowgenerator(1, 10)
where row_num between 5 and 5 -- 5
 or (     row_num not between 3 and 6 -- 1, 2, 7, 8, 9, 10
     and row_num not between 9 and 10) -- 1, 2, 3, 4, 5, 6, 7, 8

so probably you need to ORing the BETWEEN conditions and ANDing the NOT BETWEEN conditions?

[former_SQLA_member1694869]

Exactly! I have tried to change to "And" and the result will not be as I expect... How do you mean I'll do?

[former_SQLA_member1694869]

You wrote: "so probably you need to ORing the BETWEEN conditions and ANDing the NOT BETWEEN conditions" Then I will not work at all. How do you mean?

[VolkerBarth]

Please please please show us some sample data (i.e. a list of the cc_mtrl values) and show what outcome you expect when the input variables (the three "invalueX" strings and the three "cc_checkX" flags) have some particular values.

Otherwise it will remain a "Read my mind" task for us.

[former_SQLA_member1694869]

This works perfectly when I only use Between. If cc_check, cc_check2 and cc_check3 = 0.

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

WHERE
      (    len(coalesce(invalue, '')) > 0
       AND (   (cc_check = 0  AND cc_mtrl     BETWEEN 1 AND 6)
            OR (cc_check = 1 AND cc_mtrl NOT BETWEEN 1 and 6)))
   OR
      (    len(coalesce(invalue2, '')) > 0
       AND (   (cc_check2 = 0  AND cc_mtrl     BETWEEN 11 AND 15)
            OR (cc_check2 = 1 AND cc_mtrl NOT BETWEEN 11 and 15)))
   OR
      (    len(coalesce(invalue3, '')) > 0
       AND (   (cc_check3 = 0  AND cc_mtrl     BETWEEN 17 AND 20)
            OR (cc_check3 = 1 AND cc_mtrl NOT BETWEEN 17 AND 20)))
   OR
      (    len(coalesce(invalue, '')) = 0
       AND len(coalesce(invalue2, '')) = 0
       AND len(coalesce(invalue3, '')) = 0) -- 1, 2, 3, 4, 5, 6, 11, 12, 13, 14, 15, 17, 18, 19, 20

Expected rows are 1, 2, 3, 4, 5, 6, 11, 12, 13, 14, 15, 17, 18, 19, 20, which works.

//**

In this case I use Not Between cc_check2 = 1

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
      (    len(coalesce(invalue, '')) > 0
       AND (   (cc_check = 0  AND cc_mtrl     BETWEEN 1 AND 6)
            OR (cc_check = 1 AND cc_mtrl NOT BETWEEN 1 and 6)))
   OR
      (    len(coalesce(invalue2, '')) > 0
       AND (   (cc_check2 = 0  AND cc_mtrl     BETWEEN 5 AND 5)
            OR (cc_check2 = 1 AND cc_mtrl NOT BETWEEN 5 and 5)))
   OR
      (    len(coalesce(invalue3, '')) > 0
       AND (   (cc_check3 = 0  AND cc_mtrl     BETWEEN 17 AND 20)
            OR (cc_check3 = 1 AND cc_mtrl NOT BETWEEN 17 AND 20)))
   OR
      (    len(coalesce(invalue, '')) = 0
       AND len(coalesce(invalue2, '')) = 0
       AND len(coalesce(invalue3, '')) = 0) -- 1, 2, 3, 4, 6, 17, 18, 19, 20

Expected rows are 1, 2, 3, 4, 6, 17, 18, 19, 20, which NOT works. All 20 rows are listed?

In case all three string (invalue, invalue2, invalue3) values are NULL or empty, do include all rows.

[Vlad]

I suspect that a small question has grown to the free consulting... 😕

[VolkerBarth]

Is the condition for invalue2/cc_check2 deliberately different in your 2 samples ("between 11 and 15" vs. "between 5 and 5")?

[former_SQLA_member1694869]

Yes it is. To get 2 good examples, I did so.

[VolkerBarth]

So your logic is flawed:

The conditions in the 2nd sample are evaluated to

WHERE

      cc_mtrl     BETWEEN 1 AND 6
   OR
      cc_mtrl NOT BETWEEN 5 AND 5
   OR
      cc_mtrl     BETWEEN 17 AND 20

and that apparently must return all rows because all except 5 fulfil the 2nd condition, and 5 fulfils the first...

Please try to tell in your own words how the "NOT BETWEEN" conditions should be combined with the other conditions...

[former_SQLA_member1694869]

If cc_check2 = 1 (as in my second example) it should run Not Between:

WHERE

      cc_mtrl     BETWEEN 1 AND 6
   AND
      cc_mtrl NOT BETWEEN 5 AND 5
   OR
      cc_mtrl     BETWEEN 17 AND 20

Expected rows are 1, 2, 3, 4, 6, 17, 18, 19, 20.

If cc_check2 = 0 (as in my second example) it should run Between:

WHERE

      cc_mtrl     BETWEEN 1 AND 6
   OR
      cc_mtrl     BETWEEN 5 AND 5
   OR
      cc_mtrl     BETWEEN 17 AND 20

Expected rows are 1, 2, 3, 4, 5, 6, 17, 18, 19, 20.

BETWEEN 5 AND 5 is including in BETWEEN 1 AND 6, but this is just an example of how I want it to work. Hope you understand better now.

[Breck_Carter]

From section 3.10.1 IF and CASE Expressions  in "the book" ...

<searched_case_expression> ::= CASE
WHEN <boolean_expression> THEN <expression>
{ WHEN <boolean_expression> THEN <expression> }
[ ELSE <expression> ]
END

Caveat Emptor: I have not tested this code...

The CASE expression computes and returns a value, so compute that value and compare it with something.

WHERE CASE WHEN <boolean_expression1> THEN 1
           WHEN <boolean_expression2> THEN 1
           WHEN <boolean_expression3> THEN 1
           WHEN <boolean_expression4> THEN 1
           ELSE 0
      END = 1

I'm not sure what you're trying to accomplish, but maybe this works...

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

[former_SQLA_member1694869]

Do not really understand how you mean. I have probably been unclear in my explanation. I want to use between and not between up to three times. Depending on whether "invalue" has any value or not. Not between and between is controlled via "cc_check", "cc_check2" and cc_check3. Here I want it to work. But this does not work ...

  WHERE 
(   CASE 
        WHEN cc_check = 0 AND len(invalue) >0 AND cc_mtrl BETWEEN 1 and 50 THEN 1
        WHEN cc_check = 1 AND len(invalue) >0 AND cc_mtrl NOT BETWEEN 1 and 50 THEN 1
    ELSE 0
        END = 1 
OR

    CASE 
        WHEN cc_check2 = 0 AND len(invalue2) >0 AND cc_mtrl BETWEEN 65 and 80 THEN 1
        WHEN cc_check2 = 1 AND len(invalue2) >0 AND cc_mtrl NOT BETWEEN 65 and 80 THEN 1
    ELSE 0
        END = 1 
OR

    CASE 
        WHEN cc_check3 = 0 AND len(invalue3) >0 AND cc_mtrl BETWEEN 110 and 180 THEN 1
        WHEN cc_check3 = 1 AND len(invalue3) >0 AND cc_mtrl NOT BETWEEN 110 and 180 THEN 1
    ELSE 0
        END = 1
( AND...

[Breck_Carter]

Please let us help you.

Please DO NOT post code that doesn't do what you want.

Please DO tell us exactly what you do want... use your own words, or maybe use pseudo-code.

[VolkerBarth]

Just to add - you wrote:

Depending on whether "invalue" has any value or not

but then use three different variables/column names in your WHERE clause (invalue/invalue2/invalue3).

But then again, it seems that you just need to combine several WHERE filters, and as Justin has already answered, this should generally work without CASE expressions by simply ORing several conditions.

One way to get it to work is to build the (here three?) underlying filters separately and then combine them together (with OR or AND, I can't tell)...

[former_SQLA_member1694869]

What I want is the following: If invalue has a string value, should conditions be added in where. Depending on whether cc_check has 0 or 1, it should be Between or Not between.

The same goes for invalue2, invalue3 and cc_check2, cc_check3

[justin_willey]

but what is the relationship between the three sets of conditions - do all of them have to be true or just one of them.

one condition expression is

 if (cc_check = 0 AND len(invalue) >0 AND cc_mtrl BETWEEN 1 and 50) OR 
(cc_check = 1 AND len(invalue) >0 AND cc_mtrl NOT BETWEEN 1 and 50 ) then 1 ELSE 0 endif

the next is

 if (cc_check2 = 0 AND len(invalue2) >0 AND cc_mtrl BETWEEN 65 and 80) OR 
(cc_check2 = 1 AND len(invalue2) >0 AND cc_mtrl NOT BETWEEN 65 and 80 ) then 1 ELSE 0 endif

and the last is

if (cc_check3 = 0 AND len(invalue3) >0 AND cc_mtrl BETWEEN 110 and 180) OR 
(cc_check3 = 1 AND len(invalue3) >0 AND cc_mtrl NOT BETWEEN 110 and 180 ) then 1 ELSE 0 endif

and so on. I think from what you say you that you want to know if any of these tests pass. If that is right you could just add those expressions up and if the answer is greater than zero then one them must have passed. Something like:

where 
  (if (cc_check = 0 AND len(invalue) >0 AND cc_mtrl BETWEEN 1 and 50) OR 
  (cc_check = 1 AND len(invalue) >0 AND cc_mtrl NOT BETWEEN 1 and 50 ) 
  then 1 ELSE 0 endif

  +

  if (cc_check2 = 0 AND len(invalue2) >0 AND cc_mtrl BETWEEN 65 and 80) OR 
  (cc_check2 = 1 AND len(invalue2) >0 AND cc_mtrl NOT BETWEEN 65 and 80 ) 
  then 1 ELSE 0 endif

+

  if (cc_check3 = 0 AND len(invalue3) >0 AND cc_mtrl BETWEEN 110 and 180) 
OR 
  (cc_check3 = 1 AND len(invalue3) >0 AND cc_mtrl NOT BETWEEN 110 and 180 
  ) then 1 ELSE 0 endif
) 
 > 0  

AND ....

You may need to watch your handling of NULLS