topic Re: alv in Application Development and Automation Discussions

alv

Akhil_Sun — Thu, 12 Jun 2008 04:31:03 GMT

can anyone post how to join 3 tables like ekko,ekpo,eket by using for all entries plzzzz. its very urgent

Re: alv

Former Member — Thu, 12 Jun 2008 04:34:49 GMT

Hi,

yuou can take help from the following code snippet..

SELECT tplnr

pltxt

invnr

objnr

erdat

fltyp

iwerk FROM iflo

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE tplnr EQ p_tplnr.

SELECT objek

clint FROM kssk

APPENDING TABLE itab1

WHERE objek EQ p_tplnr.

SELECT class

clint FROM klah

INTO TABLE itab3

FOR ALL ENTRIES IN itab1

WHERE clint = itab1-clint.

LOOP AT itab INTO wa_itab.

READ TABLE itab1 INTO wa_itab1 WITH KEY tplnr = wa_itab-tplnr.

READ TABLE itab3 INTO wa_itab3 WITH KEY clint = wa_itab1-clint.

wa_itab-class = wa_itab3-class.

wa_itab-clint = wa_itab3-clint.

MODIFY itab FROM wa_itab.

ENDLOOP.

reward if useful

preet

Re: alv

Former Member — Thu, 12 Jun 2008 04:36:23 GMT

SELECT * FROM ekko INTO TABLE it_ekko

WHERE ebeln IN so_ebeln.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE it_ekpo

FROM crmm_but_lnk0141

FOR ALL ENTRIES IN it_ekko

WHERE <fiel>= <filr>.

like this

Regards

Anbu

Re: alv

Former Member — Thu, 12 Jun 2008 04:37:08 GMT

Hi akhil,

see this below document try ur self.

The Inner join can be very performant when you use combined table like vbak/vbap/vbep or ekko/ekpo/eket or mara/marc/mard, ...

The for all entries statement will be less performant if you have a lot of records ( and don't forget, you have in most cases to delete duplicates, so ... )

In my opinion, it really depends.

If you have doubt in your report, just do a test ...

Anyway, something sure is that obviously there is no better statement between "for all entries" and "Inner Join". If there was, everybody would know it.

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Inner join :

This is used to join two tables with matchin fields.

This is a EQUI join type means, record will be fetched if and only if joining condition is is matchin for both tables.

This is a INNER JOIN type of SQL.

This refers two or more tables with table fields.

For all entries:

This is OUTER JOIN type of SQL.

Means, all record will be fetched from left table on joining condition. and from right table if conditions satisfies that record will be fetched otherwise null will be retured as data.

FOR ALL ENTRIES is an effective way of doing away with using JOIN on two tables.

You can check the below code -

SELECT BUKRS BELNR GJAHR AUGDT

FROM BSEG

INTO TABLE I_BSEG

WHERE BUKRS = ....

SELECT BUKRS BELNR BLART BLDAT

FROM BKPF

INTO TABLE I_BKPF

FOR ALL ENTRIES IN I_BSEG

WHERE BUKRS = I_BSEG-BUKRS

AND BELNR = I_BSEG-BELNR

AND BLDAT IN SO_BLDAT.

*******************************8

look another example

what is the use of FOR ALL ENTRIES

1. INNER JOIN

DBTAB1 <----

> DBTAB2

It is used to JOIN two DATABASE tables

having some COMMON fields.

2. Whereas

For All Entries,

DBTAB1 <----

> ITAB1

is not at all related to two DATABASE tables.

It is related to INTERNAL table.

3. If we want to fetch data

from some DBTABLE1

but we want to fetch

for only some records

which are contained in some internal table,

then we use for alll entries.

*----

1. simple example of for all entries.

2. NOTE THAT

In for all entries,

it is NOT necessary to use TWO DBTABLES.

(as against JOIN)

3. use this program (just copy paste)

it will fetch data

from T001

FOR ONLY TWO COMPANIES (as mentioned in itab)

REPORT abc.

DATA : BEGIN OF itab OCCURS 0,

bukrs LIKE t001-bukrs,

END OF itab.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

*----

itab-bukrs = '1000'.

APPEND itab.

itab-bukrs = '1100'.

APPEND itab.

*----

SELECT * FROM t001

INTO TABLE t001

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs.

*----

LOOP AT t001.

WRITE 😕 t001-bukrs.

ENDLOOP.

Overall, All records from left table will be selected where as only matching record will be updated in resultset from right table.

This generally uses ITAB as RIGHT side comparisoin.

While using For all entried Ensure that, ITAB should not be empty and it fetched unique records only.

Regards,

karthik

Re: alv

Former Member — Thu, 12 Jun 2008 04:38:16 GMT

hi ,

Which fields u need??

ekko is the header table -

ebeln is the primary key

ekpo is the item table -

ebeln is the primary key ,

eket Schedule Lines -- ebeln is the primary key

so take join on ebeln is the primary key .

Regards

Amit

Re: alv

JoffyJohn — Thu, 12 Jun 2008 04:39:49 GMT

SELECT *

FROM ekko INTO TABLE it_ekko

WHERE ebeln IN s_ebeln.

IF NOT it_ekko[] IS INITIAL.

SELECT ebeln ebelp idnlf elikz

FROM ekpo INTO TABLE it_ekpo

FOR ALL ENTRIES IN it_ekpo1

WHERE ebeln = it_ekpo1-ebeln

AND ebelp = it_ekpo1-ebelp.

IF NOT it_ekpo[] IS INITIAL.

SELECT ebeln ebelp etenr eindt

FROM eket INTO TABLE it_eket

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln = it_ekpo-ebeln

AND ebelp = it_ekpo-ebelp.

ENDIF.

Re: alv

Former Member — Thu, 12 Jun 2008 05:13:53 GMT

Select ebeln

adrnr ...............(add how many fields you want)

from ekko

into table ta_ekko

where ebeln = pa_ebeln.

IF sy-subrc EQ 0.

Select ebelp

.(add how many fields you want)

from ekpo

INTO TABLE ta_ekpo

FOR ALL ENTRIES ta_ekko

where ebeln = ta_ekko-ebeln

IF sy-subrc EQ 0.

SORT ta_ekpo by EBELP.

ENDIF.

Select add how many fields you want)

from EKET

INTO TABLE ta_eket

FOR ALL ENTRIES ta_ekko

where ebeln = ta_ekko-ebeln.

if SY-SUBRC eq 0.

sort TA_EKET BT EBELN.

endif.