oracle - querying NULL values in unpivot query

Question

I want to fetch records from oracle DB where column value is NULL. Also i am using unpivot in my query. Somehow NULL values are not getting selected because of unpivot keyword. Can you please help me about how to get rows for the same when using unpivot.

EDIT:

SELECT a.emp_id, a.emp_dept, b.emp_location
  FROM employee a,
       location b UNPIVOT (emp_id
                  FOR dummy_id
                  IN (emp_id AS 'EMP_ID', last_date AS 'LAST_DATE'))
 WHERE emp_id = 123 AND b.emp_loc_id = 'india' AND b.location IS NULL;

Answer 1

Use UNPIVOT INCLUDE NULLS:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test ( id, a, b, c, d ) AS
SELECT 1, 1, 2, 3, 4 FROM DUAL UNION ALL
SELECT 2, 1, NULL, 3, NULL FROM DUAL;

Query 1:

SELECT *
FROM   test
UNPIVOT INCLUDE NULLS ( value FOR name IN ( a, b, c, d ) )

Results:

| ID | NAME |  VALUE |
|----|------|--------|
|  1 |    A |      1 |
|  1 |    B |      2 |
|  1 |    C |      3 |
|  1 |    D |      4 |
|  2 |    A |      1 |
|  2 |    B | (null) |
|  2 |    C |      3 |
|  2 |    D | (null) |