topic Re: Pulling data from multiple tables into one internal table. in Application Development and Automation Discussions

Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 06:37:57 GMT

Hi Experts,

I have a program where i am pulling and displaying data from one table BKPF. Which works fine. Now I want to use the item table BSEG and display follwing fields along with existing fields.

BSEG Fileds :

1. BUZIE

2. BUZID

3. AUGDT

where BELNR is common between header & item tables.

KIndly guide me as to how to about this coding as I am new to ABAP.

Appending is the code that wrote whiel using single table BKPF for your reference

   REPORT ZBKPF1.

TABLES : BKPF ,BSEG.

TYPE-POOLS : SLIS.

DATA : WA_BK TYPE BKPF,
       WA_BS TYPE BSEG,
       IT_BK TYPE STANDARD TABLE OF BKPF,
*       IT_BK TYPE STANDARD TABLE OF Bseg,
       WA_FCAT TYPE SLIS_FIELDCAT_ALV,
       IT_FCAT TYPE STANDARD TABLE OF SLIS_FIELDCAT_ALV,
       L_COUNT TYPE CHAR2.

*PARAMETERS : Com_Code(5) type c,
*             DOC_NUm(10) TYPE c,
*             Year(8) TYPE c.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS : CO_CODE FOR BKPF-BUKRS,
                 DOC_NUM FOR BKPF-BELNR,
                 DOC_YEAR FOR BKPF-GJAHR.
SELECTION-SCREEN END OF BLOCK B1.

START-OF-SELECTION.

PERFORM GET_DATA.

PERFORM DISPLAY_ALV.
*&---------------------------------------------------------------------*
*&      Form GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM GET_DATA .

SELECT * FROM BKPF INTO TABLE IT_BK WHERE BUKRS IN CO_CODE AND BELNR IN DOC_NUM AND GJAHR IN DOC_YEAR.

ENDFORM.                    " GET_DATA
*&---------------------------------------------------------------------*
*&      Form DISPLAY_ALV
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM DISPLAY_ALV .

IF IT_BK[] IS NOT INITIAL.
    CLEAR: WA_FCAT, L_COUNT. REFRESH IT_FCAT.

    WA_FCAT-FIELDNAME = 'BLDAT'.
    WA_FCAT-SELTEXT_L = 'Doc_Date'.
    WA_FCAT-COL_POS = L_COUNT + 1.
    APPEND WA_FCAT TO IT_FCAT.

    WA_FCAT-FIELDNAME = 'BUDAT'.
    WA_FCAT-SELTEXT_L = 'Posting_Date'.
    WA_FCAT-COL_POS = L_COUNT + 1.
    APPEND WA_FCAT TO IT_FCAT.

    WA_FCAT-FIELDNAME = 'CPUDT'.
    WA_FCAT-SELTEXT_L = 'Entry_Date'.
    WA_FCAT-COL_POS = L_COUNT + 1.
    APPEND WA_FCAT TO IT_FCAT.

    WA_FCAT-FIELDNAME = 'CPUTM'.
    WA_FCAT-SELTEXT_L = 'Entry_Time'.
    WA_FCAT-COL_POS = L_COUNT + 1.
    APPEND WA_FCAT TO IT_FCAT.

    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
     EXPORTING
*                      I_INTERFACE_CHECK                 = ' '
*                      I_BYPASSING_BUFFER                = ' '
*                      I_BUFFER_ACTIVE                   = ' '
*                      I_CALLBACK_PROGRAM                = ' '
*                      I_CALLBACK_PF_STATUS_SET          = ' '
*                      I_CALLBACK_USER_COMMAND           = ' '
*                      I_CALLBACK_TOP_OF_PAGE            = ' '
*                      I_CALLBACK_HTML_TOP_OF_PAGE       = ' '
*                      I_CALLBACK_HTML_END_OF_LIST       = ' '
*                      I_STRUCTURE_NAME                  =
*                      I_BACKGROUND_ID                   = ' '
*                      I_GRID_TITLE                      =
*                      I_GRID_SETTINGS                   =
*                      IS_LAYOUT                         =
       IT_FIELDCAT                       = IT_FCAT
