CODEWITHSUNDEEP
abapopensql
Ovidiu Pocnet
Ovidiu Pocnet

Reputation: 589

How to write condition in LEFT OUTER JOIN for select options?

I have the following code which works fine:

TYPES: BEGIN OF ty_table,
   matnr TYPE marc-matnr,
   prodh TYPE mvke-prodh,
END OF ty_table. 

DATA: bukrs TYPE mvke-vkorg, matnr TYPE marc-matnr.
DATA gt_table TYPE TABLE OF ty_table.

SELECT-OPTIONS s_bukrs FOR bukrs.
SELECT-OPTIONS s_matnr FOR matnr.

SELECT marc~matnr, mvke~prodh
  FROM ( marc
         LEFT OUTER JOIN mvke
         ON mvke~matnr = marc~matnr )
    INTO TABLE @gt_table[]
       WHERE marc~matnr IN @s_matnr
         AND mvke~vkorg IN @s_bukrs.

I want to put the vkorg condition in LEFT OUTER JOIN, but it does not work:

SELECT marc~matnr, mvke~prodh
  FROM ( marc
         LEFT OUTER JOIN mvke
         ON mvke~matnr = marc~matnr
         AND mvke~vkorg IN @s_bukrs ) " <---
    INTO TABLE @gt_table[]
       WHERE marc~matnr IN @s_matnr.

By doing this, I receive the following error:

"@S_BUKRS" is invalid here (due to grammar). contains an invalid character or it is a

IF I write AND mvke~vkorg = @s_bukrs it works, but I need to use IN.

Upvotes: 0

Views: 9930

Answers (3)

Sushant Agarwal
Sushant Agarwal

Reputation: 548

You can find a detailed explanation of the use cases here sql-outer-join-overview-and-examples

I am adding the part of the above link here

SELECT *
FROM Employee
 LEFT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID

enter image description here

We need to note the table placement position in the Join statement. Currently, we have an Employee table on the left side and Departments table in Right side.

Let’s rewrite query and swap the position of tables in query. In this query, we have the Department table in left position, so the Left Outer Join should check the values for this table and return a NULL value in case of a mismatch.

In the following screenshot, you can see that only one NULL value for EmpID 11. It is because EmpID 11 is not available in the Employee table.

enter image description here

enter image description here

Upvotes: 0

Ovidiu Pocnet
Ovidiu Pocnet

Reputation: 589

I managed to make it work the way I wanted to:

SELECT marc~matnr, mvke~prodh
  FROM ( marc
         LEFT OUTER JOIN mvke
         ON mvke~matnr = marc~matnr )
    INTO TABLE @gt_table[]
       WHERE marc~matnr IN @s_matnr
         AND ( mvke~vkorg IN @s_bukrs
               OR mvke~vkorg IS NULL ). " <---

In this way it acts like a LEFT OUTER JOIN and also selects the MATNR that do not have a PRODH in MVKE table.

Upvotes: 3

Thomas Erd&#246;si
Thomas Erd&#246;si

Reputation: 546

You can only use operator '=' when specifying the JOIN-condition. Add your condition to your WHERE-clause.

SELECT *
  FROM marc
       INNER JOIN mara
               ON mara~matnr = marc~matnr
       LEFT OUTER JOIN mvke
                    ON mvke~matnr = marc~matnr
                   AND mvke~vtweg = @lv_vtweg
  INTO TABLE @gt_table[]
  WHERE marc~matnr IN @s_matnr
    AND mvke~vkorg IN @s_bukrs.

Upvotes: 2

Related Questions