The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Return Zero Instead of NULL

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!
0