8.13.1 COALESCE

(1) Specification format

scalar-function-COALESCE ::= COALESCE(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.

The following rules apply:

• Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• In the target data, 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 a dynamic parameter by itself for target-data-1.

• If you specify a dynamic parameter for target-data-2, or later, the data type of the dynamic parameter is assumed to be the data type of target-data-1.

• A maximum of 255 target data items can be specified.

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 all the target data has a null value, the execution result will be a null value.

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

   CASE
      WHEN target-data-1 IS NOT NULL THEN target-data-1
      ELSE target-data-2
   END

5. COALESCE(target-data-1,target-data-2,...,target-data-n) is equivalent to the following CASE expression (where n is greater than or equal to 3).

   CASE
      WHEN target-data-1 IS NOT NULL THEN target-data-1
      ELSE COALESCE(target-data-2,...,target-data-n)
   END

To Page Top

(4) Example

Example:

Execute the scalar function COALESCE on the values of columns C1 to C3 in table T1.

SELECT COALESCE("C1","C2","C3") FROM "T1"

To Page Top