topic Re: Table Joins in Application Development and Automation Discussions

Table Joins

Former Member — Fri, 27 Jun 2008 17:24:22 GMT

I ahve joined 4 tables in my report , I just wante dto know is this the right way to do it and if it is not what are the repurcurssions of this kind of joint and if anyone can please tell me what will be the best way to join these tables.

Actually earlier I had just 2 tables joined in this report but unfortunately we had to club 8 fileds from other two tables whaich made me join additional 2 billing tbales to the existing one.

INTO CORRESPONDING FIELDS OF TABLE gt_sel

FROM vbak AS a

INNER JOIN vbap AS b ON a_{vbeln = b}vbeln

INNER JOIN vbkd AS d ON a_{vbeln = d}vbeln

INNER JOIN fplt AS e ON d_{fplnr = e}fplnr

INNER JOIN fpla AS f ON e_{fplnr = f}fplnr

LEFT OUTER JOIN tvlvt AS g ON b_{vkaus = g}abrvw

LEFT OUTER JOIN zsdsched AS c ON b_{vbeln = c}vbeln

AND b_{posnr = c}posnr

Thanks

Re: Table Joins

Former Member — Fri, 27 Jun 2008 17:37:29 GMT

Hello Aarav,

before you join any tables, first analyse the relation between the tables.

And, as a rule of thumb, do not join more than 3-4 tables.

In your case, you've joined 6 tables!--not recommended.

I would suggest you to split the queries into 2:

1st query joining vbak, vbap, vbkd.

2nd : fplt, fpla, etc..

the 1st and 2nd can be connected by the use of FOR ALL ENTRIES option in SAP.

I hope this helps.

Rgds,

Raghu.

Re: Table Joins

Former Member — Fri, 27 Jun 2008 17:38:26 GMT

Hi Arav,

The first thing is using JOINS is not at all performance issue.. It will have a heavy load on DATABASE..

Rather you for FOR ALL ENTERIES addition of SELECT..

This will be very easy and give u a good performance..

Select * from <table1> into ITAB1.

select * 
from   <table2> 
into ITAB2
FOR ALL ENTERIES IN ITAB1
WHERE <FIELD1> EQ ITAB1-<FIELD1>.

This way you do..this will solve your problem

Regards

Narin Nandivada

Re: Table Joins

Former Member — Fri, 27 Jun 2008 17:43:38 GMT

Hi,

Instead of using the JOINS we can go for FOR ALL ENTIRES option in 'Select statement.

First try the values from VBAK based on input selection.

Then for all entires in this VBAK table try to get the vbkd details.

Develop the relations between these tables.

Select * from

vbak into table

t_vbak

where vbeln in s_vbeln.

sort vbak by vbeln.

if t_vbak[] is not initial.

select * from vbkd

into table t_vbkd

for all entires in t_vbak

where

vbeln eq t_vbak-vbeln.

endif.

like this try to relate the other tables, you can get the reuiqred info.

Reward if helpful.

Chandralekha

Re: Table Joins

Former Member — Fri, 27 Jun 2008 17:50:36 GMT

So from each of teh six tables I will have to do the same thing and bring the values into an internal table individually , then realte the internal tables and get the value, right, like I have a very big output to show so I will have to do it separately and then use for all entreis to realte them together. Right. Right now I have used a diffrent approach if you look down in my part of code, do dose that mean I have to change it all and then do the way you suggested.?

