15.00 - ZEROIFNULL - Teradata Database

ZEROIFNULL

Purpose  

Converts data from null to 0 to avoid cases where a null result creates an error.

Syntax  

where:

Syntax element …	Specifies …
arg	a numeric argument.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Result Type and Attributes

Here are the default attributes for the result of ZEROIFNULL.

If the operand is numeric, the format is the same format as arg.

If the operand is character, the format is the default format for FLOAT.

If the operand is a UDT, the format is the format of the predefined type to which the UDT is implicitly cast.

Note: The NULL keyword has a data type of INTEGER.

For information on data type formats, see SQL Data Types and Literals.

Argument Types and Rules

IF the value of arg is …	THEN ZEROIFNULL returns …
not null	the value of the numeric argument.
null or zero Note: A structured UDT column value is null only when you explicitly place a NULL in the column, not when a structured UDT instance has an attribute that is set to NULL.	zero.

If the argument is not numeric, it is converted to a numeric value according to implicit type conversion rules. If the argument cannot be converted, an error is reported. For more information on implicit type conversion, see “Implicit Type Conversions” on page 583.

If arg is a character string, it is converted to a numeric value of FLOAT data type.

If arg is a UDT, the following rules apply:

The UDT must have an implicit cast to any of the following predefined types:

Numeric

Character

DATE

Interval

To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

Implicit type conversion of UDTs for system operators and functions, including ZEROIFNULL, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

ZEROIFNULL cannot be applied to the following types of arguments:

BYTE or VARBYTE

BLOB or CLOB

CHARACTER or VARCHAR if the server character set is GRAPHIC

Example  

In this example, you can test the Salary column for null.

   SELECT empno, ZEROIFNULL(salary) 

   FROM employee ; 

A nonzero value is returned for each employee number, indicating that no nulls exist in the Salary column.

Related Topics

For additional expressions involving checks for nulls, see:

“COALESCE Expression” on page 579

“NULLIF Expression” on page 581

“NULLIFZERO” on page 144