Simplify Your Queries with SELECT and FIELDS: Using CASE in Modern ABAP

When working with SAP tables, it’s common to combine data from multiple tables and derive new fields dynamically based on conditions. One of the best features in modern ABAP SQL is the ability to use FIELDS and CASE in a SELECT statement. It allows you to calculate fields directly in the database layer, making your queries cleaner and more efficient.

In this article, let’s break down a practical use case involving the QMEL (Notification Table) and JEST (Status Table) tables, showcasing how to use CASE to assign meaningful user statuses to notification data.

Scenario

You need to fetch a list of quality notifications (QMEL) and their current user statuses (JEST). Each status (STAT) in JEST has a specific meaning:

• E0001: Canceled (CNCL)
• E0003: Under Analysis (ALYS)
• E0004: Closed (CLSD)
• E0005: Waiting (WAIT)
• Anything else: Created (CREA)

Additionally:

• Only active statuses (JEST~INACT ≠ 'X') should be considered.
• The query must filter based on user-defined status values.

Query Breakdown

Here’s the query using FIELDS and CASE:

SELECT
  FROM qmel
  INNER JOIN jest AS jest
     ON jest~objnr = qmel~objnr
    AND jest~inact <> 'X'
    AND jest~stat LIKE 'E%'
  FIELDS
    qmel~qmnum AS notification_number,
    CASE 
      WHEN jest~stat = 'E0001' THEN 'CNCL'
      WHEN jest~stat = 'E0003' THEN 'ALYS'
      WHEN jest~stat = 'E0004' THEN 'CLSD'
      WHEN jest~stat = 'E0005' THEN 'WAIT'
      ELSE 'CREA'
    END AS userstatus
  WHERE qmel~qmart = 'LU'
    AND jest~stat IN @userstatus[]
  INTO TABLE @DATA(result).
        

How It Works

1. Table Joins:
2. Dynamic Field Creation with CASE:
3. Conditions:
4. FIELDS:
5. Result:

Advantages of This Approach

• Performance Boost: The database handles all the heavy lifting (status mapping and filtering), reducing the workload in ABAP.
• Cleaner Code: Using FIELDS eliminates the need for post-processing logic in ABAP.
• Dynamic Columns: The CASE clause allows you to create calculated fields directly in SQL.
• Reduced Data Transfer: Only required columns are fetched, optimizing network performance.

Example Output

Let’s assume the following data:

After running the query, the result will look like this:

Inactive records (like 1000003) are excluded, and statuses are mapped according to the CASE logic.

When to Use This Approach

1. Status Mapping: When translating raw codes into meaningful descriptions.
2. Data Preprocessing: Reduce post-processing in ABAP by pushing logic to the database.
3. Optimized Queries: Minimize data transfer by selecting only relevant columns.

Limitations

• Complex Cases: If the CASE logic gets too complicated, consider using a helper table or CDS view for better maintainability.
• Performance: Avoid using it with very large datasets unless proper indexing is in place.