Compatibility: Teradata Extension

The original Teradata database software also allowed the user to compare for a NULL value. Earlier in this book we saw IS NULL and IS NOT NULL used within the WHERE clause. An additional test is available with the ZEROIFNULL function.

The purpose of this function is to compare the data value in a column and when it contains a NULL, transform it, for the life of the SQL statement, to a zero.

The syntax for the ZEROIFNULL follows:

Here, literals are used to demonstrate the operation of the ZEROIFNULL function:

1 Row ReturnedZEROIFNULL function

In the above SQL and its output:

  • Col1 the value 0 is not a NULL, so the 0 is returned.
  • Col2, the NULL is a NULL, so a 0 is returned.
  • Col3, the 3 is not a NULL, so the 3 is returned.

The best use of the ZEROIFNULL is in a mathematics formula. Earlier it was seen that anytime a NULL is used in math, the answer is a NULL. Therefore, the ZEROIFNULL can convert a NULL to a zero so that an answer is returned.

The next SELECT shows what happens when a zero ends up in the calculation and then, how to avoid it using the ZEROIFNULL:

5 Rows Returned


To fix the problem of the NULL appearing:

10 Rows Returnedfix the problem of the NULL appearing

The following are the same examples seen earlier in this chapter for NULLIFZERO. They are used here to show the contrast:

5 Rows Returned


Then again with an alias on the ZEROIFNULL:

5 Rows ReturnedZEROIFNULL

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

Teradata Topics