topic Re: combining data from multiple tables using loops in Application Development and Automation Discussions

combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 14:59:07 GMT

Hi,

I have 4 tables, each of them having a field for link with another table. Table1 is linked with table2, table2 with table3 and table3 with table4. There are different number of rows in all tables.

I need to combine all the fields from these tables in one final table. For the cases in which i have more child rows corresponding to one parent row, in the final table there will be two rows.

Thank you.

Ioan

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:02:58 GMT

Hi folks,

All the tables are internal tables, so the data is already extrated from system tables.

Ioan.

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:03:49 GMT

Use Joins to combine the tables with key fields and to fetch the data from them

see the sample code using joins

select

a~vbeln " Billing Doc Number

a~fktyp " Billing Category

a~vbtyp " Sales Doc category

a~fkdat " Billing doc date

a~fkart " Billing doc type

a~bukrs " Company code

a~kurrf " Exchange rate

a~knumv " Condition record Number

a~waerk " Currency

a~kunag " Sold to Party

b~vrkme " Sales Unit

b~posnr " Item Number

b~charg " Batch Number

b~fkimg " Billed quantity

b~werks " Plant

b~matnr " Material Number

b~netwr " Net Value of Bill Doc

b~wavwr " Cost in Doc Currency

c~kdmat " Customer Material

into table itab_bill

from vbrk as a join vbrp as b

on b_{vbeln = a}vbeln

join vbap as c

on b_{aubel = c}vbeln and

b_{aupos = c}posnr

where a~vbeln in s_vbeln and

a~fkdat in s_fkdat and

a~bukrs in s_bukrs and

a~vtweg in s_vtweg and

a~vkorg in s_vkorg and

a~spart in s_spart and

a~fkart in s_fkart and

b~werks in s_werks and

a~kunag in s_kunag and

a~sfakn eq ' ' and

a~fksto eq ' ' .

If all are internal tables

then loop the first and read all the others in that with keys and append all the tables data into a final internal table

loop at ITAB1.

move-corresponding fields to ITAB1 to itab.

read table itab2 with key f1 = itab1-f2.

if sy-ubrc = o

move the field to final ITAB.

endif.

read table itab3 with key f1 = itab1-f2.

if sy-ubrc = o

move the field to final ITAB.

endif.

read table itab4 with key f1 = itab1-f2.

if sy-ubrc = o

move the field to final ITAB.

endif.

read table itab5 with key f1 = itab1-f2.

if sy-ubrc = o

move the field to final ITAB.

endif.

append ITAB

clear itab.

endloop.

Reward points if useful

Regards

Anji

Message was edited by:

Anji Reddy Vangala

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:03:58 GMT

LOOP AT ITAB1.

*Move all the required fields from ITAB1 TO FINAL_TAB, may be using MOVE-CORRESPONDING if field names are matching.

READ TABLE ITAB2 WITH KEY FIELD1 = ITAB1-FIELD1.

Move all the required fields from ITAB2 TO FINAL_TAB.

READ TABLE ITAB3 WITH KEY FIELD1 = ITAB1-FIELD1.

Move all the required fields from ITAB2 TO FINAL_TAB.

APPEND FINAL_TAB.

ENDLOOP.

Or else, you can write a join query to fetch data from tables.

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:06:27 GMT

Hi loan,

1. this can be one way of working.

a) make one final internal table which will contain the requierd fields

from all the table. Make two tables of such kind.

eg. FinalTab1

FinalTab2.

b. then

*------ Table1 Logic

*----

Loop at table1.

move-corresponding table1 to finaltable1.

appen final table1.

endloop.

*------ Table2 Logic

*----

Loop at finaltable1.

finaltable2 = finaltable1.

Read table2 with key (fields from finaltable1)

if sy-subrc = 0.

Transfer fields of Table2 to finaltable2.

endif.

append finaltable2.

endloop.

*----

finaltable1[] = finaltable2[].

REFRESH FINALTABLE2.

*----

*------ Table3 Logic

*----

Loop at finaltable1.

finaltable2 = finaltable1.

Read table3 with key (fields from finaltable1)

if sy-subrc = 0.

Transfer fields of Table3 to finaltable2.

endif.

append finaltable2.

endloop.

*----

finaltable1[] = finaltable2[].

REFRESH FINALTABLE2.

*----

*------ Table4 Logic

*----

Loop at finaltable1.

finaltable2 = finaltable1.

Read table4 with key (fields from finaltable1)

if sy-subrc = 0.

Transfer fields of Table4 to finaltable2.

