| 1 |
SQL Language Extension: COALESCE |
| 2 |
|
| 3 |
Function: |
| 4 |
Allow a column value to be calculated by a number of expressions, |
| 5 |
the first expression returning a non NULL value is returned as the |
| 6 |
column value |
| 7 |
|
| 8 |
Author: |
| 9 |
Arno Brinkman <[email protected]> |
| 10 |
|
| 11 |
Format: |
| 12 |
|
| 13 |
<case abbreviation> ::= |
| 14 |
| COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren> |
| 15 |
|
| 16 |
|
| 17 |
Syntax Rules: |
| 18 |
1) COALESCE (V1, V2) is equivalent to the following <case specification>: |
| 19 |
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END |
| 20 |
2) COALESCE (V1, V2,..., Vn), for n >= 3, is equivalent to the following |
| 21 |
<case specification>: |
| 22 |
CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2,...,Vn) END |
| 23 |
|
| 24 |
Notes: |
| 25 |
See also README.data_type_results_of_aggregations.txt |
| 26 |
|
| 27 |
Examples: |
| 28 |
A) |
| 29 |
|
| 30 |
SELECT |
| 31 |
PROJ_NAME AS Projectname, |
| 32 |
COALESCE(e.FULL_NAME,'[> not assigned <]') AS Employeename |
| 33 |
FROM |
| 34 |
PROJECT p |
| 35 |
LEFT JOIN EMPLOYEE e ON (e.EMP_NO = p.TEAM_LEADER) |
| 36 |
|
| 37 |
B) |
| 38 |
SELECT |
| 39 |
COALESCE(Phone,MobilePhone,'Unknown') AS "Phonenumber" |
| 40 |
FROM |
| 41 |
Relations |
| 42 |
|