two internal tables

[waqar_ahmed]

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,

[waqar_ahmed]

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,

[venkat_aileni]

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

[Former Member]

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

[sascha_reissig]

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.

endloop.

Hope this helps.

Regards, Sascha

[Former Member]

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

[Former Member]

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.

ENDIF.

regards

Syed

[Former Member]

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

[asim_isik]

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.

[waqar_ahmed]

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

regards,