*                      IT_EXCLUDING                      =
*                      IT_SPECIAL_GROUPS                 =
*                      IT_SORT                           =
*                      IT_FILTER                         =
*                      IS_SEL_HIDE                       =
*                      I_DEFAULT                         = 'X'
*                      I_SAVE                            = ' '
*                      IS_VARIANT                        =
*                      IT_EVENTS                         =
*                      IT_EVENT_EXIT                     =
*                      IS_PRINT                          =
*                      IS_REPREP_ID                      =
*                      I_SCREEN_START_COLUMN             = 0
*                      I_SCREEN_START_LINE               = 0
*                      I_SCREEN_END_COLUMN               = 0
*                      I_SCREEN_END_LINE                 = 0
*                      I_HTML_HEIGHT_TOP                 = 0
*                      I_HTML_HEIGHT_END                 = 0
*                      IT_ALV_GRAPHICS                   =
*                      IT_HYPERLINK                      =
*                      IT_ADD_FIELDCAT                   =
*                      IT_EXCEPT_QINFO                   =
*                      IR_SALV_FULLSCREEN_ADAPTER        =
*                    IMPORTING
*                      E_EXIT_CAUSED_BY_CALLER           =
*                      ES_EXIT_CAUSED_BY_USER            =
      TABLES
        T_OUTTAB                         = IT_BK
     EXCEPTIONS
       PROGRAM_ERROR                     = 1
       OTHERS                            = 2                             .
*                   IF SY-SUBRC <> 0.
** Implement suitable error handling here
*                   ENDIF.
*    CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
ENDIF.
ENDFORM.                    " DISPLAY_ALV

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 07:04:19 GMT

Experts, can anyone help here....

Re: Pulling data from multiple tables into one internal table.

former_member193464 — Fri, 14 Dec 2012 07:22:00 GMT

Two options: you can use join in your select query to fetc the data from both the tables at once and fill in your final table, join it on the base of BELNR , if you do not know how to use joins try searching for it in SCN there are many discussion and blogs on it...
second option is , first make an internal table for BSEG with required fields.... now write another select query to fech data for the BSEG table using for all entries in table bkpf...
now fill your final table using both the tables ....

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 07:31:09 GMT

Hi ,

First you'll have to make changes for the new fields to be adden in your output table structure.

Secondly, try some thing like this

loop at bseg

read table bkpf with key belnr = bseg-belnr.

if sy-subrc = 0.

populate your output table here.

endif.

endloop.

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 07:34:43 GMT

Hi,

DATA : wa_bk TYPE bkpf,
        wa_bs TYPE bseg,
        it_bk TYPE STANDARD TABLE OF bkpf,
        it_bseg TYPE STANDARD TABLE OF bseg,
*       IT_BK TYPE STANDARD TABLE OF Bseg,
        wa_fcat TYPE slis_fieldcat_alv,
        it_fcat TYPE STANDARD TABLE OF slis_fieldcat_alv,
        l_count TYPE char2.

DATA: BEGIN OF wa_final,
       bldat TYPE bldat,
       budat TYPE budat,
       cpudt TYPE cpudt,
       cputm TYPE cputm,
       buzei TYPE buzei,
       buzid TYPE buzid,
       augdt TYPE augdt,
        END OF wa_final.
DATA: it_final like TABLE OF wa_final.

*PARAMETERS : Com_Code(5) type c,
*             DOC_NUm(10) TYPE c,
*             Year(8) TYPE c.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS : co_code FOR bkpf-bukrs,
                  doc_num FOR bkpf-belnr,
                  doc_year FOR bkpf-gjahr.
SELECTION-SCREEN END OF BLOCK b1.

START-OF-SELECTION.

   PERFORM get_data.

   PERFORM display_alv.
