Left outer join

[Former Member]

SELECT t1~bukrs t2~bwkey t3~lgort
    FROM    t001 as t1
    left OUTER JOIN   t001k as t2 on t1~bukrs = t2~bukrs
     inner join t001l as t3 on t2~bwkey = t3~werks
   into CORRESPONDING FIELDS OF TABLE lt_overview
   .

The left outer join doesnt work, because of the inner join. Is it possible to put brackets in the manner, that the left outer join is going to work?

[former_member491621]

Hi David,

You can try using FOR ALL ENTRIES for joining the tables.

[Ruediger_Plantiko]

Hi David,

let's have a look at the tables in your SELECT statement:

T001: Buffered on application server

T001K: Buffered on application server

T001L: Buffered on application server

Meaning that all the data are made available through the memory of the application server, once they have been selected from the database. If you use INNER or OUTER Joins, the buffering is bypassed and the statement is forced to be executed on the database.

For performance, it is therefore better to avoid the JOINs and to use auxiliary internal tables instead, local in your method (one standard table and two hashed tables), and to produce your desired result in ABAP by looping on one of the auxiliary tables and reading the other two internal tables.

Regards,

Rüdiger

[Ruediger_Plantiko]

The following method fully exploits the SAP buffers. Don't use Joins for buffered tables.

method get_overview.

  data: lt_t001 type standard table of t001,

          lt_t001k type sorted table of t001k with unique key bukrs bwkey,

          ls_t001L type t001L,

          ls_overview type TY_OVERVIEW.  " Your type goes here

field-symbols: <ls_t001> type t001,

                     <ls_t001k> type t001k.

select * from t001 into table lt_t001.

select * from t001k into table lt_t001k.

clear et_overview.

loop at lt_t001 assigning <ls_t001>.

  clear ls_overview.

  move-corresponding <ls_t001> to ls_overview.

  loop at lt_t001k assigning <ls_t001k> where bukrs = <ls_t001>-bukrs.

     move-corresponding <ls_t001k> to ls_overview.

     select * from t001l into ls_t001l

                 where werks = <ls_t001k>-bwkey.

         move-correspoding ls_t001l to ls_overview.

         insert ls_overview into table et_overview.

     endselect.

  endloop.

  if sy-subrc ne 0.

    insert ls_overview into table et_overview.  " corresponds to LEFT OUTER ...

  endif.

endloop.

endmethod.

[Former Member]

Hi,

Check with below Code .........

   SELECT T001~BUKRS T001~BUTXT T001~ORT01 T001K~BWKEY T001K~BUKRS T001L~LGORT T001L~WERKS

    FROM ( T001

           LEFT OUTER JOIN T001K

           ON  T001K~BUKRS = T001~BUKRS

           INNER JOIN T001L

           ON  T001L~WERKS = T001K~BWKEY )

into CORRESPONDING FIELDS OF TABLE lt_overview

WHERE T001~BUKRS IN S_BUKRS       " Where Condition You can Write as per your requirement

           AND T001~BUTXT IN S_BUTXT

           AND T001~ORT01 IN S_ORT01 .

Regard's

Smruti

[former_member491621]

Hi David,

Can you tell me if you are having any condition in the where clause??

It could be the reason you are not getting the data.

[kumud]

Hi David,

If you could tell us what is the result that you are ultimately trying to fetch out of the three tables,

we could help in formulating the query better. Thanks!

Regards,

Kumud