21 July 2009

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?

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. NVL and NVL2 also for that matter both evaluate all parameters passed to them.
    That could lead to performance issues or even errors when one uses a function that can not handle a null value.

    This one errors when it logically it shouldn't:
    select
    nvl('1', to_char(rawtohex(dbms_obfuscation_toolkit.md5(input_string=> null ))) )
    from dual;

    Using DECODE or CASE will not trigger this since they short-circuit or simply put just don't evaluate the functions used here.

    ReplyDelete
    Replies
    1. Unfortunately a DECODE or CASE will also give an error in your example:
      Connected to:
      Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

      SQL> select case when '1' is not null then '1' else
      2 to_char(rawtohex(dbms_obfuscation_toolkit.md5(null)))
      3 end
      4 from dual;
      to_char(rawtohex(dbms_obfuscation_toolkit.md5(null)))
      *
      ERROR at line 2:
      ORA-06553: PLS-307: too many declarations of 'MD5' match this call


      SQL>
      SQL> select decode ('1', null, to_char(rawtohex(dbms_obfuscation_toolkit.md5(null))), '1')
      2 from dual;
      select decode ('1', null, to_char(rawtohex(dbms_obfuscation_toolkit.md5(null))), '1')
      *
      ERROR at line 1:
      ORA-06553: PLS-307: too many declarations of 'MD5' match this call

      Delete
    2. I spoke too soon... and misread the error... that's what you get if you test on a different version of the database. I noticed you used the named notation in SQL (which is added in Oracle 11g).
      Rerunning my scripts on Oracle 11.2.0.1.0 yields these results:
      SQL> select case when '1' is not null then '1' else
      2 to_char(rawtohex(dbms_obfuscation_toolkit.md5(input => null)))
      3 end
      4 from dual;

      CASEWHEN'1'ISNOTNULLTHEN'1'ELSETO_CHAR(RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT=>NULL)))END
      ----------------------------------------------------------------------------------------------------

      1

      SQL>
      SQL> select decode ('1', null, to_char(rawtohex(dbms_obfuscation_toolkit.md5(input => null))), '1')
      2 from dual;

      DECODE('1',NULL,TO_CHAR(RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT=>NULL))),'1')
      ----------------------------------------------------------------------------------------------------

      1

      SQL>
      SQL> select nvl('1', to_char(rawtohex(dbms_obfuscation_toolkit.md5(input => null ))) )
      2 from dual;
      select nvl('1', to_char(rawtohex(dbms_obfuscation_toolkit.md5(input => null ))) )
      *
      ERROR at line 1:
      ORA-28231: no data passed to obfuscation toolkit
      ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 109
      ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 291


      SQL> select *
      2 from v$version
      3 /

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE 11.2.0.1.0 Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production

      Delete
  3. Today I used your Arc implementation with the NVL2 function!

    Thanks!

    Hans Boute

    ReplyDelete