on 2017 Apr 20 8:13 AM
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
Request clarification before answering.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Nitpicking: It's a CASE expression, not a CASE statement... - but never mind, I fully second your CASE-free solution:)
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)
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
Based on your last comment, I assume the following:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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 ...?
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.
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.)
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?
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.
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...
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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.
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)...
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
User | Count |
---|---|
61 | |
8 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.