topic Re: Information from multiple tables in Application Development and Automation Discussions

Information from multiple tables

Former Member — Thu, 05 Sep 2013 15:18:43 GMT

Hi experts,

Let’s say i have 3 Tables. I want to compare and change the information contained in Table1 regarding the information contained in both Table 2 and Table 3.

The information that interests me is 1 , and 0 only if no value 1 was found for a material. Example:

Table 1:

MATERIAL	VALUE
AAA	0
BBB	0
CCC	0

Table 2:

MATERIAL	VALUE
AAA	1
BBB	0
CCC	0

Table :

MATERIAL	VALUE
AAA	0
BBB	1
CCC	0

What I expect is that Table 1 will be as following:

MATERIAL	VALUE
AAA	1 (from Table1)
BBB	1 (from Table3)
CCC	0

My BW tables will contain many thousands of data, i am afraid about performance issues.

Do I have to use imbricated loops, or it’s better to use read tables?

Thanks for your support.

Amine

Re: Information from multiple tables

Former Member — Thu, 05 Sep 2013 15:19:05 GMT

Read itable2 with key material = <result_fields>-material binary search transporting no fields.

If sy-subrc = 0.

Loop at itable 2 from sy-tabix into str2.

If str2-value = ‘1’.

<result_fields>-value = ‘1’.

Endif.

Endloop.

Endif.

Read itable3 with key material = <result_fields>-material binary search transporting no fields.

If sy-subrc = 0.

Loop at itable 3 from sy-tabix into str3.

If str3-value = ‘1’.

<result_fields>-value = ‘1’.

Endif.

Endloop.

Endif.

Re: Information from multiple tables

Former Member — Thu, 05 Sep 2013 15:28:55 GMT

hello,

your requirement needs this logic

sort itab1 by material.

sort itab2 by material.

sort itab3 by material.

loop at itab1 into wa_itab1

read table itab2 into wa_itab2 with key material = wa_itab1.

if sy-subrc = 0 and wa_itab2-value not initial.

wa_itab1-value = wa_itab2-value.

modify itab1 from wa_itab1 transporting value.

endif.

read table itab3 into wa_itab3 with key material = wa_itab1.

if sy-subrc = 0 and wa_itab3-value not initial.

wa_itab1-value = wa_itab3-value.

modify itab1 from wa_itab1 transporting value.

endif.

endloop.

best regards,

swanand

Re: Information from multiple tables

Former Member — Thu, 05 Sep 2013 15:50:02 GMT

Please tell me you're using hashed tables! None of his BINARY SEARCH or SORT itab BY whatever needs to happen.

Use this code:

* Move everything from table 2 to table 1.

INSERT LINES OF itab2 INTO TABLE itab1.

LOOP AT itab3 ASSIGNING <line3>.

* Check if the table 3 row exists in table 1.

READ TABLE itab1 WITH KEY material = <line3>-material

TRANSPORTING NO FIELDS.

* If the table 3 row doesn't exist in table 1, add it.

IF sy-subrc <> 0.

INSERT <line3> INTO TABLE itab1.

* If the table 3 row already exists in table 1, and it

* has value 1, modify table 1 to also have that value.

ELSEIF sy-subrc = 0 AND <line3>-value = 1.

MODIFY itab1 FROM <line3>.

ENDIF.

ENDLOOP.

Simple enough, right?

Re: Information from multiple tables

former_member220538 — Fri, 06 Sep 2013 03:33:30 GMT

Hi Amine,

Here tab1,tab2 and tab3 are the tables and <x_tab1> is a field symbol of type table1.

x_tab2 and w_tab3 are workareas.

SORT tab1 BY material ASCENDING.
SORT tab2 BY material ASCENDING.
SORT tab3 BY material ASCENDING.

LOOP AT tab1 ASSIGNING <x_tab1>.
READ TABLE tab2 INTO w_tab2 WITH KEY <x_tab1>-material.

IF sy-subrc EQ 0.
READ TABLE tab3 INTO w_tab3 WITH KEY <x_tab1>-material.

       IF sy-subrc EQ 0.
            CHECK w_tab2-value EQ 1 OR w_tab3-value EQ 1.
            <x_tab1>-value = 1.

ENDIF.

ENDIF.
ENDLOOP.

Regards,

Jeffin

