topic loop internaltable inner join insert into only getting last-row? in Application Development and Automation Discussions

loop internaltable inner join insert into only getting last-row?

Former Member — Tue, 28 Jan 2014 07:21:33 GMT

I have a ABAP program where I read a file from the local machine into table (T_EXCEL) with 1 field. In this case this is a list of order numbers.

This is working correctly where I am reading in the list (100 orders). I am then trying to do a loop at this table selecting from 3 tables using inner joins to then insert into a different table.

I am only getting the last record from my table T_EXCEL in my table ORDLIST1

Here is my table def for T_EXCEL:

DATA:

BEGIN OF T_EXCEL OCCURS 0,
VBELN LIKE VBAK-VBELN,
END OF T_EXCEL.

************************** and here is my form I am having the issue with

Form Get_SO_List2.
*
SORT T_EXCEL.
*
LOOP AT T_EXCEL.
*
select VBAK~VBELN VBAK~ERDAT VBAK~ERZET VBAK~ERNAM VBAK~AUART
       VBAK~AUGRU VBAK~VKORG VBAK~VKGRP VBAK~VKBUR VBAK~VDATU
       VBAK~KUNNR VBAK~OBJNR VBAK~FAKSK VBAK~LIFSK VBAK~IHREZ
       VBAK~KVGR5
       VBUK~ABSTK VBUK~LFSTK VBUK~LFGSK
       VBAK~BSTNK VBUK~COSTA
       vbap~posnr vbap~matnr vbap~matwa vbap~werks vbap~vstel
       vbap~kwmeng vbap~abgru vbap~pstyv vbap~route vbap~netpr
       vbap~ktgrm vbap~kondm
         INTO TABLE ORDLIST1
           FROM ( ( VBAK
            INNER JOIN VBUK ON VBUK~VBELN = VBAK~VBELN )
            inner join vbap on vbap~vbeln = vbak~vbeln )
           WHERE VBAK~VBELN = T_EXCEL-VBELN.
append ordlist1.
clear: vbak, vbuk, vbap.
endloop.
loop at ordlist1.
   write:/ 'Ordlist1-SalesOrd - ', ordlist1-salesord.
endloop.

If I place a write statement above the select I do see this is looping through all 100 records, but my table ORDLIST1 only has 1 row populated instead of 100+.

What am I doing wrong?
Thanks!

*******************************************************************************************************
Here is my form where I am looping through T_EXCEL trying to insert the data into my table ORDLIST1.

- See more at: http://www.saptechies.org/loop-internal-table-inner-join-insert-into-only-getting-last-row/#sthash.giDOK2C9.dpuf

Re: loop internaltable inner join insert into only getting last-row?

arivazhagan_sivasamy — Tue, 28 Jan 2014 07:27:51 GMT

Hi Vamsi,

Use INTO ORDLIST1 instead of INTO TABLE ORDLIST1.

Arivazhagan S

Re: loop internaltable inner join insert into only getting last-row?

Former Member — Tue, 28 Jan 2014 07:39:40 GMT

hi vamshi,

Try the below code,

TABLES : VBAK, VBUK, VBAP.

DATA:

BEGIN OF T_EXCEL OCCURS 0,

VBELN LIKE VBAK-VBELN,

END OF T_EXCEL.

select vbeln from vbak into corresponding fields of table t_excel up to 100 rows.

types : begin of ty_ordr,

ERDAT type VBAK-ERDAT,

ERZET type VBAK-ERZET,

ERNAM type VBAK-ERNAM,

AUART type VBAK-AUART,

AUGRU type VBAK-AUGRU,

VKORG type VBAK-VKORG,

VKGRP type VBAK-VKGRP,

VKBUR type VBAK-VKBUR,

VDATU type VBAK-VDATU,

KUNNR type VBAK-KUNNR,

OBJNR type VBAK-OBJNR,

FAKSK type VBAK-FAKSK,

LIFSK type VBAK-LIFSK,

IHREZ type VBAK-IHREZ,

KVGR5 type VBAK-KVGR5,

ABSTK type VBUK-ABSTK,

LFSTK type VBUK-LFSTK,

