NVL2 - useful? Two use cases

Sometimes you encounter a function that makes you wonder why Oracle made it. NVL2 is one of those functions, at least I think so. Even though it was introduced quite a long time ago, I've never used it in production code.
Until now that is.
NVL2 was introduced in Oracle 8i (8.1.7 according to Tahiti.Oracle.com)


Syntax
The NVL2 function takes three arguments. The first argument (could also be an expression) is evaluated for NULL or NOT NULL. When this argument evaluates to NOT NULL, the second expression is returned. When the first argument evaluates to NULL then last (third) expression is returned.
It works like this pseudocode:

if argument1 is not null
then 
   argument2;
else
   argument3;
end if;

First Use Case: Concatenate strings with an optional delimiter
In The Netherlands, where I'm from, you can choose the way your last name should be used after marriage.
In general there are four options, say your lastname is "Jansen" and your partners name is "de Vries" - both very common names in The Netherlands - then these are your options:

1. keep your own lastname: Jansen


2. use the lastname of your partner: de Vries


3. your own lastname hyphen partner lastname: Jansen - de Vries


4. partner lastname hyphen own lastname: de Vries - Jansen

Say we have a table which store your name, your partners name and your preference of "name usage"

drop table names
/

create table names
(lastname         varchar2(35)
,partner_lastname varchar2(35)
,name_usage       varchar2(2)
)
/
insert into names values ('Jansen', 'de Vries', 'O') -- Own
/
insert into names values ('Jansen', 'de Vries', 'P') -- Partner
/
insert into names values ('Jansen', 'de Vries', 'OP') -- Own - Partner
/
insert into names values ('Jansen', 'de Vries', 'PO') -- Partner - Own
/

Using a simple Case Expression we can have the names the way we want them

SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname||' - '||partner_lastname
  5         when 'PO' then partner_lastname||' - '||lastname
  6         end full_lastname
  7    from names
  8  /

FULL_LASTNAME
------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen

Looks good sofar. But,... there is always a but... The data is not always up to par. Some records have "inappropriate" name_usage indicators:

insert into names values ('Jansen', null, 'O') -- Own
/
insert into names values ('Jansen', null, 'OP') -- Own - Partner
/
insert into names values ('Jansen', null, 'PO') -- Partner - Own
/

I left out the Partner usage when there is no Partner - this would just be a "regular" NVL.

When we run the query we used before, we will get this output

FULL_LASTNAME
------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen -
 - Jansen

7 rows selected.

The last two look strange, don't you agree? There are hyphens there when they shouldn't be.
We could of course write another Case Expression nested inside our already present Case:

SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname||case
  5                                  when partner_lastname is not null
  6                                  then  ' - '||partner_lastname
  7                                  end
  8         when 'PO' then partner_lastname||case
  9                                          when partner_lastname is not null
 10                                          then  ' - '||lastname
 11                                          end
 12         end full_lastname
 13    from names
 14  /

FULL_LASTNAME
-------------------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen


7 rows selected.

As you can see the SQL statement becomes quite bulky, but very easy to understand. It's also possible to use NVL2 to do the same

SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname ||nvl2 (partner_lastname, ' - ', null)||partner_lastname
  5         when 'PO' then partner_lastname ||nvl2 (partner_lastname, ' - ', null)||lastname
  6         end full_lastname
  7    from names
  8  /

FULL_LASTNAME
-------------------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen
Jansen

7 rows selected.

Nice.

Of course the same thing can be achieved using the good ol' DECODE:

SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname ||decode (partner_lastname
  5                                          , null, null, ' - ')||partner_lastname
  6         when 'PO' then partner_lastname ||decode (partner_lastname
  7                                                  , null, null, ' - ')||lastname
  8         end full_lastname
  9    from names
 10  /

FULL_LASTNAME
-------------------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen
Jansen

7 rows selected.

Second Use Case: Implementing an Arc
To implement an Arc relation in the database, the NVL2 function could also be useful.
Let's start with the model

drop table t3
/
drop table t1
/
drop table t2
/

create table t1
(c1 int primary key)
/

create table t2
(c1 int primary key)
/

create table t3
(c1 int primary key
,t1_c1 int references t1
,t2_c1 int references t2
)
/

The Arc should be implemented on the T3 table. Either the relation to T1 should be filled or the relation to T2 should be filled. They should not both be filled for the same record.
This can be implemented with a Case Expression in a Check Constraint

SQL> alter table t3
  2  add constraint arc_chk check (case 
  3                                when t1_c1 is not null 
  4                                 and t2_c1 is null
  5                                then 1
  6                                when t1_c1 is null
  7                                 and t2_c1 is not null
  8                                then 1
  9                                else 0
 10                                end = 1
 11                                )
 12  /

Table altered.

The expression in the Check Constraint is

the value of the Case Expression (either "1" or "0") must be equal to "1"

And to test our Arc implementation:

SQL> insert into t1 values (1)
  2  /

1 row created.

SQL> 
SQL> insert into t2 values (2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (10, 1, null)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (11, null, 2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (12, 1, 2)
  2  /
insert into t3 values (12, 1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated


SQL> 
SQL> insert into t3 values (13, null, null)
  2  /
insert into t3 values (13, null, null)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated

This seems to work, When either relation is filled, all is well. When both relations are filled, or none of the relations is, the Check Constraint is violated.

Now let's do the same thing with NVL2.

SQL> rollback
  2  /

Rollback complete.

SQL> 
SQL> alter table t3
  2  drop constraint arc_chk
  3  /

Table altered.

SQL> 
SQL> 
SQL> alter table t3
  2  add constraint arc_chk check (nvl2 (t1_c1, 1, 0)
  3                                + nvl2 (t2_c1, 1, 0)
  4                                = 1)
  5  /

Table altered.

The Check Constraint is a lot shorter, but does the same thing.

SQL> 
SQL> insert into t1 values (1)
  2  /

1 row created.

SQL> 
SQL> insert into t2 values (2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (10, 1, null)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (11, null, 2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (12, 1, 2)
  2  /
insert into t3 values (12, 1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated


SQL> 
SQL> insert into t3 values (13, null, null)
  2  /
insert into t3 values (13, null, null)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated

The NVL2 function can also take an expression as the first argument. Just for completeness I have taken this example from the documentation:

SELECT last_name, salary, NVL2(commission_pct, 
   salary + (salary * commission_pct), salary) income
   FROM employees WHERE last_name like 'B%'
   ORDER BY last_name;

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6882
Bates                           7300       8468
Bell                            4000       4000
Bernstein                       9500      11970
Bissot                          3300       3300
Bloom                          10000      12100
Bull                            4100       4100

And there you have it, two use cases - and an example from the docs - for the NVL2 function.

What would be a reason not to use the NVL2 function? One reason would be that the function is quite hard to read (comparable to interpreting DECODE). Another would be that this function is not very well known.
The Use Cases described above are the cases where I use the NVL2 function. Simply because it's so concise. Matter of preference I think.
Do you use it? And how - for which use cases - do you use it?