CODEWITHSUNDEEP
abapopensql
user1612058
user1612058

Reputation: 19

Not able to LEFT OUTER JOIN three tables

I've got a problem. Currently I have three tables, the first table is the main table and if there is no record in the second table, write it too. But if a record exists in the second table, so from the third table display "dispo" information..

I want to use three SAP table - lagp, lqua and marc. Me goal is write all stock positions from lagp.

2x LEFT JOIN doesnt work: "Unable to compare with"B~MATNR". A table can be joined with a maximum of one other table usign LEFT OUTER JOIN.

Structure:

TYPES:
  BEGIN OF t_work,
    lgnum TYPE lgnum,
    lgtyp TYPE lgtyp,
    lgpla TYPE lgpla,
    bdatu TYPE lagp_bdatu,
    matnr TYPE matnr,
    verme TYPE lqua_verme,
    meins TYPE meins,
    dispo TYPE dispo,
  END OF t_work.
DATA:
  lt_work TYPE TABLE OF t_work INITIAL SIZE 0,
  ls_work LIKE LINE OF lt_work.

And SQL command:

SELECT a~lgnum a~lgtyp a~lgpla a~bdatu b~matnr b~verme b~meins c~dispo FROM lagp AS a
   LEFT JOIN lqua AS b ON a~lgnum = b~lgnum AND a~lgtyp = b~lgtyp AND a~lgpla = b~lgpla
  INNER JOIN marc AS c ON b~matnr = c~matnr AND b~werks = c~werks
  INTO TABLE lt_work
   WHERE a~lgnum IN so_lgnum
     AND a~lgtyp IN so_lgtyp
     AND a~skzua EQ space
     AND a~skzue EQ space.

But as result is only one stock position - https://i.sstatic.net/1sEEo.png

Can you tell me, how the SQL code has look?

Thank you

Upvotes: 0

Views: 3389

Answers (2)

inetphantom
inetphantom

Reputation: 2594

I got that fine working code: 

  SELECT ma~matnr ma~mtart ma~ernam ma~ersda ma~laeda
  de~maktx as maktx_de fr~maktx as maktx_fr it~maktx as maktx_it
  FROM mara as ma
  LEFT JOIN MAKT as de ON de~matnr = ma~matnr AND de~spras = 'DE'
  LEFT JOIN MAKT as fr ON fr~matnr = ma~matnr AND fr~spras = 'FR'
  LEFT JOIN MAKT as it ON it~matnr = ma~matnr AND it~spras = 'IT'
  INTO CORRESPONDING FIELDS OF TABLE g_it_material
  WHERE ma~ernam IN so_ERNAM
  AND ma~laeda IN so_LAEDA
  AND ma~matnr IN so_MATNR.

and it works fine. What did you say about multi-leftjoins?

Upvotes: 0

Gert Beukema
Gert Beukema

Reputation: 2565

Try:

SELECT a~lgnum, a~lgtyp, a~lgpla, a~bdatu, b~matnr, b~verme, b~meins, c~dispo 
   FROM lagp AS a
   LEFT JOIN ( lqua AS b 
               JOIN marc AS c ON b~matnr = c~matnr AND b~werks = c~werks )
          ON a~lgnum = b~lgnum AND a~lgtyp = b~lgtyp AND a~lgpla = b~lgpla 
  INTO TABLE @lt_work
   WHERE a~lgnum IN @so_lgnum
     AND a~lgtyp IN @so_lgtyp
     AND a~skzua EQ @space
     AND a~skzue EQ @space.

Other option would be to put the join between LQUA and MARC in a view and do an outer join with the view. Or split out the select on MARC and do that while looping at the data found with a select on LAGP and LQUA.

Upvotes: 1

Related Questions