topic Re: Joining multiples tables with structure in Application Development and Automation Discussions

Joining multiples tables with structure

pankaj_adhikari — Sun, 20 Mar 2022 05:39:59 GMT

Hi All

I was practicing ABAP & need some help. I first did inner join between two tables & then was trying to add more tables by taking tables individually as its not good practice to take more than two tables in inner join & I am planning to use 7 tables. I have created structure & added fields there. At present I m getting G/L Description blank in output of report. I will be gradually adding more fields & tables.

Here is my code-

REPORT zven_details_test1.
TABLES: bkpf, acdoca, skat, cskt, cepct, lfa1, kna1, makt.
DATA: it_final TYPE TABLE OF zst_join_test,
 wa_final TYPE zst_join_test.
DATA: wa_fcat TYPE slis_fieldcat_alv,
 it_fcat TYPE slis_t_fieldcat_alv.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.
 SELECT-OPTIONS: s_bukrs FOR bkpf-bukrs,
 s_belnr FOR bkpf-belnr,
 s_gjahr FOR bkpf-gjahr.


SELECTION-SCREEN END OF BLOCK b1.

SELECT a~bukrs
 a~belnr
 a~gjahr
 a~blart
 a~bldat
 a~budat
 a~monat
 a~awkey
 a~awtyp
 a~waers
 b~awitem
 b~rbukrs
 b~rwcur
 b~rhcur
 b~rkcur
 b~racct
 b~rcntr
 b~prctr
 b~lifnr
 b~kunnr
 b~mwskz
 b~wsl
 b~hsl
 b~ksl
 b~matnr
 b~rldnr
FROM bkpf as a
INNER JOIN acdoca AS b
ON a~bukrs = b~rbukrs AND
a~belnr = b~belnr AND
a~gjahr = b~gjahr
INTO TABLE it_final
WHERE a~bukrs IN s_bukrs
AND a~belnr IN s_belnr
AND a~gjahr IN s_gjahr.

SELECT saknr, txt20 from skat
INTO TABLE @data(it_skat)
FOR ALL ENTRIES IN @it_final
WHERE saknr EQ @it_final-racct.
LOOP AT it_final into wa_final.
READ TABLE it_skat INTO data(wa_skat) WITH KEY saknr = wa_final-racct BINARY SEARCH.
wa_final-txt20 = wa_skat-txt20.
APPEND wa_final TO it_final.
CLEAR: wa_final, wa_skat.
ENDLOOP.
IF sy-subrc <> 0.
 MESSAGE 'Records not found' TYPE 'I'.
 LEAVE LIST-PROCESSING.
ENDIF.
DATA: lv_col TYPE i VALUE 0.


CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
 EXPORTING
* I_PROGRAM_NAME =
* I_INTERNAL_TABNAME =
 i_structure_name = 'ZST_JOIN_TEST'
* I_CLIENT_NEVER_DISPLAY = 'X'
* I_INCLNAME =
* I_BYPASSING_BUFFER =
* I_BUFFER_ACTIVE =
 CHANGING
 ct_fieldcat = it_fcat
* EXCEPTIONS
* INCONSISTENT_INTERFACE = 1
* PROGRAM_ERROR = 2
* OTHERS = 3
 .
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
 EXPORTING
 i_callback_program = sy-repid "Program name
 i_callback_user_command = 'HANDLE_USER_COMMAND'
 it_fieldcat = it_fcat
 TABLES
 t_outtab = it_final "Final output table
 EXCEPTIONS
 program_error = 1
 OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
* MESSAGE 'Report not Generated' TYPE 'I'.
ENDIF.
*ENDIF.
FORM handle_user_command USING r_ucomm LIKE sy-ucomm
 rs_selfield TYPE slis_selfield.
 IF r_ucomm EQ '&IC1'
 AND rs_selfield-fieldname EQ 'BELNR'.
 READ TABLE IT_final INTO DATA(wa_final_tmp) INDEX rs_selfield-tabindex.
 IF sy-subrc EQ 0.
 SET PARAMETER ID 'BLN' FIELD wa_final_tmp-belnr.
 SET PARAMETER ID 'BUK' FIELD wa_final_tmp-bukrs.
 SET PARAMETER ID 'GJR' FIELD wa_final_tmp-gjahr.
 CALL TRANSACTION 'FB03' AND SKIP FIRST SCREEN.
 ENDIF.
 ENDIF.
ENDFORM.

I m 100% sure that the issue is in my loop & read statement. I guess I know what to do when declaring all tables invidually but not sure how to do with structure. Here in my case the structure is zst_join_test.

Regards

Pankaj

Re: Joining multiples tables with structure

Sandra_Rossi — Sun, 20 Mar 2022 08:16:07 GMT

Please edit your question: select your ABAP code and click the button CODE so that it's nicely displayed and we can read it easily.

Re: Joining multiples tables with structure

Sandra_Rossi — Sun, 20 Mar 2022 08:17:10 GMT

Who said that it was not good practice to join more than 2 tables?

Re: Joining multiples tables with structure

Sandra_Rossi — Sun, 20 Mar 2022 08:18:07 GMT

What is your issue?

Re: Joining multiples tables with structure

pankaj_adhikari — Sun, 20 Mar 2022 10:12:37 GMT

Thanks Sandra. I have edited question so that it is in proper format. I read at many places it's not good practice to use inner join in more than 2 tables. My issue is I m not able to get data of G/L description.

Regards

Pankaj

Re: Joining multiples tables with structure

Sandra_Rossi — Sun, 20 Mar 2022 11:40:49 GMT

Okay, you probably read some obsolete discussions, myths, among which probably there were some true things but in very specific contexts. As a rule-of-thumb, you can use much more than 2 tables in a join, basically there's no reason to limit the number. Database systems are very much efficient, provided that you configure the database correctly.

For All Entries is no more recommended as rule-of-thumb, especially with high volumes.

(if you use FAE, don't forget to test IF it_final IS NOT INITIAL before SELECT, otherwise it will read the whole database table)

BINARY SEARCH should be avoided, use a SORTED or HASHED internal table instead.

BINARY SEARCH is prone to errors (in case the internal table has not been correctly sorted before searching). It's probably your error as I don't see any SORT (but if you use a SORTED or HASHED table, you won't have to SORT explicitly, nor use BINARY SEARCH).

Don't forget to select only texts in the current language (SY-LANGU).

And add IF sy-subrc = 0 after every read.

REUSE_ALV_GRID_DISPLAY should be considered obsolete. Instead, use CL_SALV_TABLE.