ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Operands and Expressions → ABAP SQL - SQL Expressions sql_exp → sql_exp - sql_func → ABAP SQL - Built-In Functions sql_func → ABAP SQL - SQL Functions
sql_exp - sql_coalesce
Other versions:
7.31 | 7.40 | 7.54
Syntax
... COALESCE( sql_exp1, sql_exp2, ..., sql_expn ) ...
Effect
Calls the Coalesce function as an SQL expression or operand
of an expression in ABAP SQL. The Coalesce function can have a comma-separated list with at least two
but no more than 255 arguments sql_exp1, sql_exp2, ..., sql_expn. This function returns the values of the first argument, which does not have the
null value. If every argument
has the null value, the value of the last argument sql_expn is returned. A blank must be placed after the opening parenthesis and before the closing parenthesis.
The arguments can be any SQL expressions of all dictionary types except ACCP, DF16_SCL (obsolete), DF34_SCL (obsolete), LCHR, LRAW, PREC, RAWSTRING, STRING, and GEOM_EWKB. The data types of arguments must either be the same or the data type of one argument must represent the value of the other data types. The result has the dictionary type of the argument with the largest value range.
Notes
- The Coalesce function can be used to assign a target object a self-defined value or the result of an expression, when null values are read. The type-dependent initial value is assigned by default.
- A maximum of 10 Coalesce functions can be nested.
- A Coalesce function is a short from of the following complex case distinction:
WHEN sql_exp2 IS NOT NULL THEN sql_exp2
...
ELSE sql_expn
END
- The Coalesce function can be executed in the table buffer. Table buffering is not bypassed when the Coalesce function is used.
Example
The first three CASE expressions
return the null value, since no WHEN condition is met and no ELSE
is specified. The result of the fourth CASE expression is not a null value and is returned by COALESCE.
DELETE FROM demo_expressions.
INSERT demo_expressions FROM @( VALUE #( id = 'X' ) ).
SELECT SINGLE
FROM demo_expressions
FIELDS COALESCE( CASE id WHEN 'A' THEN 'a' END,
CASE id WHEN 'B' THEN 'b' END,
CASE id WHEN 'B' THEN 'b' END,
CASE id WHEN 'X' THEN 'x' END ) AS coalesced
INTO @DATA(result).
Executable Example
SQL Expressions, Coalesce Function