topic Re: Regd Join in Application Development and Automation Discussions

Regd Join

Former Member — Mon, 16 Jun 2008 02:04:34 GMT

Hi all,

The below statement is taking a very long time, can any one please suggest what can be done to reduce the time.

*read PO history from EKBE join EKKO

SELECT ekbe_{ebeln ekbe}ebelp ekbe~vgabe

ekbe_{gjahr ekbe}belnr ekbe_{buzei ekbe}bewtp

ekbe_{bwart ekbe}budat ekbe_{menge ekbe}dmbtr

ekbe_{wrbtr ekbe}waers ekbe_{arewr ekbe}shkzg

ekbe_{cpudt ekbe}xblnr ekbe_{matnr ekko}lifnr ekpo~meins

INTO TABLE l_tbl_ekbe

FROM ekbe JOIN ekko

ON ekbe_{ebeln = ekko}ebeln

JOIN ekpo

ON ekbe_{ebeln = ekpo}ebeln

AND ekbe_{ebelp = ekpo}ebelp

WHERE ekbe~ebeln IN s_ebeln

AND ekbe~ebelp IN s_ebelp

AND ekbe~vgabe IN r_vgabe

AND ekbe~gjahr IN r_gjahr

AND ekbe~budat IN s_budat

AND ekbe~matnr IN s_matnr

AND ekbe~werks IN r_werks

AND ekko~bukrs = p_bukrs

AND ekko~lifnr IN s_lifnr

AND ekko~bsart IN r_bsart.

*Select History per Purchasing Document: Delivery Costs (EKBZ)

REFRESH l_tbl_ekbz.

SELECT a_{ebeln a}ebelp a~vgabe

a_{gjahr a}belnr a_{buzei a}bewtp

a_{budat a}menge a~dmbtr

a_{wrbtr a}waers a_{arewr a}shkzg

a_{cpudt a}xblnr c~matnr

b_{lifnr c}meins

a~belnr AS awkey

FROM ekbz AS a JOIN ekko AS b

ON a_{ebeln = b}ebeln

JOIN ekpo AS c

ON a_{ebeln = c}ebeln

AND a_{ebelp = c}ebelp

INTO CORRESPONDING FIELDS OF TABLE l_tbl_ekbz

FOR ALL ENTRIES IN l_tbl_ebeln

WHERE a~ebeln = l_tbl_ebeln-ebeln

AND a~ebelp = l_tbl_ebeln-ebelp

AND a~budat IN s_budat.

APPEND LINES OF l_tbl_ekbz TO l_tbl_ekbe.

Re: Regd Join

Former Member — Mon, 16 Jun 2008 03:24:40 GMT

Hi,

Dont use more joins in a single stmt.

Use only two tables in the first join.

Then, check the no. of records in the extracted internal table. If atleast one record exists, then use FOR ALL ENTRIES and extract records from the third table.

Hope this helps.

Regards,

SUbramanian

Re: Regd Join

Former Member — Mon, 16 Jun 2008 03:40:08 GMT

Hi,

first get the data from both ekbe,ekko with select statemetns..if at least one record exits ...then get the data from thrid table based on above internal table by using for all entries.

dont use join stmt on third table...

regards,

venkat n

Re: Regd Join

Former Member — Mon, 16 Jun 2008 03:40:10 GMT

Hi,

Use INNER JOIN rather than JOIN.

And change the join structure, first take on left EKKO, EKPO then EKBE.

Same way for second query EKKO, EKPO then EKBZ.

Hope this will increase performance.

Regards,

Ravi K

Re: Regd Join

Former Member — Mon, 16 Jun 2008 06:52:30 GMT

Don't use join statement at all.Create separate internal tables say IT_EKKO, IT_EKPO,IT_EKBE and populate them one after another.Use 'for all entries' and finally club them together into another internal table IT_FINAL. This will improve performance.

Re: Regd Join

Former Member — Mon, 16 Jun 2008 06:58:53 GMT

Hi ,

U can used 'for all entries' not Join.

Prepared Internal table for all indivisual database table & then fetch data from one table to another.

Finally prepared a Final Internal table.......

Regards,

Biswanth