Compatibility: Teradata Extension

The Teradata database software, for many years, has provided the user the ability to test for zero using the original NULLIFZERO function. The purpose of this function was to compare the data value in a column for a zero and when found, convert the zero, for the life of the SQL statement, to a NULL value.

The following syntax shows two different uses of the NULLIFZERO function, first on a column and then within an aggregate:

The next SELECT uses literal values to demonstrate the functionality of the NULLIFZERO:

1 Row Returneddifferent uses of the NULLIFZERO function

In the above SQL and its output:

  • ol1 the value 0 is equal to 0, so a NULL is returned.
  • Col2, the NULL is not equal to 0, so the NULL is returned.
  • Col3, the 3 is not equal to 0, so the 3 is returned.

The next SELECT shows what happens when a zero ends up in the denominator of a division request and then, how to avoid it using the NULLIFZERO in division:

To fix the 2619 error, this next technique might be used:

10 Rows Returned

next technique might be used
next technique might be used

This can be a lifesaver when a zero will cause an incorrect result or cause the SQL statement to terminate in an error such as dividing by zero. Therefore, it is common to use NULLIFZERO for the divisor when doing a division operation.

Although dividing by NULL returns a NULL, the SQL continues to execute and returns all the values for rows containing good data. At the same time, it also helps to identify the row or rows that need to be fixed.

The next two examples show a different answer when the NULLIFZERO function is used inside an aggregate function to eliminate the ZERO:

First without NULLIFZERO:

5 Rows ReturnedFirst without NULLIFZERO


5 Rows ReturnedNULLIFZERO

As seen in the above answer sets, the zero GPA value came out as is in the first one. Then, the NULLIFZERO converts it to a NULL in the second with FR being higher.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics