topic Re: reg: join statement in Application Development and Automation Discussions

reg: join statement

Former Member — Thu, 15 May 2008 10:01:54 GMT

Hi,

SELECT a_{belnr a}gjahr a_{buzei a}ebeln a_{ebelp a}bukrs a~werks

a_{wrbtr a}mwskz

b~matkl

FROM rseg AS a

JOIN ekpo AS b

ON a_{ebeln = b}ebeln

AND a_{ebelp = b}ebelp

AND a_{bukrs = b}bukrs

AND a_{werks = b}werks

INTO TABLE tbl_rseg_ekpo

FOR ALL ENTRIES IN tbl_cross

WHERE belnr = tbl_cross-awkey

AND gjahr = tbl_cross-gjahr.

Is it good join statement or not ?

Why Iam doubt is in RSEG ebeln, ebelp are not the key fields

which are key fields in ekpo.

Please give ur suggestions.

Re: reg: join statement

Former Member — Thu, 15 May 2008 10:25:37 GMT

hi,

put a break point in this select ( in ur program) use ST05, and see the details in Explain section. From this you can come to know it is a problematic select or not.

regards,

madhumitha

Re: reg: join statement

valter_oliveira — Thu, 15 May 2008 17:52:54 GMT

Hello.

Regarding your doubt looks like a good option because you are accessing RESB with it's key belnr (it's the left table). And then, with the record found, it's goes to EKPO with it's key too ... so it's ok in that way.

Now, as have been said, use ST05 to measure the performance.

My doubt is always FOR ALL ENTRIES ... remenber that if the internal table is too big, you must split it in more than one parts.

Best regasrds.

Valter Oliveira.

Re: reg: join statement

Former Member — Fri, 16 May 2008 07:54:20 GMT

How would you analyse a join:

RSEG

Not buffered, size 3, 2 indexes

Primary key

MANDT

BELNR

GJAHR

BUZEI

Index AD

MANDT

EBELN

EBELP

EKPO

Not buffered, size 3, lots of indexes!

Primary key

MANDT

EBELN

EBELP

Where condition: belnr and gjahr from FOR ALL ENTRIES table,

Obviously good part of primary key of RSEG

ON Condition is the primary key of EKPO: EBELN and EBELP are unique and therefore sufficient, additional fields are not necessary.

-> join is rather simple to built


IF not (tbl_cross is initial ).
  SELECT a~belnr a~gjahr a~buzei a~ebeln a~ebelp a~bukrs
         a~werks a~wrbtr a~mwskz b~matkl
         INTO TABLE tbl_rseg_ekpo
         FROM   rseg AS a
           JOIN ekpo AS b
           ON   a~ebeln = b~ebeln
           AND a~ebelp = b~ebelp
         FOR ALL ENTRIES IN tbl_cross
         WHERE a~belnr = tbl_cross-awkey
         AND   a~gjahr = tbl_cross-gjahr.
Endif.

The FOR ALL ENTRIES table can have up to several 10.000 entries if necessary. It is anyways processed in small blocks.

Siegfried