topic Re: Join not working !! in Application Development and Automation Discussions

Join not working !!

Former Member — Thu, 23 Aug 2012 14:12:48 GMT

Hi all,

I have joined the below two select statements using joins. I have tried INNER JOIN as well as LEFT OUTER JOIN but in vain.

Please suggest as the join is failing to retrieve correct records (extra records are being loaded).

------------------------------------------------------------------------------------

SELECT ebeln
              ebelp
              loekz
              txz01
              menge
              meins
              netpr
              netwr
              erekz
              afnam
              INTO TABLE it_ekpo
              FROM ekpo
              FOR ALL ENTRIES IN it_ekko
              WHERE ebeln EQ it_ekko-ebeln
              AND loekz EQ c_space.

IF NOT it_ekpo[] IS INITIAL.

SELECT ebeln
             ebelp
             wempf
             INTO TABLE it_ekkn
             FROM ekkn
             FOR ALL ENTRIES IN it_ekpo
             WHERE ebeln = it_ekpo-ebeln
             AND   ebelp = it_ekpo-ebelp.

ENDIF.

------------------------------------------------------------------------------------

I have tried joins as below:

SELECT a~ebeln

a~ebelp

a~loekz

a~txz01

a~menge

a~meins

a~netpr

a~netwr

a~afnam

a~erekz

b~wempf

INTO CORRESPONDING FIELDS OF TABLE it_ekpo

FROM ekpo AS a

INNER JOIN ekkn AS b

ON a~ebeln EQ b~ebeln

AND a~ebelp EQ b~ebelp

FOR ALL ENTRIES IN it_ekko

WHERE a~ebeln EQ it_ekko-ebeln

AND a~loekz EQ c_space.

SELECT ekpo~ebeln "PO.Number
            ekpo~ebelp    "PO.ITEm
            ekpo~loekz    "Indicator
            ekpo~txz01    "Text
            ekpo~menge    "Quantity
            ekpo~meins    "Unit.Measure
            ekpo~netpr    "price per Unit
            ekpo~netwr    "Extended price ordered
            ekpo~afnam    "Requestor number
            ekpo~erekz    "Final invoice indicator
            ekkn~wempf    "Goods recipient
   INTO CORRESPONDING FIELDS OF TABLE it_ekpo
   FROM ekpo LEFT OUTER JOIN ekkn
   ON ekpo~ebeln EQ ekkn~ebeln
   AND ekpo~ebelp EQ ekkn~ebelp
   FOR ALL ENTRIES IN it_ekko
   WHERE ekpo~ebeln EQ it_ekko-ebeln
     AND ekpo~loekz EQ c_space.

Note: The internal table ekko does not contain field 'ebelp'.

Regards,

Hema.

Re: Join not working !!

Former Member — Thu, 23 Aug 2012 16:01:51 GMT

Hi,

Have you consider doing one SELECT that will join EKPO, EKKO and EKKN? I think that would be a good solution.

I usually don't use JOIN and FOR ALL ENTRIES in the same stamement, but please remember that when you use FOR ALL ENTRIES, the entire logical expression sql_cond is evaluated for each individual row of the internal table. Which means, if you have several records in it_ekpo with the same document number, the SELECT will get you duplicated records.

Re: Join not working !!

amy_king — Thu, 23 Aug 2012 16:35:37 GMT

Hi Hema,

Perhaps you could explain in more detail, or with an example, what records you are trying to retrieve, since your INNER JOIN statement does return records (I tested it in my system). I didn't test your LEFT OUTER JOIN.

Cheers,

Amy

Re: Join not working !!

RaymondGiuseppi — Fri, 24 Aug 2012 10:04:27 GMT

Can you explain your exact problem. Actually you asked "why my code does not extract what I want", so we can only compare your code with ... your code ?

Your program extracts correctly each not cancelled line item with each account assignment for a list of purchase order (from your Abap code) and on my system it works fine.

So which purchase order line or which account assignment do you want ?.

Is this thread/question following "Join fails between tables !!" ?

Regards,

Raymond

PS: Read The quality of an answer depends significantly on the quality of the question (or: how to ask good questions) or Asking Good Questions in the SCN Discussion Spaces will help you get Good Answers.

Re: Join not working !!

Former Member — Fri, 24 Aug 2012 11:03:49 GMT

Dear all, my apologies for not being clear earlier.

I have the below given select statements which take a long time to run. As a part of code optimization, I am trying to perform the same selects using joins. My objective is to reduce the running time finally.

------------------------------------ original piece of code -----------------------------

SELECT ebeln

bukrs

lifnr

bedat

INTO TABLE it_ekko

FROM ekko

WHERE bukrs IN s_bukrs

AND lifnr IN s_lifnr

and bedat >= c_date.

IF NOT it_ekko[] IS INITIAL.

SELECT lifnr

sperr

stceg

INTO TABLE it_lfa1

FROM lfa1

FOR ALL ENTRIES IN it_ekko

WHERE lifnr = it_ekko-lifnr.

SORT it_ekko BY ebeln.

SELECT ebeln

ebelp

loekz

txz01

menge

meins

netpr

netwr

afnam

INTO TABLE it_ekpo

FROM ekpo

FOR ALL ENTRIES IN it_ekko

WHERE ebeln EQ it_ekko-ebeln

AND loekz EQ c_space.

ENDIF.

IF NOT it_ekpo[] IS INITIAL.

