Having Same table in FOR ALL ENTRIES IN and INTO clause

[Former Member]

Hi Experts,

When I query a database table with a refernce table, can I go head and fill the reference table also in the same SELECT query, instead of taking the trouble to loop over it again  and filling it ?

The scenario is:

I have an internal table i_table of the below structure,

wrkst      TYPE     mara-wrkst,

matnr     TYPE     mara-matnr,

aufnr      TYPE     caufv-aufnr,

Before the query, my table is filled with matnr and aufnr.

Now would it be wrong or would it give any unexpected errors in future if I query like this:

SELECT  wrkst matnr

    FROM mara

     INTO TABLE  i_table

     FOR ALL ENTRIES IN i_table

     WHERE  matnr EQ i_table-matnr.

Note: I tested it and it worked fine. But I wanted to be sure to avoid any dangers of using such a code.

[former_member193464]

when you use into table it replaces the internal table with the new data , so   for sure your aufnr data will be gone in the table i_table.
it will work fine but the previous data in i_table will all be gone . if you want to keep the previous data too then you can use APPENDING instead of INTO but it will append new rows in your internal table and the old rows will remain unchanged .
hope it helps.

[raymond_giuseppi]

That should work, but as the internal table will be refreshed you will lose the entries without correspondences in the database.

(And don't forget to check that the internal table is not empty)

Regards,

Raymond

[Former Member]

Hi Yuvaraj,

               How are you filling the i_table with aufnr and matnr ,for the first time? Coz you may require to use into corresponding fields, which will have performance issues.

Regards

Sindhuja

[former_member585060]

Hi,

    Instead of using the same table, create a similar internal table. Move the first table entries into second table. Use the second table for FOR ALL ENTRIES and append into first table. Free the second table.

i_table_tmp[] = i_table[].

SORT i_table_tmp BY matnr.

SELECT  wrkst

                matnr

                         FROM mara

                         APPENDING TABLE  i_table_tmp

                         FOR ALL ENTRIES IN i_table_tmp

                         WHERE  matnr EQ i_table_tmp-matnr.

FREE i_table_tmp.

Thanks and Regards

Bala Krishna

[Former Member]

Hi Bala,

Even if I do so, I would have the already available entries (AUFNR in this case) and the other fields WRKST and MARA in the destination table without any relation (or multiple entries for MATNR). The SELECT query will just append the data rather than adding them in repective fields,

I was hoping that since we are fetching only two fields, the others wil be unaffected, but it turns out the destination table is getting refreshed.

[former_member585060]

Hi,

    The in that case you have to loop the entries, instead of workarea in loop statement use field symbols for work areas. It will speed up the process and you don't even require to use Modify statement while updating the AUFNR values.

i_table_tmp[] = i_table[].

SORT i_table_tmp BY matnr.

IF NOT i_table_tmp[] IS INITIAL.

SELECT  wrkst

                matnr

                         FROM mara

                         APPENDING TABLE  i_table_tmp

                         FOR ALL ENTRIES IN i_table_tmp

                         WHERE  matnr EQ i_table_tmp-matnr.

ENDIF.

LOOP AT i_table_tmp INTO wa_table_tmp.

  LOOP AT i_table ASSIGNING <fs_table>

                                                   WHERE matnr = wa_table_tmp-matnr.

    <fs_table>-aufnr = wa_table_tmp-aufnr.

  ENDLOOP.

CLEAR : wa_table_tmp.

ENDLOOP.

FREE i_table_tmp.

Thanks & Regards

Bala Krishna

[former_member193464]

you can not do it in one go , you need to use LOOP.
just in your select query use into i_table_temp (instead i_table)
and Loop in i_table assigning <fs_table>.\

Read table i_table_temp into wa_table_temp with key matnr = <fs_table>-matnr.

If sy-subrc EQ 0.

     <fs_table>-wrkst = wa_table_temp-wrkst.

ENDIF.

endloop.

delete your temporary table now.

hope it helps...

[Former Member]

So the query

SELECT  wrkst matnr

    FROM mara

     INTO TABLE  i_table

     FOR ALL ENTRIES IN i_table

     WHERE  matnr EQ i_table-matnr.

will work fine if the table contains no other data than WRKST MATNR ?

I dont wanna face any runtime errors..

[former_member193464]

the query will work fine .... even if it contains other fields , the query will work but logically you will lose data in other fields of your table ( using INTO) as the table will get refreshed before inserting the data back into it.
suppose if you have i_table with only two fields matnr and wrkst  and somehow you have matnr filled in i_table (say by some previous logic) , then you can use your query to fetch wrkst for all those matnr , it would work then without losing any data.

[former_member585060]

Hi,

     Is the i_table used is final output table, if not modify the structure of that table. declare in the following order, this will also improve the SELECT performance on MARA table.

matnr     TYPE     mara-matnr,

wrkst      TYPE     mara-wrkst,

aufnr           TYPE        caufv-aufnr,

As the Order of the MARA table is in that order, define the i_table to that order and change the Order in SELECT statement also in the same way.

Thanks & Regards

Bala Krishna

[Former Member]

HI,

when you use 'into table itab' ,it overwrite the values in internal table itab if table itab is not empty.

so if you want to keep the previous entries of internal table,,,,use 'appending' instead of 'into'.

[Former Member]

If we use 'INTO TABLE' it overwrites existing data in the internal table. so if  required existing data use 'APPENDING' instead of 'INTO TABLE'.

Also in u r select query there is one mistake. pls make use the order of fields in select query should be the same as they reside in database table.

Hope this helps.

[Former Member]

will order the fields, when changed, introduce a performance impact or will it give a wrong output?

[former_member193464]

order of fields in select query should be the same as defined in internal table... when you use into corresponding and there is large amount of fields and data it impacts the performance..

[Former Member]

I agree that the order of fields in the select query should be same as the orders of fields defined in the internal table.

But my question was, is it necessary that we should SELECT from a database table (here MARA) in the same order as defined in the database table (here order of fields in MATNR and WRKST but I m selecting as WRKST and then MATNR)  ?

[former_member585060]

Yes, it should be in the Order in which it is defined in the database table. So declare the internal table according to the order of standard table.

[former_member193464]

no its not necessary to have in database order..
in your internal table also define first wrkst and then matnr ..

[Former Member]

yes it should be in the same as they resided in database table otherwise it causes performance issue even though it gives correct output.

[Former Member]

Hi Mavrick,

1)The fields should be in the same order as they declared in internal table else it gives dump.

