COALESCE Expression in ABAP CDS View Entities

Among all built-in CDS expressions, COALESCE is one of the most misunderstood. Many developers assume it behaves like the SQL COALESCE function that replaces any empty or missing value.

In ABAP CDS, the reality is quite different.

Most database fields never become NULL. Instead, they usually contain an initial value such as 0, an empty string, or 00000000.

COALESCE only replaces values that are truly NULL. Understanding where NULL comes from is therefore far more important than memorizing the COALESCE syntax itself.

By the end of this lesson, you'll understand what NULL really means, where it originates in ABAP CDS, why associations play such an important role, and when COALESCE should—or should not—be used in production CDS View Entities.

Before learning COALESCE, it's essential to understand what NULL actually represents.

NULL does not mean zero, blank, space, or an initial value.

NULL means:

There is no value at all.

The value simply does not exist.

One of the biggest misconceptions among ABAP developers is treating NULL as another initial value.

Classical ABAP does not support NULL values for normal variables.

DATA lv_amount TYPE p.

If no value is assigned, the variable automatically receives its initial value.

0

This is one of the reasons many experienced ABAP developers are surprised when they first encounter NULL handling in CDS View Entities.

In modern S/4HANA Public Cloud development, NULL values rarely originate from the primary data source.

Instead, they most commonly appear when CDS follows an optional relationship.

The COALESCE expression returns the first expression if it contains a value. If that expression evaluates to NULL, COALESCE returns the alternative value provided as the second argument.

coalesce( expression1, expression2 )

The execution logic of COALESCE is very straightforward.

Is expression1 NULL?
        │
   ┌────┴────┐
   │         │
  Yes       No
   │         │
Return      Return
expression2 expression1

coalesce(
    _Product.ProductName,
    'Unknown Product'
)

Modern ABAP Cloud development strongly encourages the use of associations instead of explicit LEFT OUTER JOIN statements in CDS View Entities.

Consequently, most NULL values encountered in production CDS Views originate from optional associations rather than the primary data source.

association [0..1] to I_Product as _Product
    on $projection.Material = _Product.Product

The cardinality [0..1] means that a related Product record may or may not exist.

If no Product is found, every field exposed through _Product evaluates to NULL.

This is one of the most common production scenarios in SAP S/4HANA Public Cloud.

define view entity ZI_PRODUCT_INFO
  as select from I_SalesDocumentItem

  association [0..1] to I_Product as _Product
      on $projection.Material = _Product.Product
{
    key SalesDocument,

    Material,

    coalesce(
        _Product.ProductName,
        'Unknown Product'
    ) as ProductName
}

association [0..1] to I_Customer as _Customer
    on $projection.SoldToParty = _Customer.Customer

coalesce(
    _Customer.CustomerName,
    'Customer Deleted'
) as CustomerName

This pattern is frequently used in reporting applications where historical documents remain available even after the associated customer master data has been removed or is no longer accessible.

association [0..1] to I_Supplier as _Supplier
    on $projection.Supplier = _Supplier.Supplier

coalesce(
    _Supplier.SupplierName,
    'No Supplier'
) as SupplierName

Returning a meaningful default value makes analytical reports easier to understand than exposing NULL values directly to business users.

Although the COALESCE expression is simple, it is frequently misunderstood. Most mistakes occur because developers assume it replaces initial values instead of NULL values.

One of the most common mistakes is applying COALESCE to amount or quantity fields coming directly from the primary data source.

coalesce(
    NetAmount,
    0
) as NetAmount

In most CDS View Entities, NetAmount is a field from the base data source. If no value exists, it usually contains its initial value rather than NULL.

coalesce(
    Material,
    'N/A'
) as Material

Since Material originates from the primary data source, it typically contains either a valid value or its initial value—not NULL.

In this situation, COALESCE will almost never change the returned result.

Although both expressions can return alternative values, they solve different problems.

case
    when NetAmount = 0 then 'Free'
    else 'Paid'
end

coalesce(
    _Product.ProductName,
    'Unknown Product'
)

COALESCE can also be used together with aggregate functions.

coalesce(
    sum( NetAmount ),
    0
) as TotalAmount

However, there is an important detail that experienced CDS developers should understand.

COALESCE is executed directly in SAP HANA as part of the SQL statement and fully participates in code pushdown.

In the accompanying video, we'll build practical CDS View Entities using optional associations, observe how NULL values are produced, and learn when COALESCE should—and should not—be used in SAP S/4HANA Public Cloud development.