Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Joining multiples tables with structure

pankaj_adhikari
Active Participant
0 Kudos

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

5 REPLIES 5

Sandra_Rossi
Active Contributor
0 Kudos

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.

Sandra_Rossi
Active Contributor
0 Kudos

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

Sandra_Rossi
Active Contributor
0 Kudos

What is your issue?

pankaj_adhikari
Active Participant

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

Sandra_Rossi
Active Contributor

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.