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

[Former Member]

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.g...

[arivazhagan_sivasamy]

Hi Vamsi,

Use INTO  ORDLIST1 instead of INTO TABLE ORDLIST1.

Arivazhagan S

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[Former Member]

ok thanku May huang....

[former_member386202]

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

[matt]

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.

[Former Member]

Thanku to one and all...