Raymond Raymond / Code Snippets & Tips

Teradata NULLIFZERO and ZEROIFNULL Function

event 2020-12-26 visibility 1,657 comment 0 insights toc
more_vert
insights Stats
toc Table of contents

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;
More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts