LEFT OUTER JOIN

[Former Member]

Dear experts,

I want to ask about left outer join, the problem is that i used left outer join in my select statement

like below ;

SELECT

     QALS~PRUEFLOS

     QALS~CHARG

     QALS~KTEXTMAT

     QALS~AUFNR

     QALS~ZZ_TUMANALIZ

     QALS~PLNNR

     QALS~ENSTEHDAT

     QALS~MATNR

     QALS~ENTSTEZEIT

     QALS~OBJNR

     QALS~LAGORTCHRG

     QALS~KDAUF

     MARA~MATKL

     MARA~MTART

     QASR~MERKNR

     QASR~ZEITERSTL

     QASR~CODE1

     QASR~ERSTELLDAT

     QASR~ORIGINAL_INPUT

     QAPP~VORGLFNR

     QAPP~PROBENR

     INTO CORRESPONDING FIELDS OF TABLE ITAB FROM QALS

      INNER JOIN MARA ON QALS~MATNR    EQ MARA~MATNR

      LEFT OUTER JOIN QAPP ON QALS~PRUEFLOS EQ QAPP~PRUEFLOS

      INNER JOIN QASR ON QALS~PRUEFLOS EQ QASR~PRUEFLOS AND

                         QAPP~VORGLFNR EQ QASR~VORGLFNR AND

                         QAPP~PROBENR  EQ QASR~PROBENR

The key field prueflos; sometimes QAPP table or QASR table doesnt contains QALS-Prueflos so,

there is no result.I want to see all QALS lines in ALV report. I am waiting your solutions.Thanks.

Regards.

Fırtına.

[nabheetscn · Accepted solution]

Use left outer join on QASR also

[Former Member · Accepted solution]

Hi Nabheet,

I used LOJ on QASR but there is a syntax error like below ;

Unable to compare with "QASR~VORGLFNR". A table can be joined with a maximum of one other table using LEFT OUTER JOIN. .

Do u have any other idea ?

Thanks.

[former_member187748]

Hi Yigin,

please change your code logic as shown below, and see are you getting all your data during debugging.

SELECT

     QALS~PRUEFLOS

     QALS~CHARG

     QALS~KTEXTMAT

     QALS~AUFNR

     QALS~ZZ_TUMANALIZ

     QALS~PLNNR

     QALS~ENSTEHDAT

     QALS~MATNR

     QALS~ENTSTEZEIT

     QALS~OBJNR

     QALS~LAGORTCHRG

     QALS~KDAUF

     MARA~MATKL

     MARA~MTART

     QASR~MERKNR

     QASR~ZEITERSTL

     QASR~CODE1

     QASR~ERSTELLDAT

     QASR~ORIGINAL_INPUT

     QAPP~VORGLFNR

     QAPP~PROBENR

     INTO TABLE ITAB FROM QALS AS Q

     LEFT OUTER JOIN MARA AS M ON Q~MATNR    EQ M~MATNR

      LEFT OUTER JOIN QAPP AS P  ON Q~PRUEFLOS EQ P~PRUEFLOS

      LEFT OUTER JOIN QASR AS S ON Q~PRUEFLOS EQ S~PRUEFLOS AND

                         P~VORGLFNR EQ S~VORGLFNR

                         AND

                         P~PROBENR  EQ S~PROBENR

[Former Member]

Hi Sanjeev,

Thanks for your answers.

I used LOJ on QASR but there is a syntax error like below ;

Unable to compare with "QASR~VORGLFNR". A table can be joined with a maximum of one other table using LEFT OUTER JOIN. .

Do u have any other idea ?

Thanks.

[Former Member]

Hi,

Presently I don't have the system to check, As I found that you are using wrong syntax.

Basic sap help

only possible

left outer join  table B to  table A

left outer join table C to  table A

left outer join table D to  table A

wrong syntax:

left outer join table  A to table B

left outer join table B to  table C

Please paste your select here

[former_member187748]

Hi Yigin,

please change your code something like this, and see that still you are having problems.

SELECT

QALS~PRUEFLOS

     QALS~CHARG

     QALS~KTEXTMAT

     QALS~AUFNR

     QALS~ZZ_TUMANALIZ

     QALS~PLNNR

     QALS~ENSTEHDAT

     QALS~MATNR

     QALS~ENTSTEZEIT

     QALS~OBJNR

     QALS~LAGORTCHRG

     QALS~KDAUF

     MARA~MATKL

     MARA~MTART

     QASR~MERKNR

     QASR~ZEITERSTL

     QASR~CODE1

     QASR~ERSTELLDAT

     QASR~ORIGINAL_INPUT

     QAPP~VORGLFNR

     QAPP~PROBENR

INTO TABLE Itab FROM ( ( ( QALS AS Q

      INNER JOIN MARA AS M ON Q~MATNR EQ M~MATNR )

      INNER  JOIN QAPP AS P  ON Q~PRUEFLOS EQ P~PRUEFLOS )

      INNER  JOIN QASR AS S ON Q~PRUEFLOS EQ S~PRUEFLOS )

             WHERE P~VORGLFNR EQ S~VORGLFNR

             AND P~PROBENR  EQ S~PROBENR.

ENDSLECT.

[former_member187748]

Hi Yigit,

please see if you are using left outer join between two tables say table TAB1 having 10 sets of data and TAB2 with  15 sets of data,

if you will use LEFT INNER JOIN in tab1 and tab2, then it will see only the entries in tab1,

so you will have a set of 10 records, but you will loose the additional 5 records which are present in tab2

so for such type of conditions we uses LEFT OUTER JOIN, in which it tooks in consideration for both the tables on which you have taken a outer join, in this way you will get all the from both the tables,

without loosing any data.