Join not working !!

[Former Member]

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.

[Former Member]

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.

[amy_king]

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

[RaymondGiuseppi]

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 q... or Asking Good Questions in the SCN Discussion Spaces will help you get Good Answers.

[Former Member]

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.

[former_member209818]

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

[Former Member]

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.

[RaymondGiuseppi]

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

[Former Member]

Hi Raymond,

I am looking for invoiced items.

[RaymondGiuseppi]

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