Return Zero Instead of NULL: Quick Tip

Posted October 1, 2018 by Phil Molea, Sr. Information Developer, Vertica

Business Team Meeting Discussion Working Concept
Jim Knicely authored this post.

The built-in Vertica function ZEROIFNULL evaluates to 0 for NULL values in a table’s column.

Example: dbadmin=> CREATE TABLE zin (C INT); CREATE TABLE dbadmin=> INSERT INTO zin SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO zin SELECT NULL; OUTPUT -------- 1 (1 row) dbadmin=> SELECT c, zeroifnull(c) zin FROM zin; c | zin ---+----- 1 | 1 | 0 (2 rows) dbadmin=> SELECT zeroifnull(NULL); ERROR 3459: Function zeroifnull(unknown) is not unique HINT: Could not choose a best candidate function. You may need to add explicit type casts Whoa! What happened in that last statement? Since NULL by itself does not have a data type, you’ll need to cast it to a valid data type for the function! dbadmin=> SELECT zeroifnull(NULL::INT); zeroifnull ------------ 0 (1 row) Helpful link:

Have fun!