topic Re: two internal tables in Application Development and Automation Discussions

two internal tables

waqar_ahmed — Sun, 17 Nov 2013 13:53:38 GMT

Hi All,

I am BW developer and trying to extract data from three tables BKPF, BSAK and REGUP using FM. As I cannot create join on these three because REGUP is cluster table, so I have extracted BKPF and BSAK into internal table. Now I have to fetch LAUFD field from REGUP Table. Please suggest how can I achieve this and get all data into one internal table. Currently I am trying like below, but it won’t help.

ZPAY_DETAILS is defined as structure.

DATA Itab_Payments TYPE TABLE OF ZPAY_DETAILS.
DATA Itab_2 TYPE TABLE OF ZPAY_DETAILS.
DATA Wa_Payments Type ZPAY_DETAILS.

SELECT BSAK~LIFNR BSAK~EBELN BKPF~BELNR BKPF~BLDAT BKPF~CPUDT BSAK~AUGDT BSAK~ZFBDT
BSAK~AUGBL BSAK~BELNR AS ZBELNR BSAK~EBELP BSAK~WRBTR BSAK~WAERS BKPF~BUKRS BKPF~GJAHR

FROM BKPF
INNER JOIN BSAK
ON BKPF~BUKRS = BSAK~BUKRS AND BKPF~GJAHR = BSAK~GJAHR AND BKPF~BELNR = BSAK~BELNR AND BKPF~CPUDT = BSAK~CPUDT
INTO TABLE Itab_Payments
Where BSAK~LIFNR IN PayVend AND
BSAK~EBELN IN PORef AND
BKPF~BLDAT IN InvDat AND
BSAK~ZFBDT IN DueDat AND
BSAK~AUGBL IN DocNum.

Second Select Statement which will fetch LAUFD from REGUP is

Select REGUP~LAUFD from REGUP INTO TABLE Itab_2 FOR ALL ENTRIES IN

Itab_Payments
WHERE REGUP~BELNR = Itab_Payments-BELNR
AND REGUP~BUKRS = Itab_Payments-BUKRS
AND REGUP~GJAHR = Itab_Payments-GJAHR.

Now how do I merge this data from these two internal tables into one internal table? Or if there any work around for this requirement? Please suggest

regards,

Re: two internal tables

venkat_aileni — Sun, 17 Nov 2013 14:10:38 GMT

Hi-

Try below approach:

TYPES: BEGIN OF t_regup,

laufd TYPE regup-laufd,

bukrs TYPE regup-bukrs,

belnr TYPE regup-belnr,

gjahr TYPE regup-gjahr,

END OF t_regup.

DATA: it_regup TYPE STANDARD TABLE OF t_regup,

wa_regup TYPE t_regup.

From second select query:

SELECT laufd

bukrs

belnr

gjahr

FROM regup

INTO TABLE it_regup

FOR ALL ENTRIES IN itab_payments

WHERE bukrs = itab_payments-bukrs

AND belnr = itab_payments-belnr

AND gjahr = itab_payments-gjahr.

LOOP AT itab_payments INTO wa_payments.

CLEAR: wa_regup.

READ TABLE it_regup INTO wa_regup

WITH KEY bukrs = wa_payments-bukrs

belnr = wa_payments-belnr

gjahr = wa_payments-gjahr.

IF sy-subrc = 0.

wa_payments-laufd = wa_regup-laufd.

MODIFY itab_payments FROM wa_payments INDEX sy-tabix.

ENDIF.

ENDLOOP.

-Venkat

Re: two internal tables

Former Member — Sun, 17 Nov 2013 14:12:52 GMT

Hi Ray,

What you have done is almost correct.

Create single a structure( lty_stru ) that is ur how ur final internal table should be( with all the fields.)

create another struc for regup.

create it_lty_struc type tanle of lty_struc.

create it_lty_regup type tanle of lty_regup.

Say lty_struc fields a, b, c, d from (bkpf and bsak) and e, f(regup ).

a,b,c,d can be obained for join statement (it_lty_struc) . and the e and f get from regup with for all entries for same (it_lty_regup).

NOw loop first structure , inside loop use read table for second structure.

You can achieve with little effort.:)

sample code

TABLES : EKKO , EKPO , LFA1.

TYPES : BEGIN OF TY_EKKO,

EBELN TYPE EKKO-EBELN,

BUKRS TYPE EKKO-BUKRS,

BSTYP TYPE EKKO-BSTYP,

