NVL-NULL

[bangla]

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.

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. :)

[debasisdas]

That simply means you did not understand how NVL works.

[urtrivedi]

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