endif.

append finaltable2.

endloop.

*----

FINALTABLE2 contains the final data.

Note :

I forgot to mention ,

since more than 1 child record are there,

use LOOP AT TABLE2, 3 , 4

Instead of Just Read

regards,

amit m.

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:06:31 GMT

Hello,

As Darshill said, you have to you READ instructions. If you have a large number of datas, don't forget to you BINARY SEARCH instructions in order to arrange your performances.

Regards,

Mathieu

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:10:44 GMT

U should create a complex table:

TYPES: BEGIN OF LY_TABLE1,
                FIELD1,
                ...........,
             END    OF LY_TABLE1.
TYPES: BEGIN OF LY_TABLE2,
                FIELD1,
                FIELD2,
                ...........,
             END    OF LY_TABLE2.
TYPES: BEGIN OF LY_TABLE3,
                FIELD2,
                FIELD3,
                ...........,
             END    OF LY_TABLE3.
TYPES: BEGIN OF LY_TABLE4,
                FIELD3,
                FIELD4,
                ...........,
             END    OF LY_TABLE4.

TYPES:  TY_ITAB1 TYPE TABLE OF LY_ITAB1,
              TY_ITAB2 TYPE TABLE OF LY_ITAB2,
              TY_ITAB3 TYPE TABLE OF LY_ITAB3,
              TY_ITAB4 TYPE TABLE OF LY_ITAB4.

TYPES: BEGIN OF ST_ITAB34,
               L3 TYPE LY_ITAB3,
               T4 TYPE TY_ITAB4,
             END   OF ST_ITAB34.

TYPES: TY_ITAB34 TYPE TABLE OF ST_ITAB34.

TYPES: BEGIN OF ST_ITAB234,
                L2   TYPE LY_ITAB2,
                T34  TYPE TY_ITAB34,
             END   OF ST_ITAB234.

TYPES: TY_ITAB234 TYPE TABLE OF ST_ITAB234.
 
TYPES: BEGIN OF ST_ITAB1234,
                L1   TYPE LY_ITAB1,
                T234  TYPE TY_ITAB234,
             END   OF ST_ITAB234.

TYPES: TY_ITAB1234 TYPE TABLE OF ST_ITAB1234.

LOOP AT ITAB1.
  REFRESH ITAB1234-T234.
  ITAB1234-L1 = ITAB1.
  LOOP AT ITAB2 WHERE FIELD1 = ITAB1-FIELD1.
    REFRESH ITAB234-T234.
    ITAB234-L2 = ITAB2.
    LOOP AT ITAB3 WHERE FIELD2 = ITAB2-FIELD2.
      REFRESH ITAB34-T4.
      ITAB34-L3 = ITAB3.
      LOOP AT ITAB4 WHERE FIELD3 = ITAB3-FIELD3.
        APPEND ITAB4 TO ITAB34-T4.
      ENDLOOP.
      APPEND ITAB34 TO ITAB234-T34.
    ENDLOOP.
    APPEND ITAB234 TO ITAB1234-T234.
  ENDLOOP.
  APPEND ITAB1234.
ENDLOOP.

Max

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:10:55 GMT

Hi,

What if in ITAB2 i have 3 rows with the same key value? In the final table there must be three rows. In these three rows, the fields corresponding to the first table must have same values.

Ioan

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:17:07 GMT

Loop at the table which has more records with the keyt field and then based on that read the rest of the tables.

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:23:54 GMT

After the first READ using a BINARY SEARCH, you can READ using the index. check this:

/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops

Rob

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 15:33:13 GMT

you can write a loop within loop:

LOOP AT ITAB1.

CLEAR FINAL_TAB.

*Move all the required fields from ITAB1 TO FINAL_TAB, may be using MOVE-CORRESPONDING if field names are matching.

READ TABLE ITAB2 WITH KEY FIELD1 = ITAB1-FIELD1.

Move all the required fields from ITAB2 TO FINAL_TAB.

LOOP AT ITAB3 WHERE FIELD1 = ITAB1-FIELD1.

Move all the required fields from ITAB3 TO FINAL_TAB.

Do not clear the fields which are copied from ITAB1 & ITAB2.

APPEND FINAL_TAB.

ENDLOOP.

ENDLOOP.

Re: combining data from multiple tables using loops

Former Member — Fri, 25 May 2007 16:47:44 GMT

Hi Amit,

It appears that your code is working but when i don't have any child rows in table3 for example, the parent rows from table2 ar not coppied.

Can be done something?

Ioan.