CODEWITHSUNDEEP
mysqlleft-joinnvl
kmancusi
kmancusi

Reputation: 571

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

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:
enter image description here
Am I missing something or will nvl() not work in this case?

Upvotes: 0

Views: 57

Answers (3)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

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;

Upvotes: 0

kmancusi
kmancusi

Reputation: 571

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"

Upvotes: 0

Sasha Pachev
Sasha Pachev

Reputation: 5326

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;

?

Upvotes: 0

Related Questions