SELECT ebeln

ebelp

wempf

INTO TABLE it_ekkn

FROM ekkn

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln = it_ekpo-ebeln

AND ebelp = it_ekpo-ebelp.

ENDIF.

------------------------------------- end of original code ------------------------------------

Now I have tried to modify the code as below. Please check and suggest any other better methods to reduce the overall runtime.

SELECT a~ebeln

a~bukrs

a~lifnr

a~bedat

b~sperr

b~stceg

INTO CORRESPONDING FIELDS OF TABLE it_ekko

FROM ekko AS a

INNER JOIN lfa1 AS b

ON a~lifnr EQ b~lifnr

WHERE bukrs IN s_bukrs

AND a~lifnr IN s_lifnr

AND bedat >= c_date.

IF NOT it_ekko[] IS INITIAL.

SELECT a~ebeln

a~ebelp

a~loekz

a~txz01

a~menge

a~meins

a~netpr

a~netwr

a~afnam

a~erekz

b~wempf

INTO CORRESPONDING FIELDS OF TABLE it_ekpo

FROM ekpo AS a

INNER JOIN ekkn AS b

ON a~ebeln EQ b~ebeln

AND a~ebelp EQ b~ebelp

FOR ALL ENTRIES IN it_ekko

WHERE a~ebeln EQ it_ekko-ebeln

AND a~loekz EQ c_space.

ENDIF.

IF NOT it_ekpo[] IS INITIAL.

SELECT ekbe~ebeln

ekbe~ebelp

ekbe~zekkn

ekbe~vgabe

ekbe~gjahr

ekbe~belnr

ekbe~buzei

rseg~menge

rseg~shkzg

rseg~wrbtr

INTO CORRESPONDING FIELDS OF TABLE it_ekbe

FROM ekbe LEFT OUTER JOIN rseg

ON ekbe~ebeln = rseg~ebeln

AND ekbe~ebelp = rseg~ebelp

AND ekbe~buzei = rseg~buzei

AND ekbe~gjahr = rseg~gjahr

AND ekbe~belnr = rseg~belnr

For all entries in it_ekpo

WHERE ekbe~ebeln = it_ekpo-ebeln

AND ekbe~ebelp = it_ekpo-ebelp

AND ( ekbe~vgabe = '2' OR ekbe~vgabe = '3' )

AND ekbe~zekkn = '01'.

ENDIF.

=========================================================================

And after all these selects, I shall loop at it_ekpo and perform some operations and finally display the output (List of all POs for a given company code).

Here the join on EKPO and EKKN returns me extra number of records in "it_ekpo" than what the original select statements return without join condition.

Thanks,

Hema.

Re: Join not working !!

former_member209818 — Fri, 24 Aug 2012 11:09:30 GMT

I think, you do not need a join of EKKO & LFA1 as there will can be many POs for same Vendor.

Instead read the EKKO as earlier query. Copy the Query result in a temp internal table and delete adjacent duplicate entries by comparing LIFNR. With this, search in the LFA1 table.

This will reduce the execution time if you are having huge data.. but there will not be any significant change if selected data is very small.

- Harshad

Re: Join not working !!

Former Member — Fri, 24 Aug 2012 11:18:27 GMT

The entire program can be found under the heading "Report taking long time to run ..." under "ABAP Testing and Troubleshooting" space. The original program takes more than 4 hours to complete which I am expected to make run below 2 hrs.

The no. of entries in it_ekpo will be around 65,000 which will be my output list (list of all purchase orders for a particular company code).

Thanks,

Hema.

Re: Join not working !!

RaymondGiuseppi — Fri, 24 Aug 2012 11:36:42 GMT

In the first case you selected

- a set of purchase order in one itab

- all their lines in another itab

- all their account assignment in a third table

In the second case you selected

- a set of purchase order in one itab

- all their lines x invoice lines (with outer join you keep also item not invoiced) restricting to first account assignment

Those are two different extractions, e.g. If an item is invoiced in 2 different invoice but with same accounts, you get it twice in one itab and once in another. Are you looking for account assignment or invoice lines ?

If you actually want to optimize SELECT for big data you could use sub-queries which most database are able to handle, like in following sample

SELECT a~ebeln a~ebelp a~loekz a~txz01 a~menge a~meins a~netpr a~netwr a~afnam a~erekz b~wempf
  INTO CORRESPONDING FIELDS OF TABLE it_ekpo
  FROM ekpo AS a
  INNER JOIN ekkn AS b
    ON a~ebeln EQ b~ebeln AND a~ebelp EQ b~ebelp
  WHERE a~loekz EQ c_space
  AND EXISTS ( SELECT * FROM ekko
                WHERE ebeln EQ a~ebeln
                  AND bukrs IN s_bukrs
                  AND lifnr IN s_lifnr
                  AND bedat GE c_date ) .

Regards,

Raymond

Re: Join not working !!

Former Member — Fri, 24 Aug 2012 11:42:47 GMT

Hi Raymond,

I am looking for invoiced items.

Re: Join not working !!

RaymondGiuseppi — Fri, 24 Aug 2012 11:53:08 GMT

So first select EKKO JOIN LFA1. Then select EKBE JOIN EKPO with selection on VGABE and subqueries on EKKO with same selection criteria than first select.

(Look at EKBE-REFWR which (If I remenber) is equal to RSEG-WRBTR, so you may not be required to read/join RSEG)

Regards,

Raymond