inner join

Former Member — Sun, 27 May 2007 05:33:59 GMT

Hi experts,

can we join any no.of tables using inerjoins?if yes what is the max. no of tables that can be joined using innerjoins?

thks in advance.

Re: inner join

amit_khare — Sun, 27 May 2007 07:00:22 GMT

You can join maximum (suggested) of 4 tables using innerjoin.

Regads,

Amit

Reward all helpful replies.

Re: inner join

Former Member — Sun, 27 May 2007 18:00:56 GMT

Its better to join max 3 tables all using primary keys... in case you dont have primary keys matching.. always better to select seperately....

Points in case you feel relevance

Re: inner join

Former Member — Sun, 27 May 2007 20:28:16 GMT

there is no limit for inner joins and if you want to get good performance ,use 3 tables otherwise it will take more time. instead of writing inner joins use For all entries and it will have good performance than inner join.

Use primary key in where condition if possible.

Reward Points if it is helpful

Thanks

Seshu

Re: inner join

Former Member — Mon, 28 May 2007 03:58:01 GMT

Hi Venkat,

you can join any number of DB tables but if you join more than 4 tables then there will be a performance issue better join only 2 or 3 DB tables.

Instead of join condition you can use several select also, it would

perform well, when compare to join condition.

Check the following code, here to join two table am using

several select statement instead of join condition.

Its better use this way, which give better performance.

************************************************************************

* INTERNAL TABLE AND WORK AREA FOR THE FIELDS IN BKPF TABLE *

************************************************************************

DATA : BEGIN OF itab_bkpf OCCURS 0,

bukrs LIKE bkpf-bukrs, "Company Code.

gjahr LIKE bkpf-gjahr, "Fiscal Year.

budat LIKE bkpf-budat, "Posting Date in the Document.

belnr LIKE bkpf-belnr, "Accounting document number.

blart LIKE bkpf-blart, "Document Type.

END OF itab_bkpf.

DATA : wa_bkpf LIKE LINE OF itab_bkpf.

************************************************************************

* INTERNAL TABLE AND WORK AREA FOR THE FIEDLS IN BSEG TABLE *

************************************************************************

DATA : BEGIN OF itab_bseg_debit OCCURS 0,

bukrs LIKE bseg-bukrs, "Company Code.

gjahr LIKE bseg-gjahr, "Fiscal Year.

belnr LIKE bseg-belnr, "Accounting Document Number.

buzei LIKE bseg-buzei, "Line Item.

hkont LIKE bseg-hkont, "General Leadger Account.

shkzg LIKE bseg-shkzg, "Credit/Debit Indicator.

wrbtr LIKE bseg-wrbtr, "Amount in Document Currency.

pswsl LIKE bseg-pswsl, "Update Currency for Gen.Ledger

dmbtr LIKE bseg-dmbtr, "Amount in local currency.

sgtxt LIKE bseg-sgtxt, "Item Text.

zuonr LIKE bseg-zuonr, "Assignment Number.

END OF itab_bseg_debit.

************************************************************************

* FINAL OUTPUT INTERNAL TABLE ***********************************************************************

DATA : BEGIN OF itab_output OCCURS 0,

belnr(08),

bukrs(04),

budat LIKE bkpf-budat,

buzei(03),

hkont(07),

blart(02),

shkzg(01),

wrbtr(08),

pswsl(05),

dmbtr(10),

sgtxt(19),

zuonr(10),

END OF itab_output.

************************************************************************

CONSTANTS : c_debit TYPE c VALUE 'S',

c_credit TYPE c VALUE 'H'.

************************************************************************

SELECT-OPTIONS *

************************************************************************

SELECTION-SCREEN BEGIN OF BLOCK input WITH FRAME TITLE text-t01.

SELECT-OPTIONS : s_bukrs FOR bkpf-bukrs.

PARAMETERS : p_year LIKE bkpf-gjahr.

SELECT-OPTIONS : s_budat FOR bkpf-budat,

s_dbacct FOR bseg-hkont,

s_cracct FOR bseg-hkont,

s_amt FOR bseg-dmbtr.

SELECTION-SCREEN END OF BLOCK input.

************************************************************************

* SELECTING RECORDS FROM BKPF TABLE BASED ON THE CONDITION ***********************************************************************

SELECT bukrs gjahr budat belnr blart

FROM bkpf INTO TABLE itab_bkpf

WHERE bukrs IN s_bukrs AND

gjahr EQ p_year AND

budat IN s_budat.

************************************************************************

SELECTING DEBIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *
NUMBER SELECTED FROM BKPF *

************************************************************************

IF NOT itab_bkpf[] IS INITIAL.

SELECT bukrs gjahr belnr buzei

hkont shkzg wrbtr pswsl

dmbtr sgtxt zuonr

FROM bseg INTO TABLE itab_bseg_debit

FOR ALL ENTRIES IN itab_bkpf

WHERE bukrs EQ itab_bkpf-bukrs AND

belnr EQ itab_bkpf-belnr AND

gjahr EQ itab_bkpf-gjahr AND

hkont IN s_dbacct AND

shkzg EQ c_debit AND

dmbtr IN s_amt.

************************************************************************

***********************************************************************

READING THE CREDIT ENTRIES WHICH MATCHES WITH HE CURRENT DOC. NUMBER

***********************************************************************

READ TABLE itab_bseg_credit WITH KEY

bukrs = itab_bseg_debit-bukrs

gjahr = itab_bseg_debit-gjahr

belnr = itab_bseg_debit-belnr BINARY SEARCH.

IF sy-subrc EQ 0.

***********************************************************************

*READING THE POSTING DATE AND DOCUMENT TYPE FOR THE CURRENT DOUCMENT

AND APPENDING THE DEBIT AND CREDIT ENTRIES

***********************************************************************

READ TABLE itab_bkpf INTO wa_bkpf WITH KEY

bukrs = itab_bseg_debit-bukrs

gjahr = itab_bseg_debit-gjahr

belnr = itab_bseg_debit-belnr BINARY SEARCH.

itab_output-belnr = itab_bseg_debit-belnr.

itab_output-bukrs = itab_bseg_debit-bukrs.

itab_output-budat = wa_bkpf-budat.

itab_output-buzei = itab_bseg_debit-buzei.

itab_output-hkont = itab_bseg_debit-hkont.

itab_output-blart = wa_bkpf-blart.

itab_output-shkzg = itab_bseg_debit-shkzg.

itab_output-wrbtr = itab_bseg_debit-wrbtr.

itab_output-pswsl = itab_bseg_debit-pswsl.

itab_output-dmbtr = itab_bseg_debit-dmbtr.

itab_output-sgtxt = itab_bseg_debit-sgtxt.

itab_output-zuonr = itab_bseg_debit-zuonr.

APPEND itab_output.

Regards,

Jackie.

Re: inner join

Former Member — Mon, 28 May 2007 13:54:57 GMT

It is better that we reduce using innerjoins and use the for all entries such that the performance is increased.

Thanks..

Re: inner join

Former Member — Mon, 28 May 2007 14:09:22 GMT

If I recall correctly, I think I've seen standard SAP views with up to eight or eleven tables. I don't think there is a performance hit using a large JOIN.

Rob

Re: inner join

Former Member — Mon, 28 May 2007 14:47:34 GMT

I take it back. I seriously underestimeated the number of joins in some standard SAP views. In 4.7, there are a number of views joining more than 30 tables. See V_TCJ41 for example.

Rob

topic inner join in Application Development and Automation Discussions

inner join

Re: inner join

Re: inner join

Re: inner join

Re: inner join

Re: inner join

Re: inner join

Re: inner join