topic Re: Alternative for Join in Application Development and Automation Discussions

Alternative for Join

Former Member — Thu, 01 Feb 2007 02:47:35 GMT

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

Re: Alternative for Join

RichHeilman — Thu, 01 Feb 2007 02:53:55 GMT

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

Regards,

RIch Heilman

Re: Alternative for Join

SantoshKallem — Thu, 01 Feb 2007 02:59:35 GMT

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

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 03:21:47 GMT

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

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 03:25:29 GMT

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.

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 04:32:52 GMT

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.

reward point if useful.

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 04:59:03 GMT

Hi all,

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

INNER JOIN. Its urgent .

Rajesh.

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 05:15:51 GMT

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

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 05:36:47 GMT

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

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 05:57:35 GMT

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,

Re: Alternative for Join

Former Member — Thu, 01 Feb 2007 06:49:00 GMT

Hi sipra,

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

Regards,

Rajesh.