topic Re: Left outer join in Application Development and Automation Discussions

Left outer join

Former Member — Sat, 26 Jan 2013 16:34:44 GMT

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?

Re: Left outer join

former_member491621 — Sat, 26 Jan 2013 17:29:20 GMT

Hi David,

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

Re: Left outer join

Ruediger_Plantiko — Sun, 27 Jan 2013 08:10:01 GMT

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

Re: Left outer join

Former Member — Mon, 28 Jan 2013 03:09:47 GMT

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

Re: Left outer join

former_member491621 — Mon, 28 Jan 2013 05:21:58 GMT

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.

Re: Left outer join

kumud — Mon, 28 Jan 2013 06:43:51 GMT

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

Re: Left outer join

Ruediger_Plantiko — Mon, 28 Jan 2013 09:04:11 GMT

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.