Return Zero Instead of NULL

Jim_Knicely

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 valid data type for the function!

dbadmin=> SELECT zeroifnull(NULL::INT);
zeroifnull
------------
          0
(1 row)

Helpful links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Null/ZEROIFNULL.htm

Have fun!