Why Doesn't My ABAP SQL Query Work?

I imagine virtually every ABAP programmer makes common use of the FOR ALL ENTRIES construct for database access, to retrieve entries into an internal table based on the contents of another table – the selection list.

However, I have been reviewing some old – and not so old – programs recently and have found a lot of people get into trouble with this seemingly simple query so I thought I would share some tips and tricks in regard to FOR ALL ENTRIES (hereafter FAE).
 

FOR ALL ENTRIES Main Bug

In IT there is the “Principle of Least Astonishment” which says software you write should not exhibit behavior that “astonishes” your users in a negative way. In the case of FAE, you hand the SQL statement a list of what you want (the selection table, T_WERKS in the above example) and if that list is empty you would reasonably expect no results to come back. Instead, you are astonished to find that a full table scan is done, every entry in the table is returned, and a dump usually occurs.

So you need to check that the selection table is NOT INITIAL before doing an FAE.

The code inspector warns you of potential problems in this area, and transaction SRTCM can be used to look for such situations in the live system.
 

Confusing Tables and Header Lines

Here the programmer thinks they are checking that the table GT_KRONOSALL is empty. But because GT_KRONOSALL is defined as OCCURS 0 it has a header line and the above check is actually checking that the header line is empty. The header line could be full and yet the table empty. This is one good reason not to use header lines. In the above case, you need to do either GT_KRONOSALL[] IS INITIAL or LINES( GT_KRONOSALL) > 0.
 

Duplicate SELECTS in FOR ALL ENTRIES

Here the selection table GT_ALV_OUTPUT has eight entries for each VBELN as it has joined the delivery table with several other tables. Thus when the FAE is done the SQL trace shows each delivery number repeated eight-time i.e. eight times the database access actually needed, as shown in the following SQL trace:-

You need to get a unique list of numbers for selection as follows:-

This is also a minor performance improvement if you sort of the records in the selection table before passing it to the SQL query as then less database “blocks” have to be read – if you were a postman you would not walk to 1 Blogs Street, then 99 Blogs Street then back to 2 Blogs Street, you would deliver to 1 then 2 then 99.
 

Implicit DISTINCT in FOR ALL ENTRIES

This one can really make your head spin if you do not know what is going on.

Here the programmer is trying to get a list of all the materials and their quantities (KWMENG) for a list of sales orders, and then they are going to output some sort of summary. Yet the results come out wrong. Why? Because an FAE query contains an implicit DISTINCT clause which means that only one record for every unique combination of VBELN / MATNR / KWMENG is returned. So if there are five records in an order with the same material and quantity, only one will be returned.

You fix this by adding item number (POSNR) to the fields you are selecting even if you do not intend to use it, that way you can be sure every single order item is returned.
 

Using FAE Instead of a JOIN

I hope no-one does this anymore, but I thought I would mention this for the sake of completeness.

You can achieve the same result using an INNER JOIN on the two tables and the database query will be a hundred times faster. In fact, in the exact example above SAP got round the problem entirely by adding a lot of the MKPF fields to MSEG so you only need to read MSEG.

Generally, however, you would read related tables like VBAK/VBAK or LIKP/LIPS using a JOIN.

The code inspector will identify situations in your programs where FAE can be changed to a JOIN.

FAE Buffer Behavior

If your ABAP system is on a lower release than 7.40 then the following would not be a good thing to do:-

The reason being that table T001W uses generic buffering and up until ABAP release 7.40 SAP buffering was bypassed by FAE so you would actually have less database access (and thus better performance) by reading the rows individually in a LOOP.

From release 7.40 and up for tables with single record buffering (provided the full primary key is used in the query) and for tables with generic buffering (provided all the fields in the generic area are specified) the buffer is used and thus there will be no database access.

In the example above T001W is generally buffered on MANDT / WERKS and since both fields are passed in the query (MANDT is passed implicitly) the buffer will be used.
 

The Future of FAE

In traditional databases (what SAP calls “ANYDB” ) in an FAE query, the SQL trace will show a large number of lines as the database is accessed repeatedly with groups of values, as shown below:-

In a HANA database, however, things work quite differently. In this situation, a temporary table is created on the database itself, and the contents of the selection table are inserted into that temporary table.

Then the main SQL query is executed as an INNER JOIN between the target table(s) and the temporary table containing the selection list.  A JOIN is far faster than multiple reads, and moreover in an INNER JOIN if the selection table is empty then no results are returned, solving the main bug we have at the moment.
 

Video lesson: Reading data in ABAP with open SQL

In Summary

Hopefully, this blog has told you some things you didn’t know about FOR ALL ENTRIES which can help your day-to-day programming. Happy Coding!