Skip to Content
author's profile photo Former Member
Former Member

Grab data from different tables to build up an internal table efficiently?

In program, we would like to build up an internal table. In each record of the internal table, some field values come from table A, some field values from table B, and some field values are in table C. We would have to use nested loops to build up the internal table? Would be very appreciated if any expert here give us the detailed sample code on how to build up the internal table efficiently and we will give you reward points!

Thanks!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2008 at 11:35 AM

    Hi Kevin,

    Say u want to get the material belonging to all plants and get its material description

    DATA : BEGIN OF IT_MAT OCCURS 0,

    MATNR TYPE MARD-MATNR,

    WERKS TYPE MARD-WERKS,

    MAKTX TYPE MAKT MAKTX,

    END OF IT_MAT.

    SELECT MATNR WERKS MAKTX

    FROM MARD JOIN MAKT ON MARDMATNR = MAKTMATNR

    INTO TABLE IT_MAT WHERE SPRAS = 'E'.

    This will get the material no: andd plant code from table MARD along with

    material description from MAKT

    Thanks Arjun

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2008 at 11:27 AM

    data : begin of itab occurs 0,

    lifnr like zg1_mkpf-lifnr, " Vendor

    g1sno like zg1_mkpf-g1sno, " Gate pass no.

    matnr like zg1_mseg-matnr, " Material

    erdat like zg1_mkpf-erdat, " Creation Date

    ernam like zg1_mkpf-ernam, " User name

    xblnr like zg1_mkpf-xblnr, " DC no.

    bldat like zg1_mkpf-bldat, " DC Date

    budat like mkpf-budat, " GR Date

    werks like zg1_mkpf-werks, " Plant

    swerk like zg1_mkpf-swerk, " Supplying Plant

    gr_made like zg1_mseg-gr_made, " GR Made Indicator

    ival like zg1_mkpf-ival, " Invoice Value

    mblnr like zg1_mseg-mblnr, " Material Doc.

    matdoc like zg1_mkpf-matdoc, " Mateiral Doc.[Stock Transfer]

    mjahr like zg1_mseg-mjahr, " Material Doc. Yr.

    zeile like mseg-zeile,

    rev_ind like zg1_mkpf-rev_ind, " Reversal Date

    menge like zg1_mseg-menge, " Qty Entered @ Gate

    ebeln like zg1_mseg-ebeln, " Po: number

    ebelp like zg1_mseg-ebelp, "PO Line Item

    org_inv like zg1_mkpf-org_inv, "Original Invoice

    rcan like zg1_mkpf-rcan, " Cancellation of Gatepass

    end of itab.

    select ag1sno aerdat aernam axblnr abldat abudat alifnr awerks

    amatdoc aswerk arev_ind aorg_inv a~rev_date

    bmatnr bgr_made bmblnr bmjahr aival dmenge bebeln bebelp b~zeile

    from zg1_mkpf as a inner join zg1_mseg as b

    on ag1sno = bg1sno

    inner join mkpf as c

    on bmblnr = cmblnr inner join mseg as d

    on cmblnr = dmblnr

    into corresponding fields of table itab where c~budat in erdat and

    a~lifnr in lifnr and

    a~org_inv eq 'Y' and

    a~werks in werks and

    b~gr_made = 'X' and

    b~matnr in matnr and

    a~rev_ind ne rev_ind.

    <REMOVED BY MODERATOR>

    KUMAR

    Edited by: Alvaro Tejada Galindo on Mar 17, 2008 7:27 PM

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      hi Mat,

      We are sorry that we have not got a chance to work on your suggestions since we have been tied up with other issues. Now we get back to work on this issue if and if we make it work from your suggestion, we will give you 10 reward points.

      When we check your code, we are kind of confused:

      1. What are the difference between the table b and c for the same field2?

      2. We don't understand the following code that why only field2 in table b is populated?

        LOOP AT it_table_b ASSIGNING .
          READ TABLE it_table_c INTO wa_table_c WITH TABLE KEY ebeln = -ebeln
       ebelp = -ebelp.
          IF sy-subrc = 0.
      -field2 = wa_table_c-field2.
          ENDIF.
        ENDLOOP.
      
      

      Thanks alot!

      Edited by: Kevin Smith on Apr 2, 2008 3:27 PM

  • author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2008 at 11:46 AM

    Hello Kevin

    The approach depends on kind of relations between the tables. If you can use the INNER JOIN and table indexes then maybe the best way is just to use the single select with INNER JOINs.

    If for some reason it is not possible then the best solution is to download all data to internal tables (using FOR ALL ENTRIES or not - also depends on the problem). Then, when having data in internal tables, you should loop on the one that has a "many" end of relationship (i.e. if you have header and items table you should do the loop on the items table) and read additional data using READ TABLE statement.

    In the "outer" loop use ASSIGNING and FIELD SYMBOL instead of INTO and work area - for better performance.

    This way you will aviod the nested loops. You should always avoid nested loops when possible. And when it is not possible then you should maximize the performance by using sorted or hashed tables (in the inner loop).

    Also, sorted or hashed tables should be used (when possible) for maximizing the performance of reading the additional data.

    Rgds

    Mat

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Sure I am 😉

      For example

      TYPES: BEGIN OF ty_vbap,
               vbeln TYPE vbeln_va,
               posnr TYPE posnr_va,
               matnr TYPE matnr,
               ernam TYPE ernam, "taken from vbak
             END OF ty_vbap,
             ty_it_vbap TYPE STANDARD TABLE of ty_vbap,
      
             BEGIN OF ty_vbak,
               vbeln TYPE vbeln_va,
               ernam TYPE ernam
             END OF ty_vbak,
             ty_it_vbak TYPE HASHED TABLE OF ty_vbak WITH UNIQUE KEY vbeln.
      
      DATA: lit_vbap TYPE ty_it_vbap,
            lit_vbak TYPE ty_it_vbak,
            lwa_vbak TYPE ty_vbak.
      
      FIELD-SYMBOLS: <fs_vbap> TYPE ty_vbap.
      
      SELECT vbeln posnr matnr 
        INTO TABLE lit_vbap.
        WHERE ....
      
      IF lit_vbap[] IS NOT INITIAL.
        SELECT vbeln ernam
          INTO TABLE lit_vbak
          FOR ALL ENTRIES IN lit_vbap
          WHERE vbeln = lit_vbap-vbeln.
      ENDIF.
      
      LOOP AT lit_vbap ASSIGNING <fs_vbap>.
        READ TABLE lit_vbak INTO lwa_vbak WITH TABLE KEY vbeln = <fs_vbap>-vbeln.
        IF sy-subrc = 0.
          <fs_vbap>-ernam = lwa_vbak-ernam.
        ENDIF.
      ENDLOOP.
      

      Something like that. Wrote it on the page so some typos might be in the code.

      Hashed table is the table when the key fields in every record has been hased into one hash. Thanks to this searching this table is very fast. But, as always 😉, there is one thing - you have to give the whole key to get the record.

      Rgds

      Mat

      Edited by: Mateusz Adamus on Mar 14, 2008 2:21 PM

  • author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2008 at 11:35 AM

    data : begin of itab occurs 0,

    pernr like pa0001-pernr,

    bukrs like pa0001-bukrs,

    nachn like pa0002-nachn,

    vorna like pa0002-vorna,

    LAND1 like pa0006-LAND1,

    end of itab.

    now get the data ... say

    select * from pa0001 into table it_pa0001.

    select * from pa0002 into table it_pa0002.

    select * from pa0006 into table it_pa0006.

    loop at it_pa0001.

    itab-pernr = it_pa0001-pernr.

    itab-bukrs = it_pa0001-bukrs.

    read table it_pa0002 with key pernr = it_pa0001-pernr

    if sy-subrc = 0.

    itab-nachn = it_pa0002-nachn.

    itab-vorna = it_pa0002-vorna.

    endif.

    read table it_pa0006 with key pernr = it_pa0001-pernr

    if sy-subrc = 0.

    itab-land1 = it_pa0006-land1.

    endif.

    append itab.

    clear itab.

    endloop.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2008 at 11:37 AM

    HI KEVIN,

    USE SELECT JOIN FOR THIS...AS YOU WANT TO JOIN 3 TABLES...

    EG..

    TYPES : BEGIN OF ty_bp_data,

    • KNA1

    kunnr TYPE kunnr,

    name1 TYPE name1_gp,

    name2 TYPE name2_gp,

    adrnr TYPE adrnr,

    • KNB1

    bukrs TYPE bukrs,

    altkn TYPE altkn,

    akont TYPE akont,

    knrze TYPE knrze,

    zsabe TYPE dzsabe_d,

    zterm TYPE dzterm,

    zwels TYPE dzwels,

    • BUT000

    type TYPE bu_type,

    bpkind TYPE bu_bpkind,

    bpext TYPE bu_bpext,

    bu_group TYPE bu_group,

    END OF ty_bp_data.

    DATA : it_bp_data TYPE TABLE OF ty_bp_data.

    SELECT akunnr aname1 aname2 aadrnr

    bbukrs baltkn bakont bknrze bzsabe bzterm b~zwels

    ctype cbpkind cbpext cbu_group

    INTO TABLE it_bp_data

    FROM kna1 AS a " TABLE 1

    INNER JOIN knb1 AS b ON " TABLE 2

    akunnr = bkunnr

    INNER JOIN but000 AS c ON " TABLE 3

    akunnr = cpartner

    WHERE a~kunnr IN s_bpart

    AND b~bukrs IN s_bukrs

    AND c~bpkind IN s_bptyp.

    IF sy-subrc <> 0.

    • No need to check sy-subrc

    ENDIF.

    <REMOVED BY MODERATOR>

    SACHIN

    Edited by: Sachin Sawant on Mar 14, 2008 12:38 PM

    Edited by: Alvaro Tejada Galindo on Mar 17, 2008 7:28 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2008 at 11:52 AM

    Hi,

    It is not advisable to use NESTED loops as they tremendously affect the performance.

    Its better to select the data into an internal table and READ those internal tables within the loop.

    Check the code which is infact within a PERFORM.

    FORM BUILD_DATA_TABLES .

    LOOP AT T_VBAP INTO WA_VBAP.

    WA_FINAL-VBELN = WA_VBAP-VBELN .

    WA_FINAL-POSNR = WA_VBAP-POSNR .

    WA_FINAL-KWMENG = WA_VBAP-KWMENG .

    WA_FINAL-WERKS = WA_VBAP-WERKS .

    READ TABLE T_VBUP INTO WA_VBUP WITH KEY VBELN = WA_VBAP-VBELN POSNR = WA_VBAP-POSNR .

    IF SY-SUBRC = 0 .

    WA_FINAL-GBSTA = WA_VBUP-GBSTA .

    WA_FINAL-ABSTA = WA_VBUP-ABSTA .

    *PERFORM check_order_status USING wa_final-vbeln CHANGING wa_final-gbsta .

    ENDIF .

    READ TABLE T_T001W INTO WA_T001W WITH KEY WERKS = WA_VBAP-WERKS .

    IF SY-SUBRC = 0 .

    WA_FINAL-NAME11 = WA_T001W-NAME1 .

    ENDIF .

    READ TABLE T_MAKT INTO WA_MAKT WITH KEY MATNR = WA_VBAP-MATNR .

    IF SY-SUBRC = 0 .

    WA_FINAL-MAKTX = WA_MAKT-MAKTX .

    ENDIF .

    READ TABLE T_TVAPT INTO WA_TVAPT WITH KEY PSTYV = WA_VBAP-PSTYV .

    IF SY-SUBRC = 0 .

    WA_FINAL-VTEXT = WA_TVAPT-VTEXT .

    ENDIF .

    READ TABLE T_VBAK INTO WA_VBAK WITH KEY VBELN = WA_VBAP-VBELN .

    IF SY-SUBRC = 0 .

    WA_FINAL-KUNNR = WA_VBAK-KUNNR .

    WA_FINAL-AUDAT = WA_VBAK-AUDAT .

    WA_FINAL-AUART = WA_VBAK-AUART .

    WA_FINAL-VKORG = WA_VBAK-VKORG .

    WA_FINAL-VTWEG = WA_VBAK-VTWEG .

    READ TABLE T_KNA1 INTO WA_KNA1 WITH KEY KUNNR = WA_VBAK-KUNNR .

    IF SY-SUBRC = 0 .

    WA_FINAL-NAME1 = WA_KNA1-NAME1 .

    ENDIF .

    ENDIF .

    APPEND WA_FINAL TO T_FINAL .

    CLEAR: WA_FINAL,WA_VBAK,WA_VBAP,WA_VBUP,WA_KNA1,WA_MAKT.

    ENDLOOP .

    <REMOVED BY MODERATOR>

    Regards.

    Edited by: Alvaro Tejada Galindo on Mar 17, 2008 7:28 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2008 at 11:35 AM

    hi look this,

    report ztest.

    tables:pa0002,pa0008,pa0021,pa0041.

    data: begin of itab occurs 0,

    pernr like pa0002-pernr,

    vorna like pa0002-vorna,

    nachn like pa0002-nachn,

    end of itab.

    data: begin of itab1 occurs 0,

    pernr like pa0008-pernr,

    begda like pa0008-begda,

    stvor like pa0008-stvor,

    ansal like pa0008-ansal,

    end of itab1.

    data :begin of itab2 occurs 0,

    pernr like pa0021-pernr,

    favor like pa0021-favor,

    fanam like pa0021-fanam,

    end of itab2.

    data:begin of itab3 occurs 0,

    pernr like pa0041-pernr,

    dar01 like pa0041-dar01,

    dat01 like pa0041-dat01,

    end of itab3.

    data:begin of final occurs 0,

    pernr like pa0002-pernr,

    vorna like pa0002-vorna,

    nachn like pa0002-nachn,

    begda like pa0008-begda,

    stvor like pa0008-stvor,

    ansal like pa0008-ansal,

    favor like pa0021-favor,

    fanam like pa0021-fanam,

    dar01 like pa0041-dar01,

    dat01 like pa0041-dat01,

    end of final.

    select-options:s_pernr for pa0002-pernr.

    select pernr

    vorna

    nachn

    from pa0002

    into table itab

    where pernr in s_pernr.

    select pernr

    begda

    stvor

    ansal

    from pa0008

    into table itab1

    for all entries in itab

    where pernr = itab-pernr.

    select pernr

    favor

    fanam

    from pa0021

    into table itab2

    for all entries in itab1

    where pernr = itab1-pernr.

    select pernr

    dar01

    dat01

    from pa0041

    into table itab3

    for all entries in itab2

    where pernr = itab2-pernr.

    loop at itab.

    final-pernr = itab-pernr.

    final-vorna = itab-vorna.

    final-nachn = itab-nachn.

    read table itab1 with key pernr = itab-pernr.

    final-begda = itab1-begda.

    final-stvor = itab1-stvor.

    final-ansal = itab1-ansal.

    read table itab2 with key pernr = itab1-pernr.

    final-favor = itab2-favor.

    final-fanam = itab2-fanam.

    read table itab3 with key pernr = itab2-pernr.

    final-dar01 = itab3-dar01 .

    final-dat01 = itab3-dat01.

    append final.

    clear final.

    endloop.

    loop at final.

    write:final-pernr ,

    final-vorna ,

    final-nachn ,

    final-begda ,

    final-stvor ,

    final-ansal ,

    final-favor ,

    final-fanam ,

    final-dar01 ,

    final-dat01 .

    endloop.

    regards,

    venkat.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.