IF  p_rental = 'X' .

  SELECT a~vbeln a~erdat a~ernam a~audat a~vbtyp a~auart a~vkorg
   a~vtweg a~spart a~vkbur a~guebg a~gueen a~kunnr a~kvgr4
   b~posnr b~matnr b~charg b~matkl b~arktx b~pstyv b~zmeng
   b~zieme b~kdmat b~werks b~lgort b~vstel b~netpr b~kpein
   b~vkaus b~aufnr b~kmein b~shkzg b~oid_extbol b~oid_miscdl
   b~oidrc b~oid_ship AS kunwe b~zzwprofid c~datbi c~datab b~route
   e~fkdat e~nfdat e~fakwr e~fksaf e~afdat
   f~fpart f~bedat f~endat f~horiz g~bezei


     INTO CORRESPONDING FIELDS OF TABLE gt_sel
     FROM vbak AS a
         INNER JOIN vbap AS b ON a~vbeln = b~vbeln
         INNER JOIN vbkd AS d ON a~vbeln = d~vbeln
         INNER JOIN fplt AS e ON d~fplnr = e~fplnr
         INNER JOIN fpla AS f ON e~fplnr = f~fplnr
         LEFT OUTER JOIN tvlvt AS g ON b~vkaus = g~abrvw
         LEFT OUTER JOIN zsdsched AS c ON b~vbeln = c~vbeln
                                      AND b~posnr = c~posnr


     WHERE a~vbeln IN s_vbeln
       AND a~erdat IN s_erdat
       AND a~vbtyp = gc_g
       AND a~ernam IN s_ernam
       AND a~vkorg IN s_vkorg
       AND a~vtweg IN s_vtweg
       AND a~spart IN s_spart
       AND a~vkbur IN s_vkbur
       AND a~guebg IN s_guebg
       AND a~gueen IN s_gueen
       AND a~auart IN s_auart
       AND a~kunnr IN s_kunnr
       AND b~oid_ship IN s_kunwe
       AND b~werks IN s_werks
       AND b~lgort IN s_lgort
       AND b~vstel IN s_vstel
       AND b~route in s_route
       AND b~matnr IN s_matnr
       AND e~afdat IN s_afdat
       AND e~fksaf IN s_fksaf
       AND b~pstyv in s_pstyv.

Thanks

Re: Table Joins

Former Member — Fri, 27 Jun 2008 18:19:42 GMT

Before you go and start putting tables into internal tables, be aware of the memory restrictions on your system. First, I would hope that your basis person did put a restriction on the work processes. Typically, we limit DIA processes to 1.5GB and BTC to 4GB. Why? Because if every code out there sticks everything in memory, we can have hundreds of instances of 1 single user bringing a SAP instance crashing down.

Now to your code, basically I see 6 tables:

vbak

vbap

vbkd

fplt

fpla

First thing, which is the smallest table? Which is the one where least amount of constant data will be read? Take that table(s) and follow the instructions above and read it all into an internal table. Please be aware of scalability as well, if the table is small today it doesn't mean it will be small tomorrow.

Second, what about the larger tables? Try SELECT ENDSELECT into a temp internal table but with a PACKAGE SIZE parameter to avoid single-record selects (slow).

Lastly, look at what you have done so far. Check to see if everything is scalable and will last a long time (no maint required), and as an side-effect your program should run faster.

Re: Table Joins

Former Member — Fri, 27 Jun 2008 18:22:15 GMT

Oh, and also, joins between these two sets of tables shouldn't be that bad:

vbak and vbap and vbkd

fplt and fpla

sometimes, it's also nice to let the DB do some work.

Re: Table Joins

Former Member — Fri, 27 Jun 2008 19:14:17 GMT

well so far even the joins that I have made is not giving me any problem at all , even with performance , its good, the only thing I am worried about is the loss of data which might be happeningby the join. May be some data might be lost when I join these 6 tavbles at a time instead of putting thenm in the internal table as you all had explained, I am just curious to know will it loose some data while we joion and try taking out the output?

Thanks

Re: Table Joins

JanStallkamp — Fri, 27 Jun 2008 21:43:18 GMT

Hi.

First of all: Even if you get hundereds of answers that you should replace every JOIN by a FOR ALL ENTRIES, don't do that in all cases. It's one of the urban ledgends of SDN that FOR ALL ENTRIES is better in all cases. As far as I can tell in the majority of cases JOIN is faster. Please search for JOIN and FOR ALL ENTRIES, there has been a nice blog about that and some days ago Gareth started a thread here in the forum about that topic.

