469,648 Members | 1,143 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

failed concaination

How do I make a NULL entry from a left join NOT mess up a concatination?
ie if both values are present this statement will work equaling "AF, CD"
however if value is NULL then the concatination fails.
,( #My_AF.activity_CD ||', '|| #My_AP.activity_CD) as History
TIA

meter_man
Dec 8 '06 #1
1 1059
meter_man wrote:
How do I make a NULL entry from a left join NOT mess up a concatination?
ie if both values are present this statement will work equaling "AF, CD"
however if value is NULL then the concatination fails.
,( #My_AF.activity_CD ||', '|| #My_AP.activity_CD) as History
The concatenation does _not_ fail. It just results in NULL. That is quite
logical, since the result of a string that is concatenated with
an "unknown" values is simply not know.

You could interpret a NULL string as empty string, but only if those
semantics are right for you. To that end, you can employ the COALESCE
function:

COALESCE(#My_AF.activity_CD, '') ||', '|| COALESCE(#My_AP.activity_CD, '')

Then you would have to figure out if the handling of commas is still
correct. Maybe a bit more logic like CASE expressions is required:

CASE
WHEN #My_AF.activity_CD IS NOT NULL AND #My_AP.activity_CD IS NOT NULL
THEN #My_AF.activity_CD ||', '|| #My_AP.activity_CD
WHEN #My_AF.activity_CD IS NOT NULL
THEN #My_AF.activity_CD
WHEN #My_AP.activity_CD IS NOT NULL
THEN #My_AP.activity_CD
ELSE ''
END

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Dec 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeff Reed | last post: by
9 posts views Thread by Bijoy Naick | last post: by
1 post views Thread by Laurent Lequenne | last post: by
2 posts views Thread by Dennis | last post: by
1 post views Thread by kencana | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.