topic Re: problem joining 5 tables in Application Development and Automation Discussions

problem joining 5 tables

Former Member — Thu, 26 Apr 2007 07:26:41 GMT

Hi, I have to modify a report because it goes too slow.. the problem is when joining 5 tables, i'm trying to change it but i'm a little bit "lost".... the original code is de following..

SELECT DISTINCT a_{aufnr a}auart a_{erdat a}ktext a~werks

b_{acpos b}objnr

c_{kokrs c}acpos

d_{bwart d}budat d_{mblnr d}mjahr d_{zeile d}erfmg d~erfme

d_{matnr e}maktx d_{aufnr d}dmbtr d_{kokrs d}sakto

INTO TABLE it_materiales

FROM ( aufk AS a

INNER JOIN pmco AS b

ON b_{objnr = a}objnr

INNER JOIN tpik3 AS c

ON c_{kokrs = a}kokrs

AND c_{acpos = b}acpos

INNER JOIN aufm AS d

ON d_{aufnr = a}aufnr

AND d_{kokrs = c}kokrs

AND d_{sakto = c}kstrf

INNER JOIN makt AS e

ON e_{matnr = d}matnr )

WHERE a~aufnr IN s_aufnr AND

a~auart IN s_auart AND

a~werks IN s_iwerk AND

b~acpos IN s_acpos AND

d~budat IN s_budat AND

e~spras = sy-langu.

to make it go quicklier, i did this:

DATA: it_aufm LIKE aufm OCCURS 0 WITH HEADER LINE,

it_tpik3 LIKE tpik3 OCCURS 0 WITH HEADER LINE,

it_aux LIKE it_materiales OCCURS 0 WITH HEADER LINE,

wa_material LIKE it_materiales.

SELECT DISTINCT a_{aufnr a}auart a_{erdat a}ktext a~werks

b_{acpos b}objnr

INTO CORRESPONDING FIELDS OF TABLE it_materiales

FROM ( aufk AS a

INNER JOIN pmco AS b

ON b_{objnr = a}objnr )

WHERE a~aufnr IN s_aufnr AND

a~auart IN s_auart AND

a~werks IN s_iwerk AND

b~acpos IN s_acpos.

SELECT * INTO TABLE it_tpik3

FROM tpik3

FOR ALL ENTRIES IN it_materiales

WHERE kokrs = it_materiales-kokrs

AND acpos = it_materiales-acpos.

LOOP AT it_materiales INTO wa_material.

LOOP AT it_tpik3 WHERE kokrs = wa_material-kokrs

AND acpos = wa_material-acpos.

MOVE-CORRESPONDING wa_material TO it_aux.

MOVE-CORRESPONDING it_tpik3 TO it_aux.

APPEND it_aux.

CLEAR it_aux.

ENDLOOP.

it_materiales[] = it_aux[].

LOOP AT it_materiales.

READ TABLE it_aufm WITH KEY aufnr = it_materiales-aufnr

kokrs = it_materiales-kokrs

sakto = it_materiales-kstrf.

IF sy-subrc = 0.

IF it_aufm-budat NOT IN s_budat.

DELETE it_materiales.

ELSE.

SELECT SINGLE maktx

FROM makt

INTO it_materiales-maktx

WHERE matnr = it_materiales-matnr

AND spras = sy-langu.

MODIFY it_materiales.

ENDIF.

ENDLOOP.

when acceding table TPIK3 i get no data.... i know i'm doing something wrong but don't know where....

thanks in advance !!!

Re: problem joining 5 tables

Former Member — Thu, 26 Apr 2007 07:47:38 GMT

Hi,

Try this:

1. check join conditions properly.

2. do not use use nested loop command s like

LOOP AT it_materiales INTO wa_material.

LOOP AT it_tpik3 WHERE kokrs = wa_material-kokrs

ENDLOOP.

instead use.

loop at it_material..

read table it_tpik3 with key....

endloop.

3. do not use CORROSPONDING FIELDS option!!!

4. donot use select single in loop, instead fetch all records once in itabl using for all entries.

5. check data of it_material with these condition in table tpik3 :

WHERE kokrs = it_materiales-kokrs

AND acpos = it_materiales-acpos.

Hope this will help you.

Jogdand M B

Re: problem joining 5 tables

Former Member — Thu, 26 Apr 2007 08:08:36 GMT

ok.. i'll make some changes but.... the join sentence i don't know how to do it properly, i've done it as it was done before... no primary key fields...i have done a nested loop because i thought there could be more than one registry for an entry in it_materiales...

and the biggest doubt i have is why it_tpik3 has no data aftee the "select .. for all entries in it_materiales.." ??

thanks...

Re: problem joining 5 tables

Former Member — Thu, 26 Apr 2007 08:11:11 GMT

Hi Maria,

If you are access 5 database table with join, it will affect performance of the report. It always recommand that select from main table and use 'FOR ALL ENTRIES' and then manipulate the information as per required using loop.

Regards

Nilesh Shete