oracle-在unpivot查询中查询NULL值 [英] oracle - querying NULL values in unpivot query

问题描述

我想从Oracle数据库中获取列值为NULL的记录.我也在查询中使用unpivot.由于unpivot关键字，以某种方式未选择NULL值.使用unpivot时，如何获得相同的行，请您帮我一下.

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.

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;

推荐答案

使用 UNPIVOT INCLUDE NULLS :

SQL小提琴

Oracle 11g R2架构设置:

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;

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

这篇关于oracle-在unpivot查询中查询NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！