Teradata NULLIFZERO and ZEROIFNULL Function

visibility 441 access_time 2 years ago languageEnglish

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.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

timeline Stats
Page index 0.86
More from Kontext
How to fetch data from Teradata database? forum
visibility 100
thumb_up 1
access_time 8 months ago
Save Spark DataFrame to Teradata and Resolve Common Errors
visibility 653
thumb_up 0
access_time 2 years ago