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.
SELECT NULLIFZERO(<column-name>) from <table-name>
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)