The NVL2 Function

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

The NVL2 Function

Post by JimKnicely » Fri Aug 10, 2012 12:42 pm

Vertica has a built in function named NVL2 that can be used to simplify your queries when dealing with NULL values.

The NVL2 function accepts three parameters. If the first argument is not NULL, the function returns the second argument, otherwise it returns the third argument.

In MySQL and SQL Server you’d have to use a slightly more complicated CASE statement to get the same results as the NVL2 function…
  • CASE WHEN arg1 IS NOT NULL THEN arg2 ELSE arg3 END
Here is a very simple example using the NVL2 function in Vertica:

Code: Select all

dbadmin=> CREATE TABLE nvl2_test (pk int, test_value varchar(50));
CREATE TABLE
dbadmin=> INSERT INTO nvl2_test VALUES (1, 'Vertica is cool');
OUTPUT
--------
      1
(1 row)

Code: Select all

dbadmin=> INSERT INTO nvl2_test VALUES (2, NULL);
OUTPUT
--------
      1
(1 row)

Code: Select all

dbadmin=> INSERT INTO nvl2_test VALUES (3, '');
OUTPUT
--------
      1
(1 row)

Code: Select all

dbadmin=> SELECT pk,
dbadmin->        test_value,
dbadmin->        NVL(test_value, 'test_value column is NULL'),
dbadmin->        NVL2(test_value, 'test_value column is not NULL', 'test_value column is NULL'),
dbadmin->        CASE WHEN test_value IS NOT NULL THEN 'test_value column is not NULL' ELSE 'test_value column is NULL' END AS "CASE"
dbadmin->   FROM nvl2_test;
pk |   test_value    |            NVL            |             NVL2              |             CASE 
----+-----------------+---------------------------+-------------------------------+-------------------------------
  1 | Vertica is cool | Vertica is cool           | test_value column is not NULL | test_value column is not NULL
  2 |                 | test_value column is NULL | test_value column is NULL     | test_value column is NULL
  3 |                 |                           | test_value column is not NULL | test_value column is not NULL
(3 rows)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”