difference between inner join and left outer join

[Former Member]

Dear All;

The report i am working on write now takes 3 fields values from two tables: VBAP, and LIPS and print the output on the screen.

The code is as the following:

===============================================================================================

REPORT  ZDEMO_SELECT_LEFT_OUTER_JOIN2.

DATA: BEGIN OF wa,

       LFIMG TYPE LIPS-LFIMG,

       KDAUF TYPE LIPS-KDAUF,

       KWMENG TYPE VBAP-kwmeng,

       END OF wa,

       itab LIKE SORTED TABLE OF wa WITH NON-UNIQUE KEY LFIMG KDAUF KWMENG.

       SELECT f~LFIMG f~KDAUF q~kwmeng  INTO CORRESPONDING FIELDS OF TABLE itab

       FROM VBAP AS q INNER JOIN LIPS AS f ON q~vgpos = f~vbeln AND q~VGPOS = f~POSNR.

WRITE: /10 'Delivery Quantity', 40 'Sales Order', 70 'Order Quantity'.

   LOOP AT itab INTO wa.

   WRITE: /10 wa-LFIMG, 40 wa-KDAUF, 70 wa-KWMENG.

ENDLOOP.

================================================================================================

The issue i am facing is the output of this. when i use inner join i get no output:

when i change the select statement to left outer join i get the following output:

can you help me please in knowing how can i get the correct output for the three fields, and knowing why i am not getting any output at the field "sales_order" --> KDAUF from the table lips

Best Regards

~Amal

[former_member209818]

This message was moderated.

[koolspy_ultimate]

hi Amal Aloun.

report  zdemo_select_left_outer_join2.



data: begin of wa,
         lfimg type lips-lfimg,
         vgbel type lips-vgbel,
         kwmeng type vbap-kwmeng,
         end of wa,
         itab like sorted table of wa with non-unique key lfimg vgbel kwmeng.


select f~lfimg
        f~vgbel
        q~kwmeng
         into corresponding fields of table itab
         from lips as f inner join vbap as q
         on f~vgbel = q~vbeln .


loop at itab into wa.
    write: / wa-lfimg,
             wa-vgbel,
             wa-kwmeng.
endloop.

[hubert_heitzer]

The condition

...
ON q~vgpos = f~vbeln 
...

looks very strange to me. U compare NUMC6 with CHAR10.

Sure u dont mean

...

ON q~vbeln = f~vbeln

...

?

Regards, Hubert

[Former Member]

Dear Hubert;

the condition in my select statement now is: 

f~vbeln = q~vbeln AND f~posnr = q-posnr

is this the correct condition for tables VBAP, and LIPS?

Best Regards

~Amal

[koolspy_ultimate]

Hi ,

the below can't be true

f~vbeln = q~vbeln.

[hubert_heitzer]

Sorry Amal,

I cannot know this.

U and your consaltants have to know this.

I even dont know, if U use the right tables for your requirement.

This condition looks better but unfortunately I anyhow think it is not correct.

Good luck,

Hubert

[Former Member]

Hi Amal,

you need to offer some more informations about the requierments.

In generall:

VBAP -> stores the PO lines

LIPS -> stores the delivery lines

One PO line could referenc to 0 to n delivery lines.

One delivery line could referenc to 1 to zero PO lines. (Yes there could be business processes were a delivery is created withou a PO line referenc).

The link between VBAP to LIPS is mostly:

VBAP-VBELN = LIPS-VGBEL

VBAP-POSNR = LIPS-VGPOS

but there could be some processes were KDAUF/KDPOS is also used.

As you are using LEFT OUTER JOIN, you need to know what you want:

Select all Deliverys and get the related PO lines:

FROM LIPS LEFT JOIN VBAP

Select all PO lines and get the releated Deleivery lines:

FROM VBAP LEFT JOIN LIPS.

(Will select also PO lines were no deliveriy exsists)

Regards Hendrik