CODEWITHSUNDEEP
oracle-databasenullunpivot
exceed007
exceed007

Reputation: 133

oracle - querying NULL values in unpivot query

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;

Upvotes: 3

Views: 6358

Answers (1)

MT0
MT0

Reputation: 168613

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

Upvotes: 6

Related Questions