Teradata NULLIFZERO and ZEROIFNULL Function

visibility 257 comment 0 access_time 12m 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

Tags
More from Kontext
visibility 169
thumb_up 0
access_time 9m