| Author |
Message
|
| geewee |
 Posted: Thu Aug 02, 2012 7:15 am Post subject: how to concatenate two strings in esql, if one/both are null |
 |
|
Apprentice
Joined: 31 Jul 2012
Posts: 28
|
I am building up a log message using
SET logmsg = firstPart || secondPart;
this concatenation works as expected if both firstPart and secondPart is not null.
But if either operand is NULL, the result is NULL.
This is unexpected behaviour.
How would you code this to make sure the logmsg actually get as much info as available, and ignoring the null part.
A large if test for every combination of firstPart, secondPart or both null gives just to verbose code for this simple task. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Thu Aug 02, 2012 7:23 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Aug 02, 2012 7:33 am Post subject: Re: how to concatenate two strings in esql, if one/both are |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
try this:
| geewee wrote: |
| SET logmsg = COALESCE(firstPart, '') || COALESCE(secondPart, ''); |
| geewee wrote: |
| But if either operand is NULL, the result is NULL. |
That has to to with computer science... if any operand within a chain of operations is undefined, then the result is undefined. It is like multiplying (somewhere with zero (0)). |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Aug 02, 2012 7:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| http://pic.dhe.ibm.com/infocenter/ratdevz/v7r5/index.jsp?topic=%2Fcom.ibm.etools.est.doc%2Fref%2Frsfsql027.htm |
That link does not point where you think it points, nor does it point anywhere directly helpful. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Aug 02, 2012 7:43 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mqjeff wrote: |
| lancelotlinc wrote: |
| http://pic.dhe.ibm.com/infocenter/ratdevz/v7r5/index.jsp?topic=%2Fcom.ibm.etools.est.doc%2Fref%2Frsfsql027.htm |
That link does not point where you think it points, nor does it point anywhere directly helpful. |
Good catch. I may have not copied the right URL.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| geewee |
| Posted: Thu Aug 02, 2012 8:48 am Post subject: |
|
|
Apprentice
Joined: 31 Jul 2012
Posts: 28
|
Thanks for all replies, and the reference to computer science was very useful, what about mathematics?
Concatenating two strings,
'abb' || null = 'abb'
'abb' || null = null
What makes more sense?
I accept the fact that second option is the chosen way in this area of computer science, I just needed hints on how to code this in efficient way without if-testing for every combination. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Aug 02, 2012 8:54 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| geewee wrote: |
Thanks for all replies, and the reference to computer science was very useful, what about mathematics?
Concatenating two strings,
'abb' || null = 'abb'
'abb' || null = null
What makes more sense? |
The distinction here is the difference between an element that EXISTS but has NULL as it's value, and an element that DOES NOT exist.
Mathematics only has a notion of an element that has a null value, if I remember my set theory. That is, you can't express
| geewee wrote: |
| I accept the fact that second option is the chosen way in this area of computer science, I just needed hints on how to code this in efficient way without if-testing for every combination. |
That's what COALESCE is for. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Aug 02, 2012 9:45 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| geewee wrote: |
| I accept the fact that second option is the chosen way in this area of computer science, I just needed hints on how to code this in efficient way without if-testing for every combination. |
That's what COALESCE is for. |
IMHO the COALESCE is less cumbersome than the (perfectly serviceable) LENGTH check. It's my construction of choice.
This in no way is intended to indicate it should be everyone else's choice, or anyone else's choice.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| geewee |
| Posted: Thu Aug 02, 2012 10:33 am Post subject: |
|
|
Apprentice
Joined: 31 Jul 2012
Posts: 28
|
thanks a lot, mqsiuser
COALESCE is perfect and gives med the expected behaviour in a coding efficient way.
SET logmsg = COALESCE(firstPart, '') || COALESCE(secondPart, '');
regarding the fading discussion on expected behaviour:
A value of null means that the value is unknown, undefined, or uninitialized.
CONCAT(string_expl, string_exp2)
Returns a character string that is the result of concatenating string_exp2 to string_exp1. The resulting string is DBMS dependent. For example, if the column represented by string string_exp1 contains a NULL value, DB2 would return NULL, but SQL Server would return the non-NULL string.
in java, c#:
Why doesn't concatenating with null throw a null reference exception? Is it to make a programmer's life easier, such that they don't have to check for null before concatenation?
In java a null operand is converted to the string literal "null" |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Fri Aug 03, 2012 3:33 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
I just wanted to point to that this decision that one NULL-value will "whipe out" the other strings (when concatenating) is not like 'a decisons from ESQL', but just the way people probably found out it should be... at least something that people with a CS backround (from some decades ago  ) might expect. They argue like: "There is true, there is false, and then there is undefined"... and then this results in logically correct (but to normal people unintuitive) decision tables (for operators that operate on these values).
Probably this is just something coming out of / from the underlying C(++)-code... likely its a decision (to keep it) to make ESQL correct.
We use COALESCE a lot... especially irritating is when your exception-message is nulled out ... so use COALESCE on anything that may be(come) NULL when concatenating strings(CHARs). It is also useful to say e.g. COALESCE( dbValue, 'NULL') on an exception string (instead of saying COALESCE(dbValue, '') all the time).
| Quote: |
in java, c#:
Why doesn't concatenating with null throw a null reference exception?
Is it to make a programmer's life easier, such that they don't have to check for null before concatenation? |
There are NULL pointer exceptions in Java ... but likely yes: (Simple) types in Java need to be initialized with something when they are declared afaik.
Objects use the toString() method. Java has objects only, c# has objects and simple types (which may be boxed to objects if necessary (and then called toString())... there was something called "nullable types" a couple of years ago.
NULL is not the same as 'NULL': Putting the null-string ('null' or 'NULL') into a trace(-file) or an exception-text is likely ok. Returning the null-string (instead of a real/native NULL) during processing can bring you (the developer) into trouble.
Probably (the existens of (it is always there) and to implement or overwrite) "toString()" (in Java/c#) is somewhat equivalent to using COALESCE() (in ESQL).
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
|
|
