0

Hi,

I am trying to find out list of employees who have no dept_id.


Below code works.
select e.emp_id, e.last_name, e.dept_id
from employees E
where dept_id IS NULL


Below code does not work. i want to rewrite the query using NVL to replace the value by 0. does not work.

select e.emp_id, e.last_name, e.dept_id
from employees E
where NVL(dept_id, 0) IS NULL


Thx.

4
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by urtrivedi
1

Well... No... Of course not.
The Nvl (Dept_ID, 0) is now 0 and not null.
change your where clause to read
Where Nvl (Dept_ID, 0) = 0;
Of course, if the Dept_ID IS 0, you'll get that too.
If I were you, I would stick with your original query,
because that WILL tell you if it IS Null. :)

Votes + Comments
agree
0

usually nvl is used with arithmetic operation (atleast I use for that), when you are not sure that column will contain null or not.
If you add two values where one of them is null, it will return null result even if second one is valid number.

assuming one row table with cola is null, colb =5

select cola+colb; //returns null
select nvl(cola,0)+colb;//returns 5
select nvl(cola,10)+colb;//returns 15
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.