topic Re: Join 4 tables...... in Application Development and Automation Discussions

Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:08:22 GMT

Hi Guys,

Good morning. May I share the problem correlated to inner join for 4 tables? One custom table, KNVV, VBAK and VBAP tables with me. I have the selection criteria for the following:

HKUNNR, KUNNR, VKORG, VTGEW, SPART, VKGRP, WERKS, MATNR and VBTYP.

I need to select VKGRP and KUNNR from KNVV and VBELN, POSNR from VBAP.

My custom table have fields for the following.

MANDT Client

KUNNR Customer

VKORG Sales Organization

VTWEG Distribution Channel

SPART Division

HKUNNR Customer number of the higher-level customer hierarchy

HITYP Customer hierarchy type

HILVL Customer Hierarchy Level (Customer or Buying Group)

KTOKD Customer Account Group

It was wondered for me that my query is showing the error. Please suggest me how can I approach to join these 4 tables. Your help will be greatly appreciate. Waiting ofr your valuable suggestions.....

Regards,

Ravi…

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:17:00 GMT

Hi Ravi,

i thaink if u r joining more than two tables means better to use for "all entries statements" it will give u high perforamances comparied to joining the tables where more than two table.

reward is usefull.

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:21:20 GMT

Ravi what Nagraj says is correct it is not good programming practice to use joins on more than 3 tables this will increase data retreival time and program will run slow, better use FOR ALL ENTRIES. if your still want to go with join then pl make this clear. what fields u want to extract from which tabel your question is lil confusing.

Regards

Quavi.

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:30:23 GMT

you can join any number of tables but t he problem with joins is

if you join more than 2 tables then the performance reduces very much

thats why SAP advises to use FOR ALL ENTRIES if there are more than 2 tables to join

if you use more than 2 tables in joins what happens is the database connectivity is there for that 4 tables up to the execution of the program so it degardes the performance so better to use FOR ALL ENTRIES

syntax :

select data from dbtable1 into table itab1 where condition

if not itab[] is initial

select data from dbtable2 into table itab2 FOR ALL ENTRIES in itab1 where condition

endif.

if not itab2 is initial .

select data from dbtable3 into table itab3 FOR ALL ENTRIES in itab2 where condition

endif.

........

.....

like this you need to extract the data

with this you don't get any problem in feture regarding performance

<b>Reward fi usefull</b>

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:37:05 GMT

Hi,

Thanks for your valuable suggestions. As per my previous query I was mentioned details of selection fields and selection screen fields and tables also.

Here please find my query which I was assigned to little bit suspect ion. It would be great for me if I will get your valuable suggestions up on this query…

<b>SELECT P~VBELN

P~POSNR

P~VBTYP

P~VKGRP

K~KUNNR

K~VKGRP

INTO TABLE I_JOIN

FROM KNVV AS K

INNER JOIN ZNCUSTHRCY AS A ON A_{KUNNR = K}KUNNR

INNER JOIN VBAP AS P ON P_{SPART = K}SPART

INNER JOIN VBAK AS V ON V_{VBELN = P}VBELN

AND V_{POSNR = P}POSNR

AND V_{VTWEG = A}VTWEG

WHERE A~HKUNNR IN S_HKUNNR

AND A~KUNNR IN S_KUNNR

AND B~VKORG = P-VKORG

AND B~VTWEG IN S_VTWEG

AND B~SPART IN S_SPART

AND B~VKGRP IN S_VKGRP

AND P~WERKS IN S_WERKS

AND P~MATMR IN S_MATNR

AND P~VBTYP IN (‘C’ , ‘J’).</b>

Regards,

Ravi...

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:42:45 GMT

Hi Ravi,

I am just sending just template of how to join, if we have more than 2 tables.

i used sflight,sbook,spfli tables.

if u see in the PERFORM, in that select stmt, we can add 'n' no of joins like that, how i did for 3 tables. normally for 4 tables also same. plz try it out.

Sample Prog:

REPORT Z14049_ABAPQUERY1.

TABLES: SFLIGHT,SBOOK,SPFLI.

DATA: L_CITYFRM LIKE SPFLI-CITYFROM,

L_CITYTO LIKE SPFLI-CITYTO.

DATA: BEGIN OF ITAB1 OCCURS 0,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID,

FLDATE LIKE SFLIGHT-FLDATE,

PRICE LIKE SFLIGHT-PRICE,

BOOKID LIKE SBOOK-BOOKID,

CUSTOMID LIKE SBOOK-CUSTOMID,

CITYFROM LIKE SPFLI-CITYFROM,

CITYTO LIKE SPFLI-CITYTO,

END OF ITAB1.

SELECT-OPTIONS: S_CITYF FOR L_CITYFRM,

S_CITYTO FOR L_CITYTO.

START-OF-SELECTION.

PERFORM GET_DATA.

LOOP AT ITAB1.

WRITE:/ ITAB1-CARRID , ITAB1-CONNID, ITAB1-FLDATE,ITAB1-PRICE,ITAB1-BOOKID,ITAB1-CONNID,ITAB1-CITYFROM,ITAB1-CITYTO.

ENDLOOP.

&----

*& Form GET_DATA

&----

form GET_DATA .

SELECT S_{CARRID S}CONNID S_{FLDATE S}PRICE B_{BOOKID B}CUSTOMID F_{CITYFROM F}CITYTO

INTO TABLE ITAB1

FROM ( ( SFLIGHT AS S

INNER JOIN SBOOK AS B ON S_{CARRID = B}CARRID )

INNER JOIN SPFLI AS F ON B_{CARRID = F}CARRID )

WHERE F~CITYFROM IN S_CITYF

AND F~CITYTO IN S_CITYTO.

Thanks,

Pradeep

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:51:12 GMT

Hi Pradeep,

Thanks for your suggestion. What i am trying to claryfy with you guys is: is it correct which i mentioned the select statement in the above mail.

I am trying to get clarity correlated to that tables with all conditions. Please clarify me..thanks for your suggestions..

Regards,

ravi

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 05:56:50 GMT

Hi Ravi.

Your code is correct. carry on. However keep one thing in mind that it is good not to use joins on more than 3 tables.

Regards,

Quavi.

Re: Join 4 tables......

Former Member — Tue, 09 Oct 2007 06:00:09 GMT

hi,

I suggest you to use FOR ALL ENTRIES since you are joining 4 tables

the following is sample code....

select data from vbak into it_vbak

then .. select data from vbap into it_vbap

for all entries in it_vbak

where vbeln = it_vbak-vbeln.

then .. select data from knvv into it_knvv

for all entries in it_vbak

where kunnr = it_vbak.

then .. select data from ztable

for all entreis in it_vbak

where kunnr = it_vbak

at last, loop these internal table into final internal table

reward if useful

regards

sree

Re: Join 4 tables......

Former Member — Wed, 10 Oct 2007 12:10:46 GMT

Hi Sree,

Thanks for the immediate response. Appriciate for your all assistance. Here i am woundering my self becuase of my dought. Here i have 3 internal tables with me with data.

1) I_VBAK (header data)

2) I_VBAP (For Order quantity)

3) I_VBEP (For Confirmed quantity)

Now i need to populate final internal table. So for which table i need maintain looping. Because for one record in I_VBAP there may or may not more than one record in I_VBEP. So for which table i need maintain looping. Please clarify me in this situation. Your help will be appriciated..Waiting for reply

Regards,

ravi...