CODEWITHSUNDEEP
sqloracle-database
user4298563
user4298563

Reputation:

Why does NVL2 returns NULL when it's not expected?

Let preparation structure be

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;

The following query

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 FROM_NVL2 column contain all nulls? I've been expecting that FROM_NVL2 will result in the same values as FROM_CASE did.

Upvotes: 4

Views: 453

Answers (2)

StuartLC
StuartLC

Reputation: 107317

From your case example it seems that you are trying to use NVL2 as a means of null coalescing 3 values, or you have swapped the order expr2 and expr3, which is not correct.

The equivalent of case when P.VALUEA is null then Q.VALUEB else P.VALUEA end would be:

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

Which would be the same as basic NVL:

NVL(P.VALUEA, Q.VALUEB)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The logic for NVL2() is described as:

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

The equivalent case would be:

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

Upvotes: 2

Related Questions