topic Re: Select Data from 2 intern tables in Application Development and Automation Discussions

Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 08:47:12 GMT

Hi Experts,

how I can select Data from 2 intern Tables into another intern table?

For Example:

My Result Table has the fields: mandt, user, ID, ID_Name.

My select table no. 1 has the fields mandt, XYZ (like A_Name), ID, ID_Name, ...

My select table no. 2 has the fields mandt, A_Name, User, ...

I want to search for all entries in select table no. 1 and 2. where are a_name have the same worth.

How I can select my Dates?

Regards,

Mike

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 08:50:17 GMT

Hi Mike,

You can use join on 2 tables...

Check SAP Help for writing join...or search in the forum you will find the code snippet for the same....

Regards,

Kunjal

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 08:52:12 GMT

hii

you can do it by using for all entries and with READ statement ..do like follow code

IF i_marc[] IS NOT INITIAL.

    SELECT matnr                       " Material Number
           werks                       " Plants
           lgort                       " Storage Location
      FROM mard
      INTO TABLE i_mard
       FOR ALL ENTRIES IN i_marc
     WHERE matnr EQ i_marc-matnr
       AND werks EQ i_marc-werks
       AND lgort IN s_lgort.

  ENDIF.                               " IF i_mara[] IS NOT INITIAL


  IF sy-subrc EQ 0.
    LOOP AT i_output INTO wa_output.
      READ TABLE i_mard INTO wa_mard WITH KEY matnr = wa_output-matnr.
      wa_output-lgort = wa_mard-lgort.
      MODIFY i_output FROM wa_output.
      CLEAR wa_output.
    ENDLOOP.                           " LOOP AT i_output
  ENDIF.                               " IF sy-subrc EQ 0

regards

twinkal

Re: Select Data from 2 intern tables

matt — Tue, 26 Aug 2008 08:54:01 GMT

Well, assuming that you do actually mean internal tables rather than transparent tables as other responders seem to think:

Assuming table 2 is smaller than table 1, and that A_Name is unique in table 2 (if not, use a SORTED table).

Tab3 is your destination table.

First make sure you use a TYPES declaration to define the type of table 2.

Then declare table 2 as

DATA: tab2 TYPE HASHED TABLE OF tab2_type WITH UNIQUE KEY a_name.

Process the data like this:

LOOP AT tab1 ASSIGNING <ls_wa1>.
  MOVE-CORRESPONDING <ls_wa1> INTO ls_wa3.
  READ TABLE tab2 WITH TABLE KEY a_name = <ls_wa1>-a_name ASSIGNING <ls_wa2>.
  IF sy-subrc IS INITIAL.
    MOVE-CORRESPONDING <ls_wa2> INTO ls_wa3.
  ENDIF.
  INSERT ls_wa3 INTO TABLE tab3.
ENDLOOP.

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 08:54:47 GMT

This message was moderated.

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 08:55:35 GMT

Hi mike,

select single <field_name>

from itab1 as t1

inner join on itab2 as t2

into corresponding fields of itab3

where t1_{field1 eq t2}field1.

Note: field1 is a primary key field in both tables.

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 08:55:42 GMT

loop at table1.

move-corresponding table1 to resulttable.

read table table2 with key user = table1-user.

if sy-subrc = 0.

move-corresponding table2 to resulttable.

endif.

append resulttable.

endloop.

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 09:01:48 GMT

Hi,

Use join for this.

Select b~user

a~id

a~id_name

from table1 as a INNER JOIN table2 as b

on a_{A_Name = b}A_Name

into corresponding fields of table itab.

Hope this will solve your purpose.

Regards

Natasha Garg

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 09:22:22 GMT

I have many key fields who I does not have and need in my other intern tables.

How I can avoid that this key fields are requested to provide?

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 09:26:17 GMT

Hi Mike,

If you want to make a "Join", you can only tka ethe fields you need !!

Anyway, if you don't need all fields, you can, at the end moving all your data into another itab ( containing only required fields )

Something like this :


loop at itab.
move-corresponding itab to itab_final.
append itab_final.
endloop.

Hope this helps,

Erwan

Re: Select Data from 2 intern tables

ingo_barschow — Tue, 26 Aug 2008 09:30:46 GMT

Hi,

Mike clearly wrotes "internal tables" so what should he do with all the select / join statements? The only right way is from Matthew Billing. There is no other way to get the corresponding data as to use READ statement or the LOOP ... WHERE statement, but the second solution is much slower than a READ. if possible use a sorted table and BINARY SEARCH with FIELD-SYMBOLS for performance.

LOOP AT itab1 ASSIGNING <ta1> {WHERE ...}.
  READ itab2 ASSIGNING <ta2> WITH KEY a_name = <ta1>-a_name BINARY SEARCH.
  IF  sy-subrc EQ 0.
       lw_tab3-mandt = <ta1>-mandt.
       lw_tab3-xyz     = <ta1>-xyz.
       lw_tab3-id        = <ta2>-id.
       ...
       APPEND lw_tab3 TO itab3.
  ENDIF.
ENDLOOP.

regards.

Re: Select Data from 2 intern tables

Former Member — Tue, 26 Aug 2008 09:43:18 GMT

This message was moderated.

Re: Select Data from 2 intern tables

matt — Tue, 26 Aug 2008 14:47:25 GMT


>, but the second solution is much slower than a READ. ..

If you're referring to my solution, have a second look at it. It's using a HASHED table which is faster than a sorted table and using binary search.

matt

Re: Select Data from 2 intern tables

ingo_barschow — Wed, 27 Aug 2008 07:42:21 GMT

Hi Matt,

I've wrote not clearly. I don't know how the thread-starter wants to read the table and if its necessary to sort the table various for several work-steps. If I have a lot of records and always want the table sorted by the same key, you're right: hashed table is to be preferred. But if I want the table sorted in various ways, I would prefer a standard table and sort it with sort-statement before reading with binary search. The statement + ... but the second solution is much slower than a READ ...+ means a loop is much slower than a (direct) read.

Regards.