access_time 8 months ago languageEnglish
more_vert

Teradata NULLIFZERO and ZEROIFNULL Function

visibility 134 comment 0

There are two NULLs related Teradata extension to ANSI SQL functions - NULLIFZERO and ZEROIFNULL.

NULLIFZERO

This function converts zero to NULL and it is commonly used to avoid error like divide by zeros.

SELECT 100/NULLIFZERO(0);

Above query returns NULL. And the following query will get one error: [2618] Invalid calculation: division by zero.

SELECT 100/0;

ZEROIFNULL

ZEROIFNULL function converts NULL values to 0 and it is commonly used in the following scenarios:

  • Avoid errors if NULL can cause problems.
  • Returns zeros in analytical results. For example, return 0 instead NULL to calculate record count, SUM of values, etc.
SELECT ZEROIFNULL(NULL);
SELECT ZEROIFNULL(SUM(CASE WHEN COL1='A' THEN COL2 END)) FROM TABLE1;
copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext