The Vertica Database Forums

Posted: **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!

The Vertica Database Forums

The NVL2 Function

The NVL2 Function