*&---------------------------------------------------------------------*
*&      Form GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM get_data .

   SELECT * FROM bkpf INTO TABLE it_bk WHERE bukrs IN co_code AND belnr IN doc_num AND gjahr IN doc_year.
   SELECT * FROM bseg INTO TABLE it_bseg FOR ALL ENTRIES IN it_bk
     WHERE belnr = it_bk-belnr AND gjahr = it_bk-gjahr AND bukrs = it_bk-bukrs.

   LOOP AT it_bk INTO wa_bk.
     wa_final-bldat = wa_bk-bldat.
     wa_final-budat = wa_bk-budat.
     wa_final-cpudt = wa_bk-cpudt.
     wa_final-cputm = wa_bk-cputm.
     CLEAR wa_bs.
     READ TABLE it_bseg INTO wa_bs WITH KEY bukrs = wa_bk-bukrs
                                            belnr = wa_bk-belnr
                                            gjahr = wa_bk-gjahr.
     IF sy-subrc = 0.

       wa_final-buzei = wa_bs-buzei.
       wa_final-buzid = wa_bs-buzid.
       wa_final-augdt = wa_bs-augdt.
     ENDIF.
     APPEND wa_final TO it_final.
     CLEAR: wa_final, wa_bk.
   ENDLOOP.

ENDFORM.                    " GET_DATA

now add remaining fields to it_fcat and use it_final as outtab.

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 07:35:57 GMT

hi bro,

declare table it_bseg with required fields along with key fields.

SELECT * FROM BKPF INTO TABLE IT_BK

WHERE BUKRS IN CO_CODE AND BELNR IN DOC_NUM AND GJAHR IN DOC_YEAR.

SELECT *

FROM BSEG INTO corresponding fields of TABLE IT_BSEG

for all entries in it_bk

where bukrs = it_bk-bukrs

and belnr = it_bk-bulnr

and gjahr = it_bk-gjahr.

then move the data to the output.

regards

satish chandra

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 07:38:32 GMT

Hi,

First use like this..

First create final itab which contains the fields u require.

SELECT bukrs BELNR GJAHR BLDAT BUDAT FROM BKPF INTO TABLE IT_BK WHERE BUKRS IN CO_CODE

AND BELNR IN DOC_NUM AND GJAHR IN DOC_YEAR.

then

select

BUKRS

BELNR

GJAHR

BUZEI

BUZID

AUGDT

from bseg into it_final

for all entries in IT_BK

where

BUKRS IN IT_BK-CO_CODE

AND BELNR IN IT_BK-DOC_NUM AND GJAHR IN IT_BK-DOC_YEAR

loop the it_final and read the it_bk and populate it into the required fields...

Regards

Ram

after that loop the it_fin and read the table it_final

so u can write like this....

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 08:58:31 GMT

Hello Mazal,

You can try below pseudo code for your requirement.

types : begin of ty_bseg,

belnr type bseg-belnr,

buzei type bseg-buzei,

buzid type bseg-buzid,

augdt type bseg-augdt,

end of ty_bseg.

data : it_bseg type table of ty_bseg,

wa_bseg type ty_bseg.

SELECT * FROM BKPF INTO TABLE IT_BK WHERE BUKRS IN CO_CODE AND BELNR

IN DOC_NUM AND GJAHR IN DOC_YEAR.

if not it_bk is initial.

select belnr BUZEI BUZID AUGDT from bseg into table it_bseg for all entries in it_bk

where belnr = it_bk-belnr.

if sy-subrc eq 0.

sort it_bseg by belnr.

endif.

loop at lt_bk into wa_bk.

read table it_bseg into wa_bseg with key belnr = wa_bk-belnr binary search.

if sy-subrc eq 0.

****Fill your output table with help of wa_bk and wa_bseg***

endif.

endloop.

P.S : It's alwayz gud practice to select only those fields from database tables which are required.

regards,

Deepti

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 09:22:37 GMT

Hi Mazal

First create an internal table for BSEG with the desired fields and a second internal table which will have fields from both (bseg and bkpf ) the tables as per requirement.

select * from BSEG into corresponding fields of table it_BSEG where <Condition>..

select * from BKPF into Corresponding fields of table LT where <field1> = it_bseg-field1.

regards

vaibhav juneja

Re: Pulling data from multiple tables into one internal table.

Former Member — Fri, 14 Dec 2012 11:56:22 GMT

Thanks Ashwatha/Deepti and all for help, your inputs have helped me in making my way forward.