0

My DB2 database has the following tables:

CustomerLog

customer  version  userid  description
--------  -------  ------  ----------------------------
Orange    101      2       New customer
Orange    102      2       Bespoke widgets added
Orange    103      4       Fix errors after review
Orange    104      3       Priority bugfix
Peach     101      1       New customer
Peach     102      3       Custom screens for Peach
...

Users

userid  name
------  -------------------
1       Andrew
2       Belinda
3       Charles

With the select statement:

select c.customer, c.version, u.name, c.description
from customerlog c, users u
where c.customer='Orange'
and c.userid = u.userid

I get the following result:

customer  version  name        description
--------  -------  ----------  ----------------------------
Orange    101      Belinda     New customer
Orange    102      Belinda     Bespoke widgets added
Orange    104      Charles     Priority bugfix

If I instead used a left join, I could get the following row selected as well:

Orange    103      null        Fix errors after review

... but is there a way to get the userid in place of the null? ie:

Orange    103      4           Fix errors after review
  • 1
    Perhaps there's an easy fix, if you would like to update your question with your left join query. – Andriy M Feb 7 '17 at 22:36
  • 1
    If you don't mind accept the answer by click on the accept mark – McNets Feb 7 '17 at 23:38
3

Use COALESCE

Purpose

COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

select c.customer, c.version, coalesce(u.name, to_char(c.userid)) as user c.description
from customerlog c
     left join users u on u.userid = c.userid
where c.customer='Orange'
  • Assuming user ID is numeric, should probably add a char() or varchar() so that the types match. Though the DB will likely implicitly convert it. – Charles Feb 7 '17 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.