If you split up your JOIN command into different statements and load everything into internal tables you might increase data transfer massively. That can be a real performance problem!

I don't have analyzed your special case but if you are really interested in performance issues do some tests.

/Jan

Re: Table Joins

Former Member — Sat, 28 Jun 2008 03:58:01 GMT

Hi,

Instead of JOIN use SELECT statements only.

First select Header table records into an internal table T_VBAK and then SELECT item details using FOR ALL ENTRIES IN TABLE T_VBAK.

Regards ,

Rajitha.

Re: Table Joins

Former Member — Sat, 28 Jun 2008 04:13:14 GMT

Hi Aarav,

In your select query u have joined five tables. VBAK VBAP VBKD FPLT FPLA. But when we see the relation between these tables VBAK VBAP & VBKD has some relation. FPLT & FPLA has some relation. Whereas VBAK VBAP VBKD got no relation with FPLT FPLA. So, you write two joins one with tables VBAK,VBAP VBKD and the other with FPLT FPLA. I think this solves the issue.

Regards,

Swapna.

Re: Table Joins

Former Member — Sat, 28 Jun 2008 11:06:42 GMT

the performance depends on the table size and the no of fields used in the jion ...for less than 3 tables ...the join or for all entrie is same..when the fields r increasing the join had poor performance....means fields and performce is inversly proportional...

check this example for the for all entries usage by many table..

report ztest.

tables:pa0002,pa0008,pa0021,pa0041.

data: begin of itab occurs 0,

pernr like pa0002-pernr,

vorna like pa0002-vorna,

nachn like pa0002-nachn,

end of itab.

data: begin of itab1 occurs 0,

pernr like pa0008-pernr,

begda like pa0008-begda,

stvor like pa0008-stvor,

ansal like pa0008-ansal,

end of itab1.

data :begin of itab2 occurs 0,

pernr like pa0021-pernr,

favor like pa0021-favor,

fanam like pa0021-fanam,

end of itab2.

data:begin of itab3 occurs 0,

pernr like pa0041-pernr,

dar01 like pa0041-dar01,

dat01 like pa0041-dat01,

end of itab3.

data:begin of final occurs 0,

pernr like pa0002-pernr,

vorna like pa0002-vorna,

nachn like pa0002-nachn,

begda like pa0008-begda,

stvor like pa0008-stvor,

ansal like pa0008-ansal,

favor like pa0021-favor,

fanam like pa0021-fanam,

dar01 like pa0041-dar01,

dat01 like pa0041-dat01,

end of final.

select-options:s_pernr for pa0002-pernr.

select pernr

vorna

nachn

from pa0002

into table itab

where pernr in s_pernr.

select pernr

begda

stvor

ansal

from pa0008

into table itab1

for all entries in itab

where pernr = itab-pernr.

select pernr

favor

fanam

from pa0021

into table itab2

for all entries in itab1

where pernr = itab1-pernr.

select pernr

dar01

dat01

from pa0041

into table itab3

for all entries in itab2

where pernr = itab2-pernr.

loop at itab.

final-pernr = itab-pernr.

final-vorna = itab-vorna.

final-nachn = itab-nachn.

read table itab1 with key pernr = itab-pernr.

final-begda = itab1-begda.

final-stvor = itab1-stvor.

final-ansal = itab1-ansal.

read table itab2 with key pernr = itab1-pernr.

final-favor = itab2-favor.

final-fanam = itab2-fanam.

read table itab3 with key pernr = itab2-pernr.

final-dar01 = itab3-dar01 .

final-dat01 = itab3-dat01.

append final.

clear final.

endloop.

loop at final.

write:final-pernr ,

final-vorna ,

final-nachn ,

final-begda ,

final-stvor ,

final-ansal ,

final-favor ,

final-fanam ,

final-dar01 ,

final-dat01 .

endloop.

Re: Table Joins

Clemenss — Sat, 28 Jun 2008 13:14:43 GMT

