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
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)