Teradata NULLIFZERO and ZEROIFNULL Function

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;