topic Re: difference between inner join and left outer join in Application Development and Automation Discussions

difference between inner join and left outer join

Former Member — Thu, 02 Aug 2012 10:20:13 GMT

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

Re: difference between inner join and left outer join

former_member209818 — Thu, 02 Aug 2012 10:31:46 GMT

This message was moderated.

Re: difference between inner join and left outer join

koolspy_ultimate — Thu, 02 Aug 2012 10:47:14 GMT

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.

Re: difference between inner join and left outer join

hubert_heitzer — Thu, 02 Aug 2012 10:57:16 GMT

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

Re: difference between inner join and left outer join

Former Member — Thu, 02 Aug 2012 11:25:25 GMT

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

Re: difference between inner join and left outer join

koolspy_ultimate — Thu, 02 Aug 2012 11:28:57 GMT

Hi ,

the below can't be true

f~vbeln = q~vbeln.

Re: difference between inner join and left outer join

hubert_heitzer — Thu, 02 Aug 2012 11:40:44 GMT

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

Re: difference between inner join and left outer join

Former Member — Thu, 02 Aug 2012 12:37:43 GMT

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