Efficient Data Filtering with Filter Tables in ABAP

In ABAP, the FILTER operator provides a powerful way to reduce internal tables based on a filter table. This enables an elegant and high-performance data filtering approach without explicit loops.

Basic Syntax

... FILTER type( itab {[EXCEPT] IN ftab [USING KEY keyname]}
                    | {[USING KEY keyname] [EXCEPT] IN ftab}
                       WHERE c1 op f1 [AND c2 op f2 [...]] ) ...

This syntax allows filtering an internal table (itab) based on conditions and values from a filter table (ftab).

For complete documentation, refer to the official SAP ABAP documentation: ABAP FILTER Operator – Filtertabelle

How the FILTER Operator Works

🔹 The internal table itab is filtered based on the values of a filter table ftab.
🔹 A comparison is made between the columns of itab and the key columns of ftab.
🔹 The result depends on the use of EXCEPT:

• Without EXCEPT → Only rows from itab that have matching values in ftab are retained.
• With EXCEPT → Only rows from itab that do not have matching values in ftab are retained.

Optimizing Performance with USING KEY

The USING KEY clause allows specifying an explicit key for either itab or ftab to optimize the lookup process. The key can be a sorted key or a hash key:

✅ Using a key for ftab → ftab must have a sorted key or hash key, which is used to efficiently check for matches. There are no special requirements for itab.
✅ Using a key for itab → itab must have a sorted key or hash key, which is used to improve lookup performance. There are no requirements for ftab.
✅ Without USING KEY → ftab must be a sorted table or a hash table, and the primary key is used implicitly. There are no requirements for itab.

🔹 The choice of which table to specify a key for depends on your optimization goals. If the filter table (ftab) is large and frequently accessed, defining a key for ftab can significantly improve performance. On the other hand, if the main table (itab) is large, defining a key for itab will optimize the filtering process. By specifying a key for the table that benefits most from indexing, you can ensure faster lookups and better performance.

Example 1 – Filtering a Table with a Sorted Key on the Filter Table

In this example, we filter a list of customers (customers) using a filter table (filter). Since filter is a standard table without a primary key, we need to use USING KEY:

DATA customers TYPE TABLE OF scustom WITH EMPTY KEY.
DATA filter    TYPE STANDARD TABLE OF scustom-id WITH EMPTY KEY
                WITH UNIQUE SORTED KEY line COMPONENTS table_line.

SELECT * FROM scustom INTO TABLE @customers.

filter = VALUE #( ( '1001' ) ( '1003' ) ( '1005' ) ).

DATA(filtered_customers) = FILTER #(customers IN filter 
                                            USING KEY line
                                            WHERE id = table_line ).

Explanation:

1. Loading customer data into customers from the database table scustom.
2. Defining a filter table (filter) that contains a list of allowed customer IDs.
3. Using FILTER with USING KEY to compare the id field of customers with table_line from filter.

Example 2 – Filtering with a Sorted Key on the Main Table (customers)

Here, we define a sorted key on the main table (customers) instead of the filter table:

DATA customers TYPE SORTED TABLE OF scustom
                WITH UNIQUE KEY id.
DATA filter    TYPE STANDARD TABLE OF scustom-id
                WITH EMPTY KEY.

SELECT * FROM scustom INTO TABLE @customers.

filter = VALUE #( ( '1001' ) ( '1003' ) ( '1005' ) ).

DATA(filtered_customers) = FILTER #( customers 
                                     USING KEY primary_key IN filter
                                     WHERE id = table_line ).

Explanation:

1. customers is now a sorted table with a unique key (id), improving lookup efficiency.
2. The filter table (filter) has no key defined, so we use USING KEY primary_key for customers.
3. Using a sorted key allows ABAP to efficiently filter data by leveraging optimized lookups.

Example 3 – Filtering Table Fields with EXCEPT

A common use case for the FILTER operator is to dynamically exclude specific fields from a component list based on configurable criteria. This example demonstrates how to achieve that using a custom filter table and the EXCEPT option.

DATA filter TYPE SORTED TABLE OF fieldname WITH UNIQUE KEY table_line.

SELECT field_name FROM zexclude_fields WHERE active = @abap_true INTO TABLE @filter.

DATA(components) = CAST cl_abap_structdescr(
                            cl_abap_typedescr=>describe_by_name( 'BUT000' ) )->components.

components = FILTER #( components EXCEPT IN filter WHERE name = table_line ).

Explanation

1. Retrieving Fields to Be Excluded
   
   • We select field names from the custom table zexclude_fields, where the field is marked as active (active = abap_true).
   • This table can be maintained dynamically, meaning users or administrators can modify the fields to be excluded without changing the code.
2. Extracting Components from BUT000
   
   • The BUT000 structure contains multiple fields (components).
   • We use cl_abap_typedescr=>describe_by_name to retrieve the list of components dynamically.
3. Applying the Filter Using EXCEPT
   
   • The FILTER … EXCEPT operation removes all components whose name matches any entry in the filter table.
   • Since filter is a sorted table with a unique key, lookups are optimized for performance.

Conclusion

By leveraging the FILTER operator with a filter table, you can significantly improve performance in ABAP. Always evaluate where to use USING KEY based on the size of the tables involved, and make use of the EXCEPT clause when excluding records. Try implementing these techniques in your own projects to experience the performance benefits firsthand.