CASE expression with COALESCE

A CASE expression with COALESCE has the following syntax:

---

case_expression ::= COALESCE ( expression1 , expression2 , ..., expressionn )

---

expression

Expression that returns an alphanumeric, national, numeric or time value when evaluated.
It cannot be a multiple value with a dimension greater than 1.

Result

The result of the CASE expression is NULL if all the expressions contained in the parentheses (expression1... expressionn) return NULL. If at least one expression returns a value other than the NULL value, the result of the CASE expression is the value of the first expression that does not return the NULL value.

The CASE expression COALESCE (expression1,expression2)  corresponds to a CASE expression with a search condition of the following form:

CASE 
   WHEN expression1 IS NOT NULL THEN expression1 
   ELSE expression2 
END

The CASE expression COALESCE (expression1,expression2,...,expressionn) corresponds to the following CASE expression with a search condition:

CASE 
   WHEN expression1 IS NOT NULL THEN expression1 
   ELSE COALESCE (expression2 ...,expressionn)
END

Examples

A list of contacts is to be created for specific customer contacts. In addition to the title, last name, telephone number and position, either the department or, if this is not known, the reason for the previous contact is to be determined.

SELECT title, lname, contact_tel, position,

COALESCE(department, contact_info) AS info FROM contacts WHERE contact_num < 30

Derived table

title	lname	contact_tel	position	info
Dr.	Kuehne	089/6361896	CEO	Personnel
Mr.	Walkers	089/63640182	Secretary	Sales
Mr.	Loetzerich	089/4488870	Manager	Networks
Mr.	Schmidt	0551/123873	Training
Ms.	Kredler	089/923764	Organization	SQL course

After the title, last name, telephone number and function, the department of the customer is determined. If this information is missing (NULL), the column value for the CONTACT_INFO column is determined for INFO. If both the DEPARTMENT and CONTACT_INFO columns contain NULL, INFO will also contain NULL.

A list of order completion dates is to be generated from the ORDERS table. The list is to contain the date when the order was made, the order description and its completion date. If the actual completion date is not known, the target completion date is to be entered.

SELECT order_date, order_text, 
   COALESCE (actual, target) AS completion_date FROM orders 
   order_date     order_text                completion_date
   <date>         Staff training             <date>
   <date>         Customer administration    <date>
   <date>         Database design customers  <date>
   <date>         Mailmerge intro            <date>
   <date>         Customer administration    <date>
   <date>         Network test/ comparison
   <date>         Staff training             <date>

To determine the values for COMPLETION_DATE, the ACTUAL column is evaluated. If there is a date in the column, this is accepted. If ACTUAL contains the NULL value, the corresponding column value in the TARGET column is determined and entered in the COMPLETION_DATE column. If both ACTUAL and TARGET contain the NULL value, the NULL value is entered in the COMPLETION_DATE column.