15.00 - NULLIFZERO - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

NULLIFZERO

Purpose  

Converts data from zero to null to avoid problems with division by zero.

Syntax  

where:

 

Syntax element …

Specifies …

arg

a numeric argument, or an argument that can be converted to a numeric argument based on implicit type conversion rules.

ANSI Compliance

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

The ANSI form of this function is the CASE shorthand expression NULLIF. For more information, see “NULLIF Expression” on page 581.

Result Type and Attributes

Here are the default attributes for the result of NULLIFZERO.

  • If arg is numeric, the data type is the same types as arg and the format is the same format as arg.
  • If arg is character, the data type is FLOAT and the default format is FLOAT.
  • If arg is a UDT, the data type is the type of which the UDT is implicitly cast and the format is the format of the data type to which the UDF 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.

    Result Value

     

    IF the value of arg is …

    THEN NULLIFZERO returns …

    nonzero

    the value of the numeric argument

    null or zero

    NULL

    Argument Types and Rules

    If arg is not numeric, it is converted to a numeric value, based on 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 NULLIFZERO, 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).
  • NULLIFZERO 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  

    The following expressions return an error if the value of x or expression is zero.

       6 / x
       6 / expression

    On the other hand, the following expressions return null, which is not an error because there is no violation of the divide by zero rule.

       6 / NULLIFZERO(x)
       6 / NULLIFZERO(expression)

    Example  

    The following request returns a null in the second column because the HCap field value for Newman is zero. In BTEQ (field mode) this appears as a ‘?’.

       SELECT empno, NULLIFZERO(hcap) 
       FROM employee 
       WHERE empno = 10019 ; 

    Related Topics

    For additional expressions involving checks for nulls, see:

  • “COALESCE Expression” on page 579
  • “NULLIF Expression” on page 581
  • “ZEROIFNULL” on page 180