Stop Looping: Use COALESCE in ABAP on HANA to Push Null Handling to the Database

Giulia Ferrari breaks down what you need to know

I still review ABAP code that loops over internal tables just to replace NULL values with a default string or a zero. On SAP HANA, that’s a missed opportunity—and often a measurable one. The database can handle this for you, faster, cleaner, and with far less code. Let’s talk about COALESCE as part of your SQL pushdown toolkit.

The Real Story

For years, ABAP developers learned to process data procedurally: select into an internal table, loop, modify, and only then display or send the data. That made sense when the database was just a storage layer. On SAP HANA, the database is a parallel processing engine. Every row you pull into application memory and manipulate in ABAP costs CPU cycles, memory, and time—especially with large data volumes.

COALESCE is a SQL function that returns the first non-null argument. Instead of doing this in ABAP:

SELECT amount, customer_name
  FROM zsales
  INTO TABLE @DATA(lt_sales).

LOOP AT lt_sales ASSIGNING FIELD-SYMBOL(<fs_sales>).
  IF <fs_sales>-amount IS NULL.
    <fs_sales>-amount = 0.
  ENDIF.
  IF <fs_sales>-customer_name IS NULL.
    <fs_sales>-customer_name = 'Unknown'.
  ENDIF.
ENDLOOP.

You can do this directly in Open SQL (or better, in a CDS view):

SELECT COALESCE( amount, 0 ) AS amount,
       COALESCE( customer_name, 'Unknown' ) AS customer_name
  FROM zsales
  INTO TABLE @DATA(lt_sales).

No loop. No procedural null handling. The database applies the transformation while reading, often during the scan itself. The result set arrives in ABAP already cleaned.

This isn’t just about one function. It’s a shift from row-by-row application logic to set-based database logic. On SAP HANA, that shift is where the real performance gains live.

What This Means for You

For developers: You’ll write less code, and the code you write will be more declarative. COALESCE in a SELECT or a CDS view tells the system what you want, not how to do it. That means fewer chances for off-by-one errors, less memory used by oversized internal tables, and fewer lines to maintain.

For architects: Every small pushdown like this compounds. If you eliminate ten loops that each handle nulls differently, you reduce application-layer processing, lower data transfer between HANA and the ABAP application server, and simplify your codebase. This aligns with the core principle of ABAP on HANA: push down what the database can do better.

For consultants: When you see performance issues in custom code, check whether the ABAP layer is doing work that a single SQL expression could handle. COALESCE is a quick win—but don’t stop there. Look for CASE, string functions, and aggregation that can be moved into Open SQL or CDS views.

One important caveat: Open SQL in ABAP supports COALESCE since ABAP 7.50, but not every expression is available in every release. If you’re on an older system or need to reuse the logic across multiple programs, a CDS view is often the better place to implement it. CDS views also let you expose the cleaned data directly to OData or other consumers.

Action Items

• Audit your loops: Search for LOOP AT blocks that only replace NULL or empty values with defaults. These are prime candidates for COALESCE.
• Rewrite using SQL expressions: Replace the loop with a SELECT that includes COALESCE( field, default ). Prefer CDS views if the logic is reused or if Open SQL restrictions block you.
• Measure before and after: Use GET RUN TIME or transaction SAT to compare runtime and memory. For large tables, you should see a notable improvement because the data transfer is smaller and application processing is eliminated.
• Adopt a pushdown mindset: Whenever you write ABAP that processes internal tables row-by-row, ask: “Can the database do this in a single SQL statement?” If yes, move it there.

Community Perspective

Some developers worry that pushing logic to SQL makes debugging harder or hides business rules in places that aren’t obvious. That’s a fair concern—but the solution isn’t to keep everything in ABAP. It’s to document and centralize pushdown logic in CDS views or reusable SQL expressions. Treat the database layer as part of your architecture, not a black box. Others have noted that COALESCE only handles nulls, not empty strings or other falsy values. That’s true; if you need more complex cleanup, you may still need a CASE expression in SQL or, in rare cases, a loop. But for simple null-to-default conversion, COALESCE is exactly the right tool.

Bottom Line

If you’re still looping over internal tables to fix nulls on SAP HANA, you’re leaving performance on the table. COALESCE is a small, elegant piece of the SQL pushdown puzzle—but it’s a pattern that pays dividends when applied consistently. The database is far better at set-based operations than any ABAP loop. Use it. Test it. And then look for the next pushdown opportunity.

Source: Original discussion/article

References

• SAP HANA Platform Overview- ABAP Development Guide

References

• ABAP Development Guide
• SAP HANA Platform Overview
• Optimize ABAP with HANA: Avoid Loops …