Question Re: How to use sub select & left outer join? in Technology Q&A

How to use sub select & left outer join?

Former Member — Thu, 14 Dec 2017 04:20:52 GMT

I'm trying to perform the below sql-statement:

But there I got a syntax error on line 43, which says the select statement is not allowed here.

I tried also

the error message says "SELECT" is in ABAP-Dictionay not as table, projection or db-view declaerd. Finally I modified the sql statement as below

and got the error message: 'JOIN' was expected here.

How can sub select be used after a left outer join or inner join statement as table correctly?

Re: How to use sub select & left outer join?

Ankit_Maskara — Thu, 14 Dec 2017 05:05:45 GMT

Select...Endselect construct is not advised to be used due to performance issues.

Instead, refactor as below-

1. Fetch data from VBRL in an internal table with a where condition matching your requirement.

2. Fetch all records from VBRK table with a where condition matching your requirement.

3. Loop at the result table of step2.

4. Inside the loop at step 3, put a loop at table of step 1 and pick the requisite records with condition similar to where clause of JOIN.

5. Create a final table(similar to l_it_fakbuch) and use.

* You can also try implementing parallel cursor based on nature of data.

Re: How to use sub select & left outer join?

Former Member — Thu, 14 Dec 2017 15:47:11 GMT

Because there is only one condition (vbrl~zz_fbuda_min in datum) after WHERE clause, I have decided to put this condition after ON clause. I think the implication will be the same. Please correct me if I'm wrrong. At the moment my sql query looks like below

But the question is and remains, how a sub select can be used after a LlEFT OUTER JOIN resp. INNER JOIN?

Re: How to use sub select & left outer join?

Ankit_Maskara — Tue, 19 Dec 2017 05:35:15 GMT

You can check here for Select subquery syntax with Inner Join - https://archive.sap.com/discussions/thread/1326078.

Still from performance perspective I will not advise your select query.

Please avoid select....endselect and in the where clause use positive conditions like IN or EQ etc. Using negatives line <> degrades the runtime.