Left join and concatenated column displaying blanks instead of (null)

Question

I'm doing a left outer join of two tables where the manager_id of one table can be joined to values in another table's employee_id. The query I wrote runs, but I am trying to use nvl() to replace the null values with the string 'NO_MANAGER'. However, this is what I get instead:

select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, nvl(d.manager_id, 0) AS MANAGER_ID, d.LOCATION_ID, 
nvl(e.first_name || ' ' || e.LAST_NAME,'NO_MANAGER') AS NAME 
from departments d
left join employees e on d.manager_id = e.EMPLOYEE_ID order by d.DEPARTMENT_ID;

This is what is returned:

Am I missing something or will nvl() not work in this case?

@JuanCarlosOropeza, is there a way to maintain the columns being concatenated and have this still work? — kmancusi, Dec 04 '15 at 01:38

score 0 · Answer 1 · answered Dec 04 '15 at 01:39

0

Does this work for you:

select d.DEPARTMENT_ID, d.DEPARTMENT_NAME,
 ifnull(d.manager_id, 0) AS MANAGER_ID, d.LOCATION_ID, 
if(e.first_name is null or e.LAST_NAME is null,'NO_MANAGER',
 concat(e.first_name, ' ', e.LAST_NAME)) AS NAME 
from departments d
left join employees e on d.manager_id = e.EMPLOYEE_ID 
order by d.DEPARTMENT_ID;

?

answered Dec 04 '15 at 01:39

Sasha Pachev

5,162
3
20
20

When I try to run this, I get a "missing right parenthesis" error – kmancusi Dec 04 '15 at 04:18
Are you using MySQL? I just tried the query above with MySQL after creating the tables, and the parser took it. Besides, "missing right parenthesis" does not sound like an error from MySQL parser, which does not try to guess what exactly is wrong with your syntax, it just gives you the location where it stumbled. – Sasha Pachev Dec 04 '15 at 21:55
apologies...I forgot to clarify that I was using Oracle Sql Developer when doing this – kmancusi Dec 05 '15 at 04:19

score 0 · Answer 2 · answered Dec 07 '15 at 00:02

0

Found a solution that displays the results I was looking for:

TRIM(LEADING FROM e.FIRST_NAME || ' ' || COALESCE(e.LAST_NAME, 'No Manager')) AS "NAME"

answered Dec 07 '15 at 00:02

kmancusi

591
3
20

for study porpouse, take a look to my answer, and understand what is happen with the first query :). – Juan Ruiz de Castilla Dec 07 '15 at 01:18

score 0 · Answer 3 · answered Dec 07 '15 at 01:18

This happen because when you put:

e.first_name || ' ' || e.LAST_NAME

automaticaly your null value concatenated with string convert into string value different to null, this is an implicit cast, and nvl can't find null.

You can try this (or another solution of course)

select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, nvl(d.manager_id, 0) AS MANAGER_ID, d.LOCATION_ID, 
nvl(e.first_name,'NO_MANAGER') || nvl(e.LAST_NAME,'') AS NAME 
from departments d
left join employees e on d.manager_id = e.EMPLOYEE_ID order by d.DEPARTMENT_ID;

Left join and concatenated column displaying blanks instead of (null)

3 Answers3