Help with INNER JOIN

Former Member — Fri, 15 Sep 2006 17:35:48 GMT

Hi All,

The following code takes a lot of time to execute. Can anyone suggest to fine tune the following inner join statement.

SELECT a_{ebeln a}ebelp a_{txz01 a}matnr a~werks

a_{lgort a}menge a_{meins a}netpr a~peinh

a_{webaz a}insmk a_{pstyp a}knttp a~labnr

a_{konnr a}ktpnr a_{evers a}kunnr a~adrnr

a_{revlv a}emlif a_{adrn2 a}mtart a_{elikz a}retpo

b_{lifnr b}ekgrp b_{waers b}bedat b~inco1

b_{inco2 c}etenr c_{eindt c}slfdt c~menge

c_{wemng d}banfn d~bnfpo

INTO TABLE t_po

FROM ekko AS b

INNER JOIN ekpo AS a

ON a_{ebeln EQ b}ebeln

INNER JOIN eket AS c

ON c_{ebeln EQ a}ebeln

AND c_{ebelp EQ a}ebelp

LEFT OUTER JOIN eban AS d

ON a_{ebeln EQ d}ebeln

AND a_{ebelp EQ d}ebelp

WHERE b~ebeln IN s_ebeln "Purchasing Document

AND b~bsart IN s_bsart "Document Type

AND b~loekz EQ space "Deletion Indicator, Header

AND b~lifnr IN s_lifnr "Vendor Number

AND b~ekorg IN s_ekorg "Purchasing Organization

AND b~ekgrp IN s_ekgrp "Purchasing Group

AND b~bedat IN s_bedat "Document Date

AND b~bukrs IN s_bukrs

AND a~elikz = space "delivery not complete

AND a~loekz EQ space "Deletion Indicator, Line

AND a~txz01 IN r_txz01 "Short Text

AND a~matnr IN s_matnr "Material Number

AND a~werks IN s_werks "Plant

AND a~lgort IN s_lgort "Storage Location

AND a~matkl IN s_matkl "Material Group

AND a~idnlf IN s_idnlf "Vendor's Material Number

AND a~pstyp IN r_pstyp "Item Category

AND a~knttp IN s_knttp "Account Assign. Category

AND a~bstae IN s_bstae "Confirmation Control Key

AND c~eindt IN s_eindt. "Item Delivery Date

Regards

Brain

Re: Help with INNER JOIN

Former Member — Fri, 15 Sep 2006 18:23:31 GMT

Check that s_ebeln is not empty before doing the select.

Rob

Re: Help with INNER JOIN

Former Member — Fri, 15 Sep 2006 18:48:45 GMT

Also, your last join:


    LEFT OUTER JOIN eban AS d
      ON a~ebeln EQ d~ebeln
      AND a~ebelp EQ d~ebelp

doesn't use an index. Remove this and see if it speeds things up. If it does, see if there's another way to get this data.

Rob

Re: Help with INNER JOIN

Former Member — Fri, 15 Sep 2006 19:37:59 GMT

To follow up on Rob's suggestion, BANFN and BNFPO are on EKPO.

topic Re: Help with INNER JOIN in Application Development and Automation Discussions

Help with INNER JOIN

Re: Help with INNER JOIN

Re: Help with INNER JOIN

Re: Help with INNER JOIN