BSART TYPE EKKO-BSART,

LIFNR TYPE EKKO-LIFNR,

END OF TY_EKKO.

TYPES : BEGIN OF TY_EKPO,

EBELN TYPE EKPO-EBELN,

EBELP TYPE EKPO-EBELP,

MATNR TYPE EKPO-MATNR,

WERKS TYPE EKPO-WERKS,

LGORT TYPE EKPO-LGORT,

END OF TY_EKPO.

TYPES : BEGIN OF TY_LFA1,

LIFNR TYPE LFA1-LIFNR,

NAME1 TYPE LFA1-NAME1,

END OF TY_LFA1.

TYPES : BEGIN OF TY_FINAL,

EBELN TYPE EKKO-EBELN,

BUKRS TYPE EKKO-BUKRS,

BSTYP TYPE EKKO-BSTYP,

BSART TYPE EKKO-BSART,

LIFNR TYPE EKKO-LIFNR,

EBELP TYPE EKPO-EBELP,

MATNR TYPE EKPO-MATNR,

WERKS TYPE EKPO-WERKS,

LGORT TYPE EKPO-LGORT,

END OF TY_FINAL.

DATA : IT_EKKO TYPE STANDARD TABLE OF TY_EKKO,

IT_EKPO TYPE STANDARD TABLE OF TY_EKPO,

IT_LFA1 TYPE STANDARD TABLE OF TY_LFA1.

DATA : WA_EKKO TYPE TY_EKKO,

WA_EKPO TYPE TY_EKPO,

WA_LFA1 TYPE TY_LFA1.

DATA : IT_FINAL1 TYPE STANDARD TABLE OF TY_FINAL,

IT_FINAL2 TYPE STANDARD TABLE OF TY_FINAL,

IT_FINAL3 TYPE STANDARD TABLE OF TY_FINAL.

DATA : WA_FINAL1 TYPE TY_FINAL,

WA_FINAL2 TYPE TY_FINAL,

WA_FINAL3 TYPE TY_FINAL.

*SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : S_BUKRS FOR EKKO-BUKRS .

SELECT-OPTIONS : S_WERKS FOR EKPO-WERKS .

SELECT-OPTIONS : S_LIFNR FOR LFA1-LIFNR.

*SELECTION-SCREEN : END OF BLOCK B1.

INITIALIZATION.

START-OF-SELECTION.

DATA: START TYPE I,

END TYPE I,

DIF TYPE I.

CLEAR : START , END , DIF.

GET RUN TIME FIELD START.

SELECT A~EBELN A~BUKRS A~BSTYP A~BSART A~LIFNR

B~EBELP B~MATNR B~WERKS B~LGORT

FROM EKKO AS A INNER JOIN EKPO AS B

ON A~EBELN = B~EBELN

INTO TABLE IT_FINAL1

WHERE A~BUKRS in S_BUKRS

AND B~WERKS in S_WERKS

AND A~LIFNR in S_LIFNR.

GET RUN TIME FIELD END.

DIF = END - START.

WRITE: /001 'Time for Join (Header to Item)' ,

055 ':', DIF, 'microseconds'.

CLEAR : START , END ,DIF.

GET RUN TIME FIELD START.

SELECT A~EBELN A~BUKRS A~BSTYP A~BSART A~LIFNR

B~EBELP B~MATNR B~WERKS B~LGORT

FROM EKPO AS B INNER JOIN EKKO AS A

ON A~EBELN = B~EBELN

INTO TABLE IT_FINAL2

WHERE A~BUKRS in S_BUKRS

AND B~WERKS in S_WERKS

AND A~LIFNR in S_LIFNR.

GET RUN TIME FIELD END.

DIF = END - START.

WRITE: /001 'Time for Join (Item to Header)' ,

055 ':', DIF, 'microseconds'.

CLEAR : START , END ,DIF.

GET RUN TIME FIELD START.

SELECT EBELN BUKRS BSTYP BSART LIFNR FROM EKKO

INTO TABLE IT_EKKO

WHERE BUKRS IN S_BUKRS

AND LIFNR IN S_LIFNR.

IF IT_EKKO IS NOT INITIAL.

SORT IT_EKKO BY EBELN.

SELECT EBELN EBELP MATNR WERKS LGORT FROM EKPO

INTO TABLE IT_EKPO

FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN = IT_EKKO-EBELN

AND WERKS IN S_WERKS.

