| Author |
Message
|
| mqsiuser |
 Posted: Wed Oct 29, 2014 1:01 am Post subject: Boolean, check for NULL and case statement |
 |
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Isn't this wrong:
| Code: |
SET myVariable =
CASE myBooleanVariable
WHEN NULL THEN <something>
ELSE <something else>
END; |
And this right:
| Code: |
IF myBooleanVariable IS NULL THEN
SET myVariable = <something>
ELSE
SET myVariable = <something else>
END IF; |
because it is... "myBooleanVariable IS NULL" and not "myBooleanVariable = NULL" ?
_________________
Just use REFERENCEs |
|
| Back to top |
|
 |
| kimbert |
| Posted: Wed Oct 29, 2014 2:24 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
I am wondering why you are asking the question. What does the Knowledge Center say about the syntax of an ESQL CASE statement? Does the toolkit flag an error when you try it?
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Oct 29, 2014 4:36 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| kimbert wrote: |
| I am wondering why you are asking the question. |
I can understand that. I would just use "IF, ELSEIF and ELSE"...
... but this is a codebase I inherited and CASE is used all over the place (including for booleans and to check for "NULL").
We'd have to fix quite a bit if my assumption holds..
| kimbert wrote: |
| What does the Knowledge Center say about the syntax of an ESQL CASE statement? |
I found nothing specific to this particular problem:
The question is: How is "CASE ... WHEN" implemented (internally)? ... like I'd guess it is... implemented with "=" for everything (and not "IS (NOT)" for "NULL").
| kimbert wrote: |
| Does the toolkit flag an error when you try it? |
No! It also deploys well. I stepped through with the debugger and myBooleanVariable was NULL, but (with the "CASE WHEN") it stepped into the ELSE branch (which it shouldn't).
I adjusted the code to use "IF ELSE" and it worked (as expected).
The questions for us remains, if we'd have to go through the entire code base and adjust these "CASE WHEN" statements (for booleans and NULL).
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Oct 29, 2014 6:53 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Have you tried replacing WHEN NULL with WHEN IS NULL ?
Does it make a difference?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Oct 29, 2014 7:26 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| fjb_saper wrote: |
Have you tried replacing WHEN NULL with WHEN IS NULL ?
Does it make a difference? |
Syntax Error (in the Toolkit)
So won't let me.
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Oct 29, 2014 8:32 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Your original example is wrong when checking for NULL but you do not need to switch to an IF statement, as you can simply use a 'searched' CASE expression rather than a 'simple' CASE statement like this:
| Code: |
SET myVariable = CASE
WHEN myBooleanVariable IS UNKNOWN THEN 'I am NULL'
ELSE 'I am NOT NULL'
END; |
kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Oct 29, 2014 8:43 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Will try that !
Sounds very good !
EDIT: I tried it with "NULL" instead of "UNKNOWN" (because that's just what I need) and debugged it. The debugger won't step into the CASE-statement (the sub-cases), ...
... so I am switching back and use (good old) "IF THEN (ELSEIF) ELSE" (the debugger steps into the individual cases, which is what I prefer).
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
|
|