Re: Information from multiple tables

former_member209120 — Fri, 06 Sep 2013 03:57:40 GMT

Hi amine lamkaissi,

Try like this

TYPES : BEGIN OF ty_table,
         a(4) TYPE c,
         b TYPE i,
         END OF ty_table.

DATA : it_1 TYPE TABLE OF ty_table,
        it_2 TYPE TABLE OF ty_table,
        it_3 TYPE TABLE OF ty_table,
        wa_1 TYPE ty_table,
        wa_2 TYPE ty_table,
        wa_3 TYPE ty_table.

wa_1-a = 'AAA'. wa_1-b = ''. APPEND wa_1 TO it_1. CLEAR wa_1.
wa_1-a = 'BBB'. wa_1-b = ''. APPEND wa_1 TO it_1. CLEAR wa_1.
wa_1-a = 'CCC'. wa_1-b = ''. APPEND wa_1 TO it_1. CLEAR wa_1.

wa_2-a = 'AAA'. wa_2-b = '1'. APPEND wa_2 TO it_2. CLEAR wa_2.
wa_2-a = 'BBB'. wa_2-b = '0'. APPEND wa_2 TO it_2. CLEAR wa_2.
wa_2-a = 'CCC'. wa_2-b = '0'. APPEND wa_2 TO it_2. CLEAR wa_2.

wa_3-a = 'AAA'. wa_3-b = '0'. APPEND wa_3 TO it_3. CLEAR wa_3.
wa_3-a = 'BBB'. wa_3-b = '1'. APPEND wa_3 TO it_3. CLEAR wa_3.
wa_3-a = 'CCC'. wa_3-b = '0'. APPEND wa_3 TO it_3. CLEAR wa_3.

SORT : it_1 BY a,
        it_2 BY a,
        it_3 BY a.

LOOP AT it_1 INTO wa_1.

   READ TABLE it_2 INTO wa_2 WITH KEY a = wa_1-a BINARY SEARCH.

   IF sy-subrc = 0.
     IF wa_2-b IS NOT INITIAL.
       wa_1-b = wa_1-b + wa_2-b.
     ENDIF.
   ENDIF.
   READ TABLE it_3 INTO wa_3 WITH KEY a = wa_1-a BINARY SEARCH.
   IF sy-subrc = 0.
     IF wa_3-b IS NOT INITIAL.
       wa_1-b = wa_1-b + wa_3-b.
     ENDIF.
   ENDIF.
   MODIFY it_1 FROM wa_1 TRANSPORTING b.
   CLEAR wa_1.
ENDLOOP.

LOOP AT it_1 INTO wa_1.

   WRITE: / wa_1-a, wa_1-b.
   CLEAR wa_1.
ENDLOOP.

Re: Information from multiple tables

Former Member — Fri, 06 Sep 2013 05:58:07 GMT

All mentioned above code is good. but if value 0 and non 0 is Ok to your next process.

Don`t need sort the three itab.

loop gt_itab2 into gwa_itab2.

collect gwa_itab2 into gt_itab1.

endloop.

loop gt_itab3 into gwa_itab3.

collect gwa_itab3 into gt_itab1.

endloop.

Next process, just judge the value.

if gwa_itab1-value eq 0.

...

elseif.

...

endif.

Regards,

Archer

Re: Information from multiple tables

Former Member — Fri, 06 Sep 2013 06:24:05 GMT

Hi,,

Try like this..

TYPES: BEGIN OF ty_zzz,

       material(3) TYPE c,

       value     TYPE i,

      END OF ty_zzz.

DATA: t_zz1 TYPE TABLE OF ty_zzz,

      t_zz2 TYPE TABLE OF ty_zzz,

      t_zz3 TYPE TABLE OF ty_zzz,

      x_zz TYPE ty_zzz.

LOOP AT t_zz3 INTO x_zz.

MODIFY t_zz1 FROM x_zz TRANSPORTING value WHERE material = x_zz-material.

ENDLOOP.

LOOP AT t_zz2 INTO x_zz.

   MODIFY t_zz1 FROM x_zz TRANSPORTING value WHERE material = x_zz-material.

ENDLOOP.

Re: Information from multiple tables

Former Member — Sat, 07 Sep 2013 17:39:53 GMT

Thanks to all of you guys.

Very interesting approachs.

Amine