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

Answers


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;

?


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"

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;

Need Your Help

Create Windows Phone Styled screen in android

android android-layout

I am new to android XML layout and trying to make a layout that is similar to windows phone home screen. Following image tells what i am looking for.

navigation rule causes JSF1064: Unable to find or serve resource

jsf navigation faces-config

I've the following xhtml page, that is wrapped in the major part of the other pages in my project: