Return Zero Instead of NULL
Jim_Knicely
- Select Field - Administrator
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