NULLIFZERO Function in Teradata

NULLIFZERO is a one of the useful function in Teradata. It is used to validate the values in the column.

If the column contains zero as a value,It will change the value from zero to NULL. Mostly this function used to avoid the divide by zero issues.

Syntax

SELECT NULLIFZERO(<column-name>) from <table-name>

Example 1

SELECT bill_amount / NULLIFZERO(total_users) from expense_report;

Values : bill_amount = 800 and total users = 0

Output : NULL

In this case, the total_users column might have zero or non-zero values. For example,If it contains zero,the NULLIFZERO function change the total_users to NULL . So the bill_amount divide by NULL and the output returns as NULL.

Example 2(without NULLIFZERO)

SELECT bill_amount / total_users from expense_report;

Values : bill_amount = 800 and total_users = 0

Output : Divide by zero exception error (Since the bill_amount divide by zero,it throws this error)

Recommended Articles

• How to select the NULL values in Teradata?