Inner Join vs For all entries

[Former Member]

Hi Forum,

Can i use a INNER JOIN and FOR ALL ENTRIES clause in a single statement ?

I have a join condition over MCH1 and MCHB.

SELECT a_{matnr a}charg a_{verab a}vfdat a_{zusch a}hsdat a_{lifnr a}cuobj_bm b_{werks b}lgort b~clabs

INTO CORRESPONDING FIELDS OF TABLE lt_batch2

FROM mch1 AS a INNER JOIN mchb AS b ON

b_{matnr = a}matnr AND

b_{charg = a}charg AND

b~matnr = i_material AND

b~werks = i_plant AND

b~lgort = i_storage_location AND

b~ersda > lc_date AND

b~lvorm NE 'X' .

Now, i want this join to be happened only for few entries which i already have in a normal internal table.

Is it possible to use FOR ALL ENTRIES here ?

Thanks,

Srini.

[amit_khare]

It is possible but not that efficient...

SELECT A~POSNR

A~OBJNR

B~PRNAM

B~POSID

B~GJAHR

FROM IMZO AS A

INNER JOIN IMPR AS B ON

A_{POSNR = B}POSNR

INTO CORRESPONDING FIELDS OF TABLE GT_INVT

FOR ALL ENTRIES IN GT_IMAK

WHERE A~OBJNR = GT_IMAK-OBJNR1.

[former_member206439]

Hi

you can use that for all entries in lt_batch2

for next select query

[Former Member]

Hi,

Hope the following Threads will help you regarding your problem

Thanks.

Nitesh

[Sandeep_Kumar]

Yes.

SELECT a_{matnr a}charg a_{verab a}vfdat a_{zusch a}hsdat a_{lifnr a}cuobj_bm b_{werks b}lgort b~clabs

INTO CORRESPONDING FIELDS OF TABLE lt_batch2

FROM mch1 AS a INNER JOIN mchb AS b ON

--> FOR ALL ENTRIES IN ITAB and sepcify conditions in where clause

b_{matnr = a}matnr AND

b_{charg = a}charg AND

b~matnr = i_material AND

b~werks = i_plant AND

b~lgort = i_storage_location AND

b~ersda > lc_date AND

b~lvorm NE 'X' .

[Former Member]

Hi,

i think You cannot use both at the same time.

[Former Member]

Instead of join, bulk fetch the entries from table 1 and table 2 into internal table 1 and internal table 2.

Then loop into into your final internal table and within the loop read internal table 1 and internal table 2.

[Former Member]

Hi Amit,

I am looking for a performance efficient solution. If the number of entries in both the tables are around 1 lakh(100000).

In this case,

1) a JOIN with FOR ALL ENTRIES is efficient

OR

2) two select statements where first one queries MCH1 table with some where condition and

second statement queries MCHB table with FOR ALL ENTRIES and WHERE .

Can you tell me which approach would be efficient as i can't measure time on DEV server where the entries are very few.

Thanks,

Srini.

[amit_khare]

I will prefer second select with INDEX in WHERE clause.

[Former Member]

yes u can

Do like this i used in 1 program

SELECT s_{posnr s}lfimg s_{mbdat f}budat

INTO CORRESPONDING FIELDS OF TABLE i_delivery

FROM lips as s

INNER JOIN mkpf as f ON s_{vbeln = f}LE_VBELN

for all entries in itab WHERE s~vbeln in s_vbeln

AND s~mbdat in s_mbdat

AND f~budat in s_budat

AND s~erdat in s_erdat2

AND S~posnr = itab-posnr.

Salil ....

[matt]

That's enough answers all saying the same thing (except Rahul Kumar Sinha). Thread locked.

matt