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

asked 20 Apr, 07:13

Rolle's gravatar image

Rolle
374213041
accept rate: 0%


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)

permanent link

answered 20 Apr, 08:03

Justin%20Willey's gravatar image

Justin Willey
6.6k107136205
accept rate: 20%

edited 20 Apr, 08:50

1

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

(20 Apr, 08:18) Volker Barth
Replies hidden

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

(20 Apr, 08:32) Justin Willey

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)
(20 Apr, 08:42) Mark Culp
Replies hidden

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

(20 Apr, 08:45) Justin Willey

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
(27 Apr, 07:39) Rolle
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
permanent link

answered 2 days ago

Breck%20Carter's gravatar image

Breck Carter
27.4k425586838
accept rate: 21%

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...
(2 days ago) Rolle
Replies hidden
1

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.

(yesterday) Breck Carter

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

(yesterday) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13

question asked: 20 Apr, 07:13

question was seen: 85 times

last updated: yesterday