Teradata NULLIFZERO and ZEROIFNULL Function

access_time 22 days ago visibility7 comment 0

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.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 602
thumb_up 0
access_time 5 months ago

As other databases, Teradata provides many aggregation functions that can be used to retrieve SUM, COUNT, MAX, MIN and AVG values. These functions returns a single value per group. To retrieve the whole first row from each 'GROUP BY' group, windowing functions can be used.  The following code ...

visibility 463
thumb_up 0
access_time 9 months ago

In SQL Server, we can use TRUNCATE statement to clear all the records in a table and it usually performs better compared with DELETE statements as no transaction log for each individual row deletion. The syntax looks like the following: TRUNCATE TABLE { database_name.schema_name.table_name | ...

visibility 494
thumb_up 0
access_time 5 months ago

Teradata Parallel Transporter (TPT) provides rich functions to load data into Teradata and to export data. In article Load CSV into Teradata via TPT , it shows how to load CSV files into Teradata. This page provides examples to export data from Teradata to CSV. Use TPT wizard GUI tool to create a ...