2)The order of fields should be same as they defined in database else it causes performance issue

i hope you can understand

[former_member193464]

hi sreenivasulu,

      yes i understand , if you see my above replies, i have said that the order in which select is written should match with internal table declaration, otherwise it may cause dump or atleast the data will be mismatched ....
it was his question that is it necessary that he defines internal table fields exactly as database fields to which i replied that it is not necessary , it may cause performance issues but still not necessary....
also if the fields are not part of primary key of database fields , then it should be in the order of a secondary index if defined....

[Former Member]

Hi,

Try using modify statement transporting only wrkst field with two internal tables.

i_table is your table with AUFNR and MATNR. Declare another internal table t_table.

data: v_index type sy-tabix.

   SELECT wrkst matnr
       FROM mara
       INTO TABLE  t_table
       FOR ALL ENTRIES IN i_table
       WHERE  matnr EQ i_table-matnr.

IF t_table IS NOT INITIAL.
  LOOP AT t_table INTO wa_table.
    v_index = sy-tabix.
    READ TABLE i_table into wa_tab1 WITH KEY matnr = wa_table-matnr.
    IF sy-subrc = 0.
      MODIFY i_table from wa_table INDEX v_index TRANSPORTING wrkst.

      clear: wa_table.
    ENDIF.
  ENDLOOP.

ENDIF.

[Former Member]

Hi All,

Thank you all for your reply.

Now I have a good idea about what to do..