LFGSK type VBUK-LFGSK,

BSTNK type VBAK-BSTNK,

COSTA type VBUK-COSTA,

POSNR type VBAP-POSNR,

MATNR type VBAP-MATNR,

MATWA type VBAP-MATWA,

WERKS type VBAP-WERKS,

VSTEL type VBAP-VSTEL,

KWMENg type VBAP-KWMENg,

ABGRU type VBAP-ABGRU,

PSTYV type VBAP-PSTYV,

ROUTE type VBAP-ROUTE,

NETPR type VBAP-NETPR,

KTGRM type VBAP-KTGRM,

KONDM type VBAP-KONDM,

end of ty_ordr.

DATA : ORDLIST1 TYPE TABLE OF ty_ordr with header line.

SORT T_EXCEL.

LOOP AT T_EXCEL.

SELECT VBAK~VBELN

VBAK~ERDAT

VBAK~ERZET

VBAK~ERNAM

VBAK~AUART

VBAK~AUGRU

VBAK~VKORG

VBAK~VKGRP

VBAK~VKBUR

VBAK~VDATU

VBAK~KUNNR

VBAK~OBJNR

VBAK~FAKSK

VBAK~LIFSK

VBAK~IHREZ

VBAK~KVGR5

VBUK~ABSTK

VBUK~LFSTK

VBUK~LFGSK

VBAK~BSTNK

VBUK~COSTA

VBAP~POSNR

VBAP~MATNR

VBAP~MATWA

VBAP~WERKS

VBAP~VSTEL

VBAP~KWMENG

VBAP~ABGRU

VBAP~PSTYV

VBAP~ROUTE

VBAP~NETPR

VBAP~KTGRM

VBAP~KONDM

INTO CORRESPONDING FIELDS OF TABLE ORDLIST1

FROM ( ( VBAK

INNER JOIN VBUK ON VBUK~VBELN = VBAK~VBELN )

INNER JOIN VBAP ON VBAP~VBELN = VBAK~VBELN )

WHERE VBAK~VBELN = T_EXCEL-VBELN.

APPEND ORDLIST1.

CLEAR: VBAK, VBUK, VBAP.

ENDLOOP.

LOOP AT ORDLIST1.

ENDLOOP.

Regards,

Sindhuja

Re: loop internaltable inner join insert into only getting last-row?

Former Member — Tue, 28 Jan 2014 07:39:49 GMT

Hi Vamshi,

Either use Appending table in select statement as below.

select VBAK~VBELN VBAK~ERDAT VBAK~ERZET VBAK~ERNAM VBAK~AUART

VBAK~AUGRU VBAK~VKORG VBAK~VKGRP VBAK~VKBUR VBAK~VDATU

VBAK~KUNNR VBAK~OBJNR VBAK~FAKSK VBAK~LIFSK VBAK~IHREZ

VBAK~KVGR5

VBUK~ABSTK VBUK~LFSTK VBUK~LFGSK

VBAK~BSTNK VBUK~COSTA

vbap~posnr vbap~matnr vbap~matwa vbap~werks vbap~vstel

vbap~kwmeng vbap~abgru vbap~pstyv vbap~route vbap~netpr

vbap~ktgrm vbap~kondm

APPENDING TABLE ORDLIST1

FROM ( ( VBAK

INNER JOIN VBUK ON VBUK~VBELN = VBAK~VBELN )

inner join vbap on vbap~vbeln = vbak~vbeln )

WHERE VBAK~VBELN = T_EXCEL-VBELN.

or create a range for T_EXCEL and use this range in SELECT and remove the loop on T_EXCEL which is a better way.

thanks & regards,

Aswath.

Re: loop internaltable inner join insert into only getting last-row?

Former Member — Tue, 28 Jan 2014 08:01:45 GMT

Hi Vamshi,

Please don't put select statement inside Loop, it will have a big performance issue.

You can use below code for reference. Thanks.

SORT t_excel.

