CODEWITHSUNDEEP
sqloracle-databaseouter-join
noman pouigt
noman pouigt

Reputation: 976

oracle sql query statement NVL not working as desired

I am trying to learn oracle sql and below is my query. I wanted to print everyone's manager and if someone doesn't a manager then it should return "No One".

Problem: I am getting a blank statement instead of "No One". Any help?

SELECT NVL(m.first_name || ' '
    || m.last_name, 'No One') || ' supervises '
    || w.first_name || ' ' || w.last_name
  FROM employees w, employees m
 WHERE w.manager_id = m.employee_id(+);

NVL(M.FIRST_NAME||''||M.LAST_NAME,'NOONE')||'SUPERVISE
------------------------------------------------------
James Smith supervises Ron Johnson
Ron Johnson supervises Susan Jones
Ron Johnson supervises Fred Hobbs
  supervises James Smith

Upvotes: 1

Views: 880

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

I believe the accepted answer can be simplified by recognizing that when an employee be unsupervised, there will be no matching record for his manager. As a result, both the manager's first and last name will either be NULL or not NULL together.

SELECT NVL(m.first_name, 'No One', m.first_name || ' ' || m.last_name)
    || ' supervises ' || w.first_name || ' ' || w.last_name
FROM employees w LEFT JOIN employees m
    ON w.manager_id = m.employee_id

Update:

The (+) operator is Oracle's syntax for a LEFT JOIN. Have a look at this Stack Overflow article to learn more about Oracle's (+) operator.

Upvotes: 1

Muhammad Zeeshan Karamat
Muhammad Zeeshan Karamat

Reputation: 326

This is because of the reason, that when m.first_name and m.last_name will be null for the manager, then m.first_name || ' ' || m.last_name will not result in null, but with ' '. So, it will print space instead of 'No One'. You can achieve your desired results by using nvl2 something like 

SELECT NVL2( m.first_name ||m.last_name , m.first_name || ' ' || m.last_name, 'No One' )
   || ' supervises '
   || w.first_name
   || ' '
   || w.last_name FROM employees w, employees m WHERE w.manager_id = m.employee_id(+);

Upvotes: 1

Related Questions