Left Outer Join on internal table in ABAP

[mikeb]

Hi, ABAP Gurus

There is a task, where we have to implement left outer join, the problem is that left side of this join is an internal table. As far as I know, the only way to involve intenal table into SQL-query in ABAP is to use FOR ALL ENTRIES (FAE), the problem is that FAE implements inner join and not left outer join.

I would know if there is any way to implement left outer join avoiding manual implementation with nested loops and complexity n²?

Thanks.

[mikeb]

Hi, ABAP Gurus

There is a task, where we have to implement left outer join, the problem is that left side of this join is an internal table. As far as I know, the only way to involve intenal table into SQL-query in ABAP is to use FOR ALL ENTRIES (FAE), the problem is that FAE implements inner join and not left outer join.

I would know if there is any way to implement left outer join avoiding manual implementation with nested loops and complexity n²?

Thanks.

[nabheetscn]

Hi Mike

Can you please detail us what kind of data you are trying to select. We can not use left outer join on internal tables as you already mentioned it is inner join.

If you can provide the detail of the issue/idea then may be we can think of other alternatives.

Nabheet

[mikeb]

Some additional information:

We have internal table, which is generated from external file, this internal table contains huge number of lines (≈50…100K), and now we have to execute left outer join with DB-table, while internal table is a left-side table.

Additional complications are:

— this internal table will be frequently updated and this query must be as much as possible closer to real time, so you can't put this into kind of job and postpone it to the night;

— one of the conditions of this left outer join is a check if the specific field of internal table line is empty (therefore you can't add this condition to FAE where expression).

So, what kind of solution can be proposed in this situation, may be there is another approach to implement such task, even if we're talking about to move this out of SAP/ABAP.

Thanks.

[Former Member]

Option A

1. Build a Z table with same structure as your internal table

2. delete all entries in the Z table

3. load your data in the Z table

4. perform the left outer join

5. Delete al entries in your Z table

Option B

1. Build a Z table with same structure as your internal table + a specific field acting as a time stamp as an extra key field

2. Load your data in the Z table

3. perform the left outer join using the keyfields 

Hope it helps

Parvez

[custodio_deoliveira]

Be very careful with this approach as you might have performance issues with the loading/deleting data to the Z table.

[sascha_reissig]

Hi Mike,

did you tried something like this (short example):

regards, Sascha

[mikeb]

Hi, Sascha

I'll check this option and will update you!

[mikeb]

Hi, Sascha

I looked into your example and didn't caught the pointer, what is the reason to perform LOJ on table it self — LOJ(a, a) and after that to do FAE? In other words, for what to increase columns number, how exactly it may be usefull?

Could you clarify it, please.

[sascha_reissig]

Hi Mike, I checked my example and a lot of others, too (incl. sub queries, EXEC SQL, Cursor, etc.)  - just to understand how this can be solved, but there is no way to fulfill the requirement! It's because you'll never get the entry of the internal table in the database cursor, so it is not possible to move the record in the result table. In my example I was hoping to get exactly this by creating a projection on these tables.  So at the end we come back to the idea to store your data first in a DDIC table and then do the outer join (but be aware about the performance issues), secondly and this is my preferred solution is doing it first with a inner join and then using the internal tables for joining them.

Sorry Mike but I fear that there is no other way .... regards, Sascha

[custodio_deoliveira]

Hi Mike,

You probably had a select statement to build the internal table. Can't you implement the outer join when building this internal table?

Some sample code would help us to propose solutions.

Cheers,

Custodio

[mikeb]

Internal table has been generated from external file, not as result of other SQL-statement.

[former_member191569]

Hello Mike,

I think you can still use FOR ALL ENTRIES to get the "inner join" between the internal table (let me call internal table A) data and the database table. Put the results in a second internal table (this will be internal table B).

At this point, you have missed the entries in internal table A that do not "inner join" with the database table. To get these entries, you can work with tables A and B, if you sort them and perform binary search you can put the remaining entries in table A into table B.

Hope it helps,

David

[arindam_m]

Hi Mike,

If you are not too fixed on Open SQL uasge You can try the SQL with the new ADBC class to write a DB specific query and process the result set. Check demo program ADBC_DEMO.

Cheers,

Arindam

[mikeb]

Thanks, Arindam

I don't have an access to SAP right now, thus I'm asking here, does «ADBC-class» approach support

dataretreiving with inbolving internal table?

[vimalv]

Mike,

Try this approach :

1 . Declare three internal tables - IT_LEFT and IT_RIGHT and IT_JOIN.

2.  Load IT_LEFT with the data in file.

3.  Select records from the DB Table into IT_RIGHT with a For All Entries(FAE) in IT_LEFT.

Now, IT_LEFT contains records from the External File,  and IT_RIGHT contains records from the DB Table which have a relation to certain records from IT_LEFT.

4. Build table IT_JOIN like given below:

               LOOP at IT_LEFT.

                    MOVE-CORRESPONDING IT_LEFT into IT_JOIN.

                    READ TABLE IT_RIGHT with KEY.. from IT_LEFT value.

                         IF SY-SUBRC = 0.

                              MOVE-CORRESPONDING IT_RIGHT into IT_JOIN.

                         ENDIF.

                    APPEND IT_JOIN.

                   ENDLOOP.

IT_JOIN will now contain the LEFT OUTER JOIN for IT_LEFT and IT_RIGHT.

Hope this helps.

Regards,

Vimal

[mikeb]

Vimal,

Thanks for the proposed solution, this one is quite similar to my-own, except one thing.

Is there any difference between nested loop and READ TABLE WITH KEY inside of LOOP from performance point of view?

[vimalv]

Mike,

For your scenario, there could be only one matching record that need to be considered from the Right table, so it is better to go for a READ TABLE.

Until you need to process multiple records in the inner loop for a single iteration of the outer loop, it is advisable to avoid the nested loop approach.

In the scenario that you have defined, you would be wasting cycles looping thorough unwanted records when using a nested loop.

Also, if nested loops can not be avoided, it is advised to follow the parallel cursor approach to process the inner loop.

If ITAB1 has n1 entries and ITAB2 has n2 entries, the time needed for  the nested loop with the straightforward algorithm is O(n1 * n2),  whereas the parallel cursor approach takes only O(n1 + n2) time. In case you want to know how to implement the parallel cursor, please check this thread.

Regards,

Vimal

[somopriyo_gorai]

This approach will display left outer join using FOR ALL ENTRIES tested

1 . Declare three internal tables - IT_LEFT and IT_RIGHT and IT_JOIN.

2.  Load IT_LEFT with the data in file.

3.  Select records from the DB Table into IT_RIGHT with a For All Entries(FAE) in IT_LEFT.

Now, IT_LEFT contains records from the External File,  and IT_RIGHT contains records from the DB Table which have a relation to certain records from IT_LEFT.

4. Build table IT_JOIN like given below:

               LOOP at IT_LEFT.

                    MOVE-CORRESPONDING IT_LEFT into IT_JOIN.

                    READ TABLE IT_RIGHT with KEY.. from IT_LEFT value.

                         IF SY-SUBRC = 0.

                              MOVE-CORRESPONDING IT_RIGHT into IT_JOIN.

                         ENDIF.

                    APPEND IT_JOIN.

                   ENDLOOP.

IT_JOIN will now contain the LEFT OUTER JOIN for IT_LEFT and IT_RIGHT.