NVL function in Oracle

Question

I want to select all the data from the table employee and make an inner join with an other table for example:

SELECT * FROM EMPLOYEE 
INNER JOIN Deparment ON Employee.Id_Department = Deparment.Deparment_Id 
    AND NVL('Mathematics', Deparment.Name);

When I execute that I get an error ORA-00920: Invalid relational operator, I think maybe the nvl() function is the problem here.

What do you want data from AND NVL('Mathematics', Deparment.Name)? — Hong Van Vit, Oct 04 '17 at 08:06

score 2 · Accepted Answer · answered Oct 04 '17 at 06:37

2

you need to add a relational operator like =, !=, < after NVL('Mathematics', Deparment.Name)
as an example :

AND NVL(Deparment.Name,'Mathematics')='Physics'

answered Oct 04 '17 at 06:37

Barbaros Özhan

59,113
10
31
55

score 1 · Answer 2 · edited Oct 04 '17 at 19:25

You have not mentioned the second condition in the join statement also NVL() function parameters misplaced. Try this by assuming employee department name as department_name and change the script as per your need

SELECT *
FROM   employee
       inner join deparment
               ON employee.id_department = deparment.deparment_id
                  AND employee.department_name =
                      NVL(deparment.name, 'Mathematics');

NVL function in Oracle

2 Answers2