Raymond
Teradata NULLIFZERO and ZEROIFNULL Function
insights Stats
warning Please login first to view stats information.
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;
copyright
This page is subject to Site terms.
comment Comments
No comments yet.