topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Thu, 14 Feb 2008 09:48:37 GMT

Hi Friends,

pls check the below code and help me to know how to relate the table of vbak and vbrp.....for return orders in the same stmt.

I've used the inner join stmt and it displays the report other than the return orders of sales....if i add to display the return delivery reports...it can doable via vbak->vbrp join....

FYI

if i use the 'INNER JOIN vbrp on vbak_{vbeln eq vbrp}vgbel instead of 'INNER JOIN vbrp on lips_{vbeln eq vbrp}vgbel' then the whole report goes duplication while appending.....

and in inner join, we can't use OR conditions.....

(the below replies not solving my problem 14/2/08)

pls advise.

*****coding

SELECT vbak_{vbeln vbak}erdat vbak_{kunnr kna1}name1 lips~vbeln

vbrp~vbeln

makt_{maktx vbrp}matnr vbrp_{fkimg vbrp}netwr VBAK_{AUART VBRP}WERKS

INTO TABLE i_sales

FROM vbak

inner join vbap on vbak_{vbeln eq vbap}vbeln

INNER JOIN vbpa ON vbak_{vbeln EQ vbpa}vbeln

INNER JOIN lips ON vbak_{vbeln EQ lips}vgbel

INNER JOIN vbrp on lips_{vbeln eq vbrp}vgbel

inner join kna1 on vbak_{kunnr eq kna1}kunnr

inner join makt on vbrp_{matnr eq makt}matnr

WHERE vbpa_{parvw EQ 'AG' and lips}posnr eq vbrp_{vgpos and vbak}vkorg in l_salorg

and vbak_{vtweg in l_chanel and vbak}spart in l_dvsn and vbak~erdat in l_date and

vbrp_{matnr in l_matnr and VBAK}AUART IN L_AUART AND vbrp~werks in l_werks.

thanks & regards

sankar.

Edited by: Alvaro Tejada Galindo on Feb 14, 2008 2:07 PM

Edited by: sankar babu on Feb 15, 2008 2:57 PM

Re: Inner join

Former Member — Thu, 14 Feb 2008 11:13:05 GMT

START-OF-SELECTION.

select a_{kunag a}vbeln a_{fkdat a}bukrs a~vbtyp

b_{matkl b}matnr b_{arktx b}fkimg b_{kzwi2 b}wavwr

c~name1

d~kunn2

into corresponding fields of table replines

from vbrk as a

inner join vbrp as b

on a_{vbeln = b}vbeln

inner join kna1 as c

on a_{kunag = c}kunnr

left outer join knvp as d

on a_{kunag = d}kunnr and

a_{vkorg = d}vkorg and

a_{vtweg = d}vtweg and

d~spart = '71' and

d~parvw = 'CO' and

d~parza = '000'

where a~vkorg = '7100' and

a~vbtyp in r_vtyp and

a~fkdat in s_date and

a~kunag in s_cust and

b~matkl in s_mgrp and

b~autyp in r_atyp.

END-OF-SELECTION

<REMOVED BY MODERATOR>

regards,

priya.

Edited by: Alvaro Tejada Galindo on Feb 14, 2008 2:07 PM

Re: Inner join

Former Member — Thu, 14 Feb 2008 11:25:11 GMT

Hi.

Dont use Inner joint,mostly we need to avoid use inner join,its not optimiz,use All entries.

try this code.

Select vbeln erdat kunnr AUART from VBRK into IT_VBRK .

Select vbeln matnr fkimg netwr WERKS from VBRP into IT_VBRP for all entries in it_vbrk where vbeln eq it_vbak-vbeln.

Select vbeln from LIPS into it_lips for all entries in it_vbrp where vbeln = it_vbrp-vgbel.

Select kunnr name1 from KAN1 into IT_KNA1 for all entries IT_VBAK where kunnr = it_vbrk-kunnr.

Select matnr from makt into it_makt for all entries in it_vbrb where matnr = it_vbrp-matnr.

<REMOVED BY MODERATOR>

Jay

Edited by: Alvaro Tejada Galindo on Feb 14, 2008 2:08 PM

Re: Inner join

Former Member — Thu, 14 Feb 2008 19:12:11 GMT

Jay - that's incorrect. JOINs usually outperform FOR ALL ENTRIES.

Bear in mind that ABAP implements views using JOINs.

Rob

Re: Inner join

Former Member — Fri, 15 Feb 2008 14:08:57 GMT

Hi,

You are using somany tables in innerjoins.

If you do for more than 3 joins, the performance wise it is not good. First you join 3 tables. then for all entries of that table select from other tables.

regards,

kamala.

Re: Inner join

Former Member — Fri, 15 Feb 2008 14:16:42 GMT

Hi Rob,

Even in my opinion for all entries is better performance wise than join. A simple reason behind this is the time for which your database access remains open. This palys major role when your tables ahve huge data. It is quiet possible that your join statement might go in dump in case of huge data.

Regards,

Siddhesh S.Tawate

Re: Inner join

Former Member — Fri, 15 Feb 2008 14:29:51 GMT

Hi Kamala,

Will u explain me clearly...

thansk & regards

sankar.

Re: Inner join

Former Member — Fri, 15 Feb 2008 14:50:12 GMT

Siddhesh - please see:

[JOINS vs. FOR ALL ENTRIES - Which Performs Better?|/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better]

[FOR ALL ENTRIES vs DB2 JOIN|http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912]

Rob