How to replace NULL value in select with subquery

Question

Hi I'm performing a left join on two tables. If a particular column is NULL I want to run a subquery to get a value from a completely different table. Here's what I have now:

SELECT A.ACCOUNT_NUM, A.USER_ID, 
   CASE B.PREFERRED_NAME
        WHEN '' THEN RTRIM(B.FIRST_NAME) || ' ' || B.LAST_NAME
        ELSE RTRIM(B.PREFERRED_NAME) || ' ' || B.LAST_NAME 
   END AS NAME
FROM TABLE_A A
LEFT JOIN TABLE_B B
ON A.USER_ID = B.USER_ID

TABLE_B sometimes doesn't contain a record that matches with TABLE_A, so I want to run a subquery from TABLE_C that contains usernames and will match on A.USER_ID.

I thought I could do something like:

CASE B.PREFERRED_NAME
     WHEN NULL THEN subquery here

But I get this error:

ERROR [42703] [IBM][DB2] SQL0206N  "NULL" is not valid in the context where it is used.

Probably because NULLs are not allowed for that column.

SOLVED Thanks for the help. This is how I solved my issue:

SELECT A.ACCOUNT_NUM, A.USER_ID, 
CASE 
    WHEN B.PREFERRED_NAME IS NULL THEN C.USER_ID
    WHEN B.PREFERRED_NAME IS NOT NULL THEN 
         CASE PREFERRED_NAME
         WHEN '' THEN RTRIM(B.FIRST_NAME) || ' ' || B.LAST_NAME
         ELSE RTRIM(B.PREFERRED_NAME) || ' ' || B.LAST_NAME 
         END
END AS NAME
FROM TABLE_A A
LEFT JOIN TABLE_B B
ON A.USER_ID = B.USER_ID
JOIN TABLE_C C
ON A.USER_ID = C.USER_ID

Not sure about the DB2 specifics. Did you try rewriting like `CASE WHEN B.PREFERRED_NAME = '' THEN ... WHEN B.PREFERRED_NAME IS NULL THEN ...`? However, what exactly do you mean by "[...] because NULLs are not allowed for that column." — Abecee, Aug 21 '15 at 22:01
possible duplicate of [T-SQL CASE Clause: How to specify WHEN NULL](http://stackoverflow.com/questions/3237646/t-sql-case-clause-how-to-specify-when-null) — Bulat, Aug 21 '15 at 22:02

bhamby · Answer 1 · 2015-08-21T22:28:17.347

Depending on your query, you can probably just add your third table as another LEFT JOIN, then add the column you want to a COALESCE function:

Also, it looks like you're storing the preferred name as spaces if there isn't one, in which case you can use the NULLIF function to convert it to a NULL, which will work with your COALESCE.

Here's an example of what I mean:

SELECT
     A.ACCOUNT_NUM
    ,A.USER_ID
    ,COALESCE(
         NULLIF(B.PREFERRED_NAME,'')
        ,B.FIRST_NAME
        ,C.OTHER_NAME
    ) || ' ' || B.LAST_NAME AS NAME
FROM TABLE_A A
LEFT JOIN TABLE_C C
  ON C.USER_ID = A.USER_ID
LEFT JOIN TABLE_B B
  ON A.USER_ID = B.USER_ID

If you know there is always going to be a row in C that matches A, then you could convert that to a regular (inner) JOIN.

The reason you're getting the error, though is because you can't use NULL like that in a CASE statement. If you want to have a NULL case, then you have to do it like @Abecee said in the comment with CASE WHEN B.PREFERRED_NAME IS NULL THEN ...

How to replace NULL value in select with subquery

1 Answers1