ENDIF.

LOOP AT IT_EKPO INTO WA_EKPO.

READ TABLE IT_EKKO INTO WA_EKKO WITH KEY EBELN = WA_EKPO-EBELN BINARY SEARCH.

IF SY-SUBRC = 0.

WA_FINAL3-EBELN = WA_EKKO-EBELN.

WA_FINAL3-BUKRS = WA_EKKO-BUKRS.

WA_FINAL3-BSTYP = WA_EKKO-BSTYP.

WA_FINAL3-BSART = WA_EKKO-BSART.

WA_FINAL3-LIFNR = WA_EKKO-LIFNR.

WA_FINAL3-EBELP = WA_EKPO-EBELP.

WA_FINAL3-MATNR = WA_EKPO-MATNR.

WA_FINAL3-WERKS = WA_EKPO-WERKS.

WA_FINAL3-LGORT = WA_EKPO-LGORT.

APPEND WA_FINAL3 TO IT_FINAL3.

CLEAR : WA_FINAL3.

ENDIF.

CLEAR : WA_EKKO , WA_EKPO.

ENDLOOP.

GET RUN TIME FIELD END.

DIF = END - START.

WRITE: /001 'Time for For All Entries in',

055 ':', DIF, 'microseconds'.

END-OF-SELECTION.

Thanks

Vijay

Message was edited by: Vijay Vikram

Re: two internal tables

sascha_reissig — Sun, 17 Nov 2013 14:15:45 GMT

Hi Ray,

you should ass in the second select statement BELNR, BUKRS an GJAHR similarly to the first select and then you have to join these both internal tables.

Example:

Select BELNR BUKRS GJAHR LAUFD from REGUP INTO corresponding fields of TABLE Itab_2 FOR ALL ENTRIES IN Itab_Payments
WHERE BELNR = Itab_Payments-BELNR
AND BUKRS = Itab_Payments-BUKRS
AND GJAHR = Itab_Payments-GJAHR.

....

loop at Itab_Payments into ls_payments.

loop at itab2 into ls_itab2

where BELNR = Itab_Payments-BELNR

and BUKRS = Itab_Payments-BUKRS

and GJAHR = Itab_Payments-GJAHR.

move-corresponding ls_payments to WORAKREA.

WORAKREA-LAUFD = ls_itab2-LAUFD.

insert workarea into table LT_RESULT.

endloop.

Hope this helps.

Regards, Sascha

Re: two internal tables

Former Member — Sun, 17 Nov 2013 15:44:57 GMT

Hi,

Since you are accessing the cluster table(REGUP) your code should be like below.

TYPES: BEGIN OF t_ITAB2,

laufd TYPE regup-laufd,

bukrs TYPE regup-bukrs,

LIFNR TYPE REGUP-LIFNR,

belnr TYPE regup-belnr,

gjahr TYPE regup-gjahr,

END OF t_ITAB2.

DATA: itAB_2 TYPE STANDARD TABLE OF t_ITAB2,

WA_REGUP LIKE LINE OF ITAB_2.

FIELD-SYMBOLS: <fS_PAYMENTS> TYPE ZPAY_DETAILS.

*second select statement which will fetch laufd from regup is

select laufd

BUKRS

LIFNR

BELNR

GJAHR

from regup

into TABLE itab_2

for all entries in

itab_payments

where XVORL = ''

AND LIFNR = ITAB_PAYMENTS-LIFNR

AND belnr = itab_payments-belnr

and gjahr = itab_payments-gjahr

and bukrs = itab_payments-bukrs.

LOOP AT ITAB_PAYMENTS ASSIGNING <fS_PAYMENTS>.

READ TABLE ITAB_2 INTO WA_REGUP WITH KEY

LIFNR = <fS_PAYMENTS>-LIFNR

BUKRS = <fS_PAYMENTS>-BUKRS

BELNR = <fS_PAYMENTS>-BELNR

GJAHR = <fS_PAYMENTS>-GJAHR.

IF SY-SUBRC = 0.

<fS_PAYMENTS>-LAUFD = WA_REGUP-LAUFD.

ENDIF.

clear: wa_regup.

ENDLOOP.

Thanks,

Kiran

Re: two internal tables

Former Member — Mon, 18 Nov 2013 05:34:25 GMT

Hii Ray,

The code of your second select statement is not complete ,Do like below

TYPES: BEGIN OF ty_regup,

laufd TYPE laufd,

