Skip to Content
0

How to use sub select & left outer join?

Dec 14, 2017 at 04:20 AM

63

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Ankit Maskara
Dec 14, 2017 at 05:05 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Emal Rahman Dec 14, 2017 at 03:47 PM
0

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?


sql-query.png (34.7 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0