ZEROIFNULL

Evaluates to 0 if the column is NULL.

Evaluates to 0 if the column is NULL.

Syntax

ZEROIFNULL(expression)

Parameters

expression
String to evaluate for NULL values, one of the following data types:

  • INTEGER

  • DOUBLE PRECISION

  • INTERVAL

  • NUMERIC

Examples

The following query returns scores for five students from table test_results, where Score is set to 0 for L. White, and null for S. Robinson and K. Johnson:

=> SELECT Name, Score FROM test_results;
    Name     | Score
-------------+-------
 J. Doe      |   100
 R. Smith    |    87
 L. White    |     0
 S. Robinson |
 K. Johnson  |
(5 rows)

The next query invokes ZEROIFNULL on column Score, so Vertica returns 0 for for S. Robinson and K. Johnson:

=> SELECT Name, ZEROIFNULL (Score) FROM test_results;
    Name     | ZEROIFNULL
-------------+------------
 J. Doe      |        100
 R. Smith    |         87
 L. White    |          0
 S. Robinson |          0
 K. Johnson  |          0
(5 rows)

You can also use ZEROIFNULL in PARTITION BY expressions, which must always resolve to a non-null value. For example:

CREATE TABLE t1 (a int, b int) PARTITION BY (ZEROIFNULL(a));
CREATE TABLE

Vertica invokes this function when it partitions table t1, typically during a load operation. During the load, the function checks the data of the PARTITION BY expression—in this case, column a—for null values. If encounters a null value in a given row, it sets the partition key to 0, instead of returning with an error.