8.13.3 NULLIF

(1) Specification format

scalar-function-NULLIF ::= NULLIF(target-data-1,target-data-2)
 
  target-data-1 ::= value-expression
  target-data-2 ::= value-expression

To Page Top

(2) Explanation of specification format

target-data-1, target-data-2:

Specifies the target data to be compared.

The following rules apply:

• Specify target-data-1 and target-data-2 in the form of value expressions. For details about value expressions, see 7.20 Value expression.

• In target-data-1 and target-data-2, specify data whose data types can be compared. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared. However, note the following exceptions:

  • DATE type data cannot be compared to character string data (even to the predefined input representation of a date).

  • TIME type data cannot be compared to character string data (even to the predefined input representation of a time).

  • TIMESTAMP type data cannot be compared to character string data (even to the predefined input representation of a time stamp).

• You cannot specify dynamic parameters by themselves for both target-data-1 and target-data-2.

• If you specify a dynamic parameter for either target-data-1 or target-data-2, the data type of the other one will be assumed to be the data type of the dynamic parameter.

To Page Top

(3) Rules

1. The data type and data length of the execution result are determined according to the rules described in 7.20.2 Data types of the results of value expressions.

2. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

3. If target-data-1 has a null value, the execution result will be a null value.

4. NULLIF(target-data-1,target-data-2) is equivalent to the following CASE expression.

   CASE
      WHEN target-data-1 = target-data-2 THEN NULL
      ELSE target-data-1
   END

To Page Top

(4) Example

Example:

Compare the values of columns C1 and C2 in table T1.

SELECT NULLIF("C1","C2") FROM "T1"

To Page Top