Hi,

just an additional commentary:

You never need the alias addition AS if you do not JOIN a table on itself. You gain much transparency, i.e.


...
FROM vbap JOIN vbak ON vbap~vbeln = vbaK~vbeln

It will help you and others to read and understand your program.

The rule of thumb not to join too many tables means that the database is usually much slower than ABAP. On the other hand, the use of FOR ALL ENTRIES means that you may use a lot of memory. In any case you should make sure that you fetch only the fields you really need and that the joins do not create to many duplicate records.

Regards,

Clemens

Re: Table Joins

Former Member — Sat, 28 Jun 2008 13:25:20 GMT

Hi,

Instead of so many join condition, go for ALL ENTRIES of itab

and take one more interbal table....

it will surely reduce the load on database..

with luck,

pritam.

Re: Table Joins

Former Member — Wed, 02 Jul 2008 15:27:40 GMT

I have joined these two sets of tables as suggested and stored into two different internal tables gt_sel and gt_sel1 .

Can anyone tell me how do I join these two sets of internal table into one so that I get the data into one final table which I can show as output as there is no condition wherin I can use to co-relate them.

Thanks

  SELECT a~vbeln a~erdat a~ernam a~audat a~vbtyp a~auart a~vkorg
   a~vtweg a~spart a~vkbur a~guebg a~gueen a~kunnr a~kvgr4
   b~posnr b~matnr b~charg b~matkl b~arktx b~pstyv b~zmeng
   b~zieme b~kdmat b~werks b~lgort b~vstel b~netpr b~kpein
   b~kmein b~shkzg b~oid_extbol b~oid_miscdl b~oidrc b~oid_ship AS kunwe
   b~zzwprofid c~datbi c~datab b~route
     INTO CORRESPONDING FIELDS OF TABLE gt_sel
     FROM vbak AS a
         INNER JOIN vbap AS b ON a~vbeln = b~vbeln
         inner join vbkd as d on b~vbeln = d~vbeln
         LEFT OUTER JOIN zsdsched AS c ON b~vbeln = c~vbeln AND
                               b~posnr = c~posnr
     WHERE a~vbeln IN s_vbeln
       AND a~erdat IN s_erdat
       AND a~vbtyp = gc_g
       AND a~ernam IN s_ernam
       AND a~vkorg IN s_vkorg
       AND a~vtweg IN s_vtweg
       AND a~spart IN s_spart
       AND a~vkbur IN s_vkbur
       AND a~guebg IN s_guebg
       AND a~gueen IN s_gueen
       AND a~auart IN s_auart
       AND a~kunnr IN s_kunnr
       AND b~oid_ship IN s_kunwe
       AND b~werks IN s_werks
       AND b~lgort IN s_lgort
       AND b~vstel IN s_vstel
       and b~route in s_route
       AND b~matnr IN s_matnr.
       
   SORT gt_sel.    
*-----
select e~fkdat e~nfdat e~fakwr e~fksaf e~afdat
   f~fpart f~bedat f~endat f~horiz g~bezei
   into corresponding fields of table gt_sel1
   from fplt as e
     inner join fpla as f on e~fplnr = f~fplnr
     
     where   e~fksaf in s_fksaf .
      sort gt_sel1.

Thanks

Re: Table Joins

Former Member — Wed, 02 Jul 2008 15:32:39 GMT

Hello Aarav,

U've to find a condition which links them, other wise, how were U planning to join all the tables in one shot?

Find a relation, loop at one itab, read the other with key and move the corresponding entries into the final table, say it_final.

plz assign points, if this helps.

Rgds,

Raghu.

Re: Table Joins

Former Member — Wed, 02 Jul 2008 16:06:14 GMT

The realtion between the two sets of joins is just one vbkd-fplnr and fplt-fplnr. But I am not aware how to join these two internal tables with this relation .

Thanks

Re: Table Joins

Former Member — Mon, 07 Jul 2008 20:10:07 GMT