topic Re: join tables w/o using join command in Application Development and Automation Discussions

join tables w/o using join command

Former Member — Thu, 13 Dec 2007 17:22:47 GMT

how can we join two or more tables w/o using join as join generates an performance issues.

Re: join tables w/o using join command

Former Member — Thu, 13 Dec 2007 17:27:44 GMT

Better to use the View which is containing all those tables....

Or....

To avoid join, you need to make them as a separate quries and you can use the FOR ALL ENTRIES.

data: begin of it_mara occurs 0,

matnr type matnr,

mtart type mtart,

end of it_mara.

select matnr mtart

into table it_mara

from mara

where mtart in so_mtart.

if it_mara[] is not initial.

SELECT aufnr

posnr

etenr

werks

matnr

j_3asize

j_4krcat

vdatu

menge

j_3aresm

INTO TABLE t_j3abdsi

FROM j_3abdsi

for all entries in it_mara

WHERE a~matnr IN it_mara-matnr AND

a~werks IN so_werks AND

a~vdatu IN so_vdatu.

endif.

But sometimes For allentries also slower compared to join... so it depends

if more than 2 tables are there

the best way to avoid complex joins,is to create a view which should accomodate all the required fields from all the tables and try to access the view.

initially it may take some time create the view but as far as performance is concern(when you have bulk records) this way'll work fine.

Re: join tables w/o using join command

Former Member — Thu, 13 Dec 2007 17:36:00 GMT

You can use the addition FOR ALL ENTRIES with SELECT. For EG:

*p_matnr is the parameter given in the selection screen

SELECT matnr bdmeng fktxt FROM mara INTO TABLE it_mara where matnr = p_matnr.

*Note the where condition where we specify the table name.

SELECT matnr maktx FROM marc FOR ALL ENTRIES IN it_mara INTO TABLE it_marc where matnr = it_mara-matnr.

*Joining the 2 tables

Loop at it_mara into wa_mara.

Read table it_marc into wa_mara with key matnr = wa_mara-matnr.

wa_joined_table-matnr = wa_mara-matnr.

wa_joined_table-matnr = wa_mara-bdmeng.

wa_joined_table-matnr = wa_mara-fktxt.

wa_joined_table-maktx = wa_marc-maktx.

APPEND wa_joined_table to it_joined_table.

ENDLOOP.

Note: I do not know if there are the fields in BDMENG FKTXT in the table MARA it is only and example.

Please do not forget to award points if this is helpful.

Re: join tables w/o using join command

Former Member — Thu, 13 Dec 2007 17:43:42 GMT

A common misconception. Joins are generally faster than FOR ALL ENTRIES.

Rob

Re: join tables w/o using join command

Former Member — Thu, 13 Dec 2007 17:46:05 GMT

hi Aditya,

With out using join u can use select qurey to join tables. But you have to crate individual internal tables for differnt tables and one final table which contains all the fields of both the tables.

if there are 2 table declare 2 internal tables for each and one it_final internal table consits of all the fileds which are there in both internal table and write select quey for both the tables and get the data in two internal tables and then move this data from internal tables to it_final. After getting all the data in it_final pirint the values taking loop at it_final and endoop.

hope this helps u...

reward if useful

regards,

sunil kariam.

Re: join tables w/o using join command

Former Member — Thu, 13 Dec 2007 17:48:41 GMT

I beg to differ here ROB, joins are only good for upto 3 tables, but after that you will have to use SELECT using FOR ALL ENTRIES if you dont want to lose time

Re: join tables w/o using join command

Former Member — Thu, 13 Dec 2007 17:54:52 GMT

Nope - see:

<a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>

Rob

Re: join tables w/o using join command

former_member156446 — Thu, 13 Dec 2007 20:09:58 GMT

I saw this blog few months before.. I coudn't really come to a conclusion...that its right

Re: join tables w/o using join command

Former Member — Thu, 13 Dec 2007 20:23:59 GMT

You just have to run the sample program.

You can also see:

<a href="http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912">FOR ALL ENTRIES vs DB2 JOIN</a>

rob