Alternative for Join

[Former Member]

Hai In a case i need to get data from more than 4 tables.

Here implementing join is some wht complex. Is there any way that we can get

the data. Here performence is also one factor.

If you can give code that will be very helpful for me.

Thanks in advance...

Srik

[RichHeilman]

You might want to look into using FOR ALL ENTRIES IN instead of using INNER JOINs

Regards,

RIch Heilman

[SantoshKallem]

have to use FOR ALL ENTRIES.

check this link for sample code.

http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

search with key 'FOR ALL ENTRIES' in this forum.

reward if useful.

santhosh

[Former Member]

you can create a database view for those tables and access in your program or you can use for all entries as said earlier.

regards

shiba dutta

[Former Member]

Srikanth,

Say u have to extract data from 4 tables.

select some data from table1 into itab1

where conditions.

if not itab1[] is initial

select required data for table2 into itab2

for all entries in iatb1

where x = iatb1-x.

endif.

like that use for all entries for all internal tables.

If single records are ther use read statement else innerloop.

loop itab1

read table itab2 with key x = itab1-x

Like move all the values to final internal table.

endloop.

Pls.mark if useful.

[Former Member]

Hi ,

use FOR ALL ENTRIES

see below code

SELECT

a~vbeln

a~auart

a~vkorg

a~spart

a~kunnr

b~posnr

b~matnr

c~maktx

b~kwmeng

b~vrkme

INTO TABLE it_so1 FROM vbak AS a

JOIN vbap AS b ON b_{vbeln = a}vbeln

JOIN makt AS c ON c_{matnr = b}matnr

AND c~spras = sy-langu

WHERE a~vbeln IN s_vbeln .

IF sy-subrc = 0.

SELECT vbelv

posnv

vbeln

posnn

vbtyp_n

INTO TABLE it_vbfa

FROM vbfa

FOR ALL ENTRIES IN it_so1

WHERE vbelv = it_so1-vbeln

AND posnn = it_so1-posnr

AND vbtyp_n ='J' .

IF sy-subrc = 0.

SELECT vbeln

posnr

matnr

werks

lgort

charg

lfimg

vrkme

FROM lips INTO TABLE it_del_ful

FOR ALL ENTRIES IN it_vbfa

WHERE vbeln = it_vbfa-vbeln

AND posnr = it_vbfa-posnn.

ENDIF.

ENDIF.

reward point if useful.

[Former Member]

Hi all,

Any one tell me what is the advantage of FOR ALL ENTRIES when campare with

INNER JOIN. Its urgent .

Rajesh.

[Former Member]

Hi,

Use select for all entries as I've used in my code in the following example:

SELECT QMNUM

IWERK

EQUNR

BTPLN

AUSWK

INGRP

QMDAB

BEZDT

TPLNR

SWERK

MSGRP

FROM VIQMEL INTO TABLE ITABMEL

WHERE IWERK = 'M011' and

AUSWK = 'A' and

INGRP = 'SLM' and

QMDAB NE '00000000' and

BEZDT GE SDATE-LOW and BEZDT LE SDATE-HIGH and

SWERK IN S_WERK.

IF SY-SUBRC = 0.

SELECT QMNUM MNGRP MNCOD FROM QMMA

INTO CORRESPONDING FIELDS OF TABLE ITABMA

FOR ALL ENTRIES IN ITABMEL

WHERE QMNUM = ITABMEL-QMNUM and

MNGRP = 'RESTLT01' and

MNCOD = '0350' OR MNCOD ='0270'.

SORT ITABMA BY QMNUM.

LOOP AT ITABMEL.

READ TABLE ITABMA WITH KEY QMNUM = ITABMEL-QMNUM BINARY

SEARCH.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITABMA TO ITABMEL.

MODIFY ITABMEL TRANSPORTING QMNUM.

ENDIF.

ENDLOOP.

SELECT TPLNR PLTXT FROM IFLO

INTO CORRESPONDING FIELDS OF TABLE ITABLO

FOR ALL ENTRIES IN ITABMEL

WHERE TPLNR = ITABMEL-TPLNR.

SORT ITABLO BY TPLNR.

LOOP AT ITABMEL.

READ TABLE ITABLO WITH KEY TPLNR = ITABMEL-TPLNR BINARY

SEARCH.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITABLO TO ITABMEL.

MODIFY ITABMEL TRANSPORTING PLTXT.

ENDIF.

ENDLOOP.

Hope this helps.

Reward if helpful.

Regards,

Sipra

[Former Member]

Hi sipra,

If you donot mind could you send me the detail code of this program.

Regards,

Rajesh.

[Former Member]

Hi srikanth,

U can join 2 tables by using joins but the performance goes down. This can be overcomed by <b>for all entries</b>.

select-options : s_vbeln for likp-vbeln.

select vbeln erdat lfart

from likp

into table it_likp

where vbeln in s_vbeln.

based on s_vbeln the records will be stored in it_likp. eg, say 24 records selected

if not it_likp[] is initial.

select vbeln posnr matnr

from lips

into table it_lips

for all entries in it_likp

where vbeln eq it_likp-vbeln.

endif.

now , since i am using,

for all entries in it_likp

so the vbeln value of these 24 records (which i have selected in it_likp) will be checked in lips and those records will be stored in it_lips.

Drawback is if the records are not there in it_likp then all the records from lips will be fetched into it_lips.

To overcome this draw back we have to compulsory check

if not it_likp[] is initial.

this means if the internal table it_likp is not initial ie, it is containing some records

http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp

[Former Member]

Hi

As of now you are using join to get data from 4 tables, so definitly they have common fields;

1) with the common fields create the database view.

2) Now data retrieval from the view greatly improves the performance compared to using joins.

You can also use 'for all entries' , but data retrieval from view is faster than using for all entries.

Regards,

MB