SELECT vbak~vbeln vbak~erdat vbak~erzet vbak~ernam vbak~auart
       vbak~augru vbak~vkorg vbak~vkgrp vbak~vkbur vbak~vdatu
       vbak~kunnr vbak~objnr vbak~faksk vbak~lifsk vbak~ihrez
       vbak~kvgr5 vbuk~abstk vbuk~lfstk vbuk~lfgsk
       vbak~bstnk vbuk~costa

       vbap~posnr vbap~matnr vbap~matwa vbap~werks vbap~vstel
       vbap~kwmeng vbap~abgru vbap~pstyv vbap~route vbap~netpr
       vbap~ktgrm vbap~kondm
INTO TABLE ordlist1
FROM ( ( vbak INNER JOIN vbuk ON vbuk~vbeln = vbak~vbeln )
                 INNER JOIN vbap ON vbap~vbeln = vbak~vbeln )
   FOR ALL ENTRIES IN t_excel
WHERE vbak~vbeln EQ t_excel-vbeln.

LOOP AT ordlist1.

WRITE:/ 'Ordlist1-SalesOrd - ', ordlist1-salesord.

ENDLOOP.

Regards,

May.

Re: loop internaltable inner join insert into only getting last-row?

former_member386202 — Tue, 28 Jan 2014 08:02:19 GMT

Hi,

Use APPENDING TABLE ORDLIST1 instead of into table ORDLIST1 or do like following

LOOP AT T_EXCEL.

SELECT VBAK~VBELN

VBAK~ERDAT

VBAK~ERZET

VBAK~ERNAM

VBAK~AUART

VBAK~AUGRU

VBAK~VKORG

VBAK~VKGRP

VBAK~VKBUR

VBAK~VDATU

VBAK~KUNNR

VBAK~OBJNR

VBAK~FAKSK

VBAK~LIFSK

VBAK~IHREZ

VBAK~KVGR5

VBUK~ABSTK

VBUK~LFSTK

VBUK~LFGSK

VBAK~BSTNK

VBUK~COSTA

VBAP~POSNR

VBAP~MATNR

VBAP~MATWA

VBAP~WERKS

VBAP~VSTEL

VBAP~KWMENG

VBAP~ABGRU

VBAP~PSTYV

VBAP~ROUTE

VBAP~NETPR

VBAP~KTGRM

VBAP~KONDM

INTO CORRESPONDING FIELDS OF TABLE ORDLIST2

FROM ( ( VBAK

INNER JOIN VBUK ON VBUK~VBELN = VBAK~VBELN )

INNER JOIN VBAP ON VBAP~VBELN = VBAK~VBELN )

WHERE VBAK~VBELN = T_EXCEL-VBELN.

APPEND ORDLIST1.

CLEAR: VBAK, VBUK, VBAP.

ENDLOOP.

Regards,

Prashant

Re: loop internaltable inner join insert into only getting last-row?

matt — Tue, 28 Jan 2014 08:02:39 GMT

If you take the time to read your code it should become obvious.

LOOP AT t_excel.

SELECT... INTO TABLE ORDLIST1.

APPEND ORDLIST.

ENDLOOP.

What do you think SELECT INTO TABLE does? If you don't know - go read the ABAP help. Similarly go and read the help for APPEND.

(Clue, think about the kind of objects that both statements work with, and how data moves from one place to another).

You've encountered this very simple problem for two reasons.

1) You've not thought about what each step of your program does. This is something that is vital to do if you wish to become a professional programmer

2) You're using tables with header lines. Tables with header lines are obsolete, and quite right too. The problem with tables with header lines is that ORDLIST1 sometimes refers to the header, sometimes to the table. Hence your confusion.

It saddens me that the responses you've got so far are (for the most part) guessing at the solution, but are not guiding you to finding the solution yourself, which to my mind is far more helpful.

In this instance, I suspect a FOR ALL ENTRIES IN t_excel, might be in order. Then, having checked t_excel isn't empty, you could probably do it all in a single select.

Re: loop internaltable inner join insert into only getting last-row?

Former Member — Tue, 28 Jan 2014 08:14:50 GMT

ok thanku May huang....

Re: loop internaltable inner join insert into only getting last-row?

Former Member — Tue, 28 Jan 2014 08:16:49 GMT

Thanku to one and all...