topic Re: Queries from the same table, but, different row in Application Development and Automation Discussions

Queries from the same table, but, different row

Former Member — Tue, 06 Jan 2015 02:39:08 GMT

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

Re: Queries from the same table, but, different row

Former Member — Tue, 06 Jan 2015 03:21:46 GMT

This message was moderated.

Re: Queries from the same table, but, different row

rajkumarnarasimman — Tue, 06 Jan 2015 05:09:56 GMT

Hi..

Follow the below steps.

Move the value into internal table. Consider IT_FINAL
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

Re: Queries from the same table, but, different row

matt — Tue, 06 Jan 2015 06:10:38 GMT

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

Re: Queries from the same table, but, different row

Former Member — Tue, 06 Jan 2015 06:30:04 GMT

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.

Re: Queries from the same table, but, different row

Former Member — Tue, 06 Jan 2015 06:56:59 GMT

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