Queries from the same table, but, different row

[former_member216218]

Hi, all.

Kindly need your help to write the queries statement,

I have table as below, I want to copy the TRFNO to RFMBELNR based on rfwtno = wtno

I did try to code like this:

DATA: lt_zmmtwinfd TYPE STANDARD TABLE OF zmmtwinfd.

CLEAR: lt_zmmtwinfd.

     SELECT trfno

       INTO TABLE lt_zmmtwinfd

       FROM zmmtwinfd

       WHERE rfwtno = zmmtwinfd-wtno.

     UPDATE zmmtwinfd

        SET pytp = zmmtwinfd-pytp

            pyamt = zmmtwinfd-pyamt

            netwt = zmmtwinfd-netwt

            trfno = zmmtwinfd-trfno

            rfmbelnr = zmmtwinfd-trfno 

            zremk = zmmtwinfd-zremk

            rfwtno = zmmtwinfd-rfwtno 

            pymid = '0'

      WHERE wtno = zmmtwinfd-wtno.

Your kindness is most appreciated.

Thanks and Regards

Liyana

[Former Member]

This message was moderated.

[rajkumarnarasimman]

Hi..

Follow the below steps.

1. Move the value into internal table. Consider IT_FINAL
2. Create Copy the internal table. Consider IT_COPY

          IT_COPY[] = IT_FINAL[]

   3.  Create field symbol and update the entry inside loop

     FIELD-SYMBOLS: <FS_FINAL> LIKE LINE OF IT_FINAL.

     "Run the loop for IT_FINAL

     LOOP AT IT_FINAL ASSIGING <FS_FINAL>.

          "Read the value

          READ TABLE IT_FINAL INTO WA_FINAL WITH KEY WTNO = <FS_FINAL>-RFWTNO.

          IF SY-SUBRC = 0.

                "Update the value

               <FS_FINAL>-RFMBELNR = WA_FINAL-TRFNO.

          ENDIF.

     ENDLOOP.

Hope it helps

Regards

Rajkumar Narasimman

[matt]

Make sure the internal tables are HASHED (or SORTED) with the correct keys.

[Former Member]

1.Create internal table.

    BEGIN OF IT,

        WTNO TYPE ZTABLE-WTNO,

        TRFNO TYPE ZTABLE-TRFNO,

    END OF IT.

2.select WTNO RFWTNO from databse table into IT where WTNO = RFWTNO.

     [i can see only one PK i.e. WTNO].

     [here you have list of rows whoes WTNO = RFWTNO with TRFNO field].

3.loop at it.

    update database table set RFMBELN = IT-RFWTNO WHERE WTNO = IT-WTNO.

  endloop.

4. and it occupies less memory rather to store whole table.

[Former Member]

Hi Hasan,

I can see that with reference to zmmtwinfd-wtno you fetching some records in internal and in update statement you never used those records...what i understand from your question you need to copy value of  TRFNO to RFMBELNR field of all records where the field value rfwtno = WTNO. if my undrestanding is correct try using fallowing code..

if you have wtno from selection screen then

Select single tfrno from ztable into g_tfrno where wtno = s_wtno.

update ztable set rfmbelnr = trfno where rfwtno = wtno.

If you want all wtno in ztable then.

Select wtno tfrno from ztable into table it_ztable.

loop at it_ztable into wa_ztable.

update ztable set rfmbelnr = wa_ztable-trfno where rfwtno = wa_ztable-wtno.

Endloop.

Regards,

Shashikanth