What’s New In ABAP – Series 01, Episode 09 – Filter It!

Nagendra Babu
Authored by

Filter It!

FILTER operator is one of the constructor expressions which creates a result of a table type specified using ‘type’ in the below syntax.

General syntax:
… FILTER type(  itab [ EXCEPT ]  [ IN ftab ] [ USING KEY keyname ]
WHERE c1 op f1  [ AND c2 op f2 […] 
) …

According to the where condition specified the rows are taken from the existing internal table ‘itab’ and converted to the row type of ‘type’ and inserted into the target table on the left side.

Things to Keep in Mind:

1. The table that you are going to filter should be defined with at least one sorted key or hash key ( we see that in an example do not worry keep reading 🙂 )

2. USING KEY is optional when the table is having primary key( Sorted or Hashed itab) and USING KEY must be used FILTER when the table is defined with secondary key.

3. The addition EXCEPT is used to read the rows that do not meet the WHERE condition. It means when rows matches with WHERE condition will not insert into the target table. 

Filter with single values:

Filtering using single values. The columns of a table key of itab are compared with single values in the WHERE condition

E.g. 1:

WITH NON-UNIQUE SORTED KEY second_key COMPONENTS carrid cityfrom.

SELECT FROM spfli INTO TABLE @spfli_tab.

DATA(filter_tab=  FILTER #spfli_tab USING KEY second_key WHERE carrid   CONV #('LH'AND cityfrom   CONV #('FRANKFURT').

cl_demo_output=>display( filter_tab ).


Here the table having the primary key ‘CARRID’ and ‘CONNID’, suppose if you want to read the itab, using non primary key fileds it would cost a performance when dealing with huge number of records.

So we have created a secondary key for the components CARRID and CITYFROM. While filtering we have used USED KEY with secondary key name ‘second_key’ and a WHERE condition.


If the itab has primary key defined USING KEY is optional while using FILTER.


next episode Loop AT .. GROUP BY or series home page


E.g. 2:

**->fetch data
SELECT FROM spfli  INTO TABLE @spfli_tab.
DATA(filter_tab=  FILTER #spfli_tab  WHERE carrid   CONV #('LH')  AND   connid   CONV #('0017').
cl_demo_output=>displayfilter_tab ).

Here I did not used USING KEY in FILTER expression because the itab has a primary key definition, although it is optionall you can still use USING KEY in this case only as below.

DATA(filter_tab=  FILTER #spfli_tab USING KEY primary_key WHERE carrid   CONV #('LH')  AND   connid   CONV #('0017').

You need to add ‘USING KEY primary_key’. Here the label ‘primary_key’ should be used( not the fields, exactly as ‘primary_key’ ). You can try it out on your system.

Filter using values from an itab:

Filtering using values from an internal table ftab. In the WHERE condition, the columns of itab are compared with the values of the columns of the table key of the rows of the filter table ftab. Just have a look at the above general syntax to get better idea.

cityfrom TYPE spfli-cityfrom,
cityto   TYPE spfli-cityto,
END OF filter,
filter_tab TYPE HASHED TABLE OF filter
WITH UNIQUE KEY cityfrom cityto.
**->fetch data
SELECT FROM spfli INTO TABLE @DATA(spfli_tab).
**->preparing condition values in an itab
DATA(filter_tabVALUE filter_tabcityfrom 'FRANKFURT' cityto 'NEW YORK' )
cityfrom 'NEW YORK'   cityto 'FRANKFURT' ).
DATA(result_tabFILTER #spfli_tab IN filter_tab WHERE cityfrom   cityfrom AND   cityto     cityto ).
cl_demo_output=>displayresult_tab ).


In the above example ‘filter_tab’ rows values are used in the WHERE condition i.e. instead of specifying the values in the condition the values are taken from the itab ‘filter_tab’. So the ‘result_tab’ will contains only the rows which satisfy the row values of ‘filter_tab’ from ‘spfli_tab’;

As you can see our filter_tab has 2 records, so these two rows values are passed to the condition. Here we need to specify the ‘filter_tab’ components after the operator in the condition ( cityfrom = cityfrom ( component of the filter_tab ) ).


End of this section, go to next episode Loop AT .. GROUP BY or series home page

Got any queries? Please go ahead to the comment section and post your query. Happy to help :).