How can I transform a SELECT with FOR ALL ENTRIES statement into a JOIN?

Question

Suppose if I have a statement like this:

select * from city into table it_city_table 
 for all entries in it_metropolitans
 where city_id eq it_metropolitans-city_id
 and winter ne 'cold'.

Now if it_metropolitans was formed from joining several other tables together, the extended ABAP syntax check says that I need to transform this for all entries statement into a JOIN. But I'm not sure how this can be done.

I tried searching for how a for all entries statement can be transformed into a join online without much success.

Answer 1

You can only transform this Select-Statement into a Join if it_metropolitans can somehow be selected from the database. Then you would join the two tables using city_id as the join condition.

Usually the extended check only complains if the internal table is selected somewhere close in the same form/method. See if you can find the source table for it_metropolitans and join with that.

Answer 2

for JOIN, It requires you to SELECT from at least 2 Database Tables (or Tables that exist in SAP Dictionary) and put it into 1 Internal Table (ITAB)

for FOR ALL ENTRIES the steps are bit longer. It requires you to SELECT each Database Table separately and put it into their corresponding Internal table. For your example would be

1. SELECT from CITY Table to internal table City (it_city_table)
2. SELECT from METROPOLITANS Table to internal table Metropolitans (it_metropolitans)
3. Join both Internal table from step 1 and 2 and it should looks like the one from your question

So to answer your question, the code for JOIN should look something like this

  SELECT *
  FROM city as a
  INNER JOIN metropolitans as b ON a~city_id eq b~city_id
  WHERE a~winter NE 'cold'.
  INTO TABLE it_city_metropolitan.

Answer 3

As for ABAP 7.52 inner tables are supported as a data source for SQL queries. If your logic to populate it_metropolitans is too complex to incorporate into the same query you can you can join internal table.

select * from city 
 inner join @it_metropolitans as metropolitans
  on city~city_id = metropolitans~city_id
 where city~winter ne 'cold'
 into corresponding fields of table @it_city_table .

Direct DB query without retrieving unnecessary data are almost always faster. There are a few exceptions with odd SAP tables and when you cannot retrieve unique results (so for example you have duplicate entries in it_metropolitan you need to remove).

If that isn't possible my next recommendation is inner join itab. It's not as fast as 1 query, but still usually a lot faster than for all entries. It also works more intuitively. for all entries itab returns everything if itab is empty and duplicates are removed.