bukrs TYPE BUKRS,

BELNR TYPE belnr_d,

END OF ty_regup.

DATA: t_regup TYPE STANDARD TABLE OF ty_regup, "Internal table

wa_regup TYPE ty_regup. "Work area

IF NOT itab_payment IS INITIAL.

Select LAUFD bukrs belnr

from REGUP

INTO TABLE t_regup

FOR ALL ENTRIES IN Itab_Payments

WHERE BUKRS = Itab_Payments-BUKRS

AND BELNR = Itab_Payments-BELNR
AND GJAHR = Itab_Payments-GJAHR.

IF sy-subrc EQ 0.

LOOP AT itab_payment INTO wa_payment.

READ TABLE t_regup INTO wa_regup WITH KEY belnr = wa_payment-belnr

bukrs = wa_payment-bukrs

gjahr = wa_payment-gjahr TRANSPORTING laufd BINARY SEARCH.

IF sy-subrc EQ 0.

wa_payment-laufd = wa_regup-laufd. "Make sure your itab_payment has laufd field

MODIFY itab_payment FROM wa_payment.

CLEAR wa_payment.

ENDIF.

ENDLOOP.

ENDIF.

regards

Syed

Re: two internal tables

Former Member — Mon, 18 Nov 2013 06:47:51 GMT

Hi Ray,

Create a final structure and include all the fields which you declared in other structures. In loop at internal table assign all the internal table to final stucture internal table. So that you can get all the fields in final_itab as follows.

types : begin of ty_final,
         matnr type mara-matnr,
         mtart type mara-mtart,
         mbrsh type mara-mbrsh,
         matkl type mara-matkl,
         bismt type mara-bismt,
         meins type mara-meins,
         zeinr type mara-zeinr,
         mfrnr type mara-mfrnr,
         mfrpn type mara-mfrpn,
         ersda type mara-ersda,
         ernam type mara-ernam,
         laeda type mara-laeda,
         aenam type mara-aenam,
         ekgrp type marc-ekgrp,
         dismm type marc-dismm,
         mtvfp type marc-mtvfp,
         bwtty type marc-bwtty,
         werks type mard-werks,
         lgort type mard-lgort,
         maktx type makt-maktx,
         end of ty_final,

         begin of ty_mara,
           matnr type mara-matnr,
           mbrsh type mara-mbrsh,
           mtart type mara-mtart,
           meins type mara-meins,
           matkl type mara-matkl,
           bismt type mara-bismt,
           zeinr type mara-zeinr,
           mfrpn type mara-mfrpn,
           ersda type mara-ersda,
           ernam type mara-ernam,
           laeda type mara-laeda,
           aenam type mara-aenam,
         end of ty_mara,

         begin of ty_marc,
           matnr type marc-matnr,
           werks type marc-werks,
           ekgrp type marc-ekgrp,
           dismm type marc-dismm,
           mtvfp type marc-mtvfp,
           bwtty type marc-bwtty,
         end of ty_marc,

         begin of ty_mard,
           matnr type mard-matnr,
           werks type mard-werks,
           lgort type mard-lgort,
         end of ty_mard,

         begin of ty_makt,
           matnr type makt-matnr,
           maktx type makt-maktx,
         end of ty_makt.

data : it_final type table of ty_final with header line,
        wa_final type ty_final,

        it_mara type table of ty_mara,
        wa_mara type ty_mara,

        it_marc type table of ty_marc,
        wa_marc type ty_marc,

        it_mard type table of ty_mard,
        wa_mard type ty_mard,

        it_makt type table of ty_makt,
        wa_makt type ty_makt,

Regards,

Janaraja

Re: two internal tables

asim_isik — Mon, 18 Nov 2013 07:46:16 GMT

hi ray,

instead of the second select statement use code below

LOOP AT ITAB_PAYMENTS INTO WA_PAYMENTS.

SELECT SINGLE LAUFD FROM REGUP INTO WA_PAYMENTS-LAUFD

WHERE BELNR = WA_PAYMENTS-BELNR

AND BUKRS = WA_PAYMENTS-BUKRS

AND GJAHR = WA_PAYMENTS-GJAHR.

MODIFY ITAB_PAYMENTS FROM WA_PAYMENTS.

ENDLOOP.

Re: two internal tables

waqar_ahmed — Mon, 18 Nov 2013 08:20:40 GMT

Thanks everyone, I will apply above suggestion and see how it goes

regards,