Geek Answers Handbook

Why does NVL2 return NULL when not expected?

Let the training structure

create table RESOURCE1(id number, valueA varchar2(255));
create table RESOURCE2(id number, valueB varchar2(255));
create table IDS(id number);

insert into IDS
  select 1 from DUAL
  union select 2 from DUAL
  union select 3 from DUAL
  union select 4 from DUAL;

insert into RESOURCE1
      select 1, 'ABC' from dual
union select 2, 'DEF' from dual;

insert into RESOURCE2
      select 3, 'GHI' from dual
union select 4, 'JKL' from dual;

Next request

select P.VALUEA, Q.VALUEB
     , NVL2(P.VALUEA, Q.VALUEB, P.VALUEA) FROM_NVL2 
     , case when P.VALUEA is null then Q.VALUEB else P.VALUEA end FROM_CASE
  from IDS
  left join RESOURCE1 P on P.ID = IDS.ID
  left join RESOURCE2 Q on Q.ID = IDS.ID
    order by ids.id;

produces

VALUEA  VALUEB  FROM_NVL2   FROM_CASE
 ABC    (null)    (null)       ABC
 DEF    (null)    (null)       DEF
(null)   GHI      (null)       GHI
(null)   JKL      (null)       JKL

Why does column FROM_NVL2 contain all NULL values? I expected FROM_NVL2 to result in the same values ​​as FROM_CASE.

+4
source share
2 answers

The logic for NVL2() is described as:

NVL2 allows you to determine the value returned by a query based on whether the specified expression is NULL or null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

Equivalent to casebe:

(case when P.VALUEA is not null then Q.VALUEB else P.VALUEA end)
+2
source

case , NVL2 3 expr2 expr3, .

case when P.VALUEA is null then Q.VALUEB else P.VALUEA end :

NVL2(P.VALUEA, P.VALUEA, Q.VALUEB)

, NVL:

NVL(P.VALUEA, Q.VALUEB)
+1

More articles:


All Articles