Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Oracle NVL question

usualmindusualmind Member Posts: 1
I'm rather new to Oracle. I'm in first semester of it.

I'm trying to display a list of employee IDs, salaries, department IDs and the department name in which the employees work. If an employee is not currently assigned to a department, they must still be on the list.

This is what I have:

SELECT e.employee_id, e.salary, NVL(e.department_id, '0'), dep.department_name
FROM employees e, departments dep
WHERE e.department_id = dep.department_id
ORDER BY e.employee_id;

The Employees table has the department ID on it, and so does the Department table. I'm supposed to receive 20 results, but instead I'm only receiving 19.

Any help would be much appreciated.

Thank you,
Dave

Comments

  • MacNeilMacNeil Member Posts: 19
    Why dont you try an outer Join instead of a simple inner join in the where condition.... It might help


    : I'm rather new to Oracle. I'm in first semester of it.
    :
    : I'm trying to display a list of employee IDs, salaries, department IDs and the department name in which the employees work. If an employee is not currently assigned to a department, they must still be on the list.
    :
    : This is what I have:
    :
    : SELECT e.employee_id, e.salary, NVL(e.department_id, '0'), dep.department_name
    : FROM employees e, departments dep
    : WHERE e.department_id = dep.department_id
    : ORDER BY e.employee_id;
    :
    : The Employees table has the department ID on it, and so does the Department table. I'm supposed to receive 20 results, but instead I'm only receiving 19.
    :
    : Any help would be much appreciated.
    :
    : Thank you,
    : Dave
    :

    [blue]MacNeil .E. Fernandes
    Software Engineer.
    [/blue]
    [hr]


  • bhavesh_09bhavesh_09 Member Posts: 5
    try this code,
    SELECT e.empno, e.sal, NVL(e.deptno, '0'), dep.dname
    FROM emp e full outer join dept dep
    on e.deptno = dep.deptno
    ORDER BY e.empno;

  • samymssamyms Member Posts: 5
    : I'm rather new to Oracle. I'm in first semester of it.
    :
    : I'm trying to display a list of employee IDs, salaries, department IDs and the department name in which the employees work. If an employee is not currently assigned to a department, they must still be on the list.
    :
    : This is what I have:
    :
    : SELECT e.employee_id, e.salary, NVL(e.department_id, '0'), dep.department_name
    : FROM employees e, departments dep
    : WHERE e.department_id(+) = dep.department_id
    : ORDER BY e.employee_id;
    :
    : The Employees table has the department ID on it, and so does the Department table. I'm supposed to receive 20 results, but instead I'm only receiving 19.
    :
    : Any help would be much appreciated.
    :
    : Thank you,
    : Dave
    :

Sign In or Register to comment.