Skip to content

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


... COALESCE( sql_exp1, sql_exp2, ..., sql_expn ) ...


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.


  • 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.
CASE WHEN sql_exp1 IS NOT NULL THEN sql_exp1
     WHEN sql_exp2 IS NOT NULL THEN sql_exp2
     ELSE sql_expn


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' ) ). 

       FROM demo_expressions 
                        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


SQL Expressions, Coalesce Function