for all antries

[Former Member]

hi frds plz give me response

About for all entries & inner joins

to mail id

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 5:27 PM

[Former Member]

hi frds plz give me response

About for all entries & inner joins

to mail id

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 5:27 PM

[Former Member]

Hi,

INNER JOIN:

inner join used for combin two table.

support u can fetch two table. u can write select query two time .data base fetch time take twotime read from db.

so using primary key we easy write inner join. but both table primary key and both field refer same field.

Inner Join and Outer Join

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .

Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

Check these links

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

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

http://www.sswug.org/see/28439

http://web.mit.edu/ist/org/admincomputing/dev/abap_review_check_list.htm

http://www.sapgenie.com/abap/performance.htm

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 5:28 PM

[Former Member]

INNER JOIN:

inner join used for combin two table.

support u can fetch two table. u can write select query two time .data base fetch time take twotime read from db.

so using primary key we easy write inner join. but both table primary key and both field refer same field.

Inner Join and Outer Join

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .

Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

Sample code for inner join with for all entries

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 " << change

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. "<< change

[sreelatha_gullapalli]

hi

Page 10 of 15

Use of FOR ALL Entries

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

Inner joins using 3 tables

Try this :-

SELECT stpo_{stlnr stpo}idnrk mast_{matnr mara}mtart stpo~menge

INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast

JOIN stpo ON stpo_{stlnr = mast}stlnr

JOIN mara ON mara_{matnr = mast}matnr

WHERE stpo_{stlty = 'M' "AND stpo}idnrk IN s_matnr

AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Or this.

Code:

Select single Vbrk_{Bukrs Vbrk}Kunrg Vbrk~Vbeln

Vbrk_{Fkdat Vbrk}Bstnk_Vf Vbrk~Zterm

Tvzbt~Vtext

Vbak_{Vbeln Vbak}Bstdk

Likp_{Vbeln Likp}lfdat Likp~Lfuhr

into w_vbrk

from vbrk

inner join Tvzbt on Tvzbt_{Zterm = Vbrk}Zterm and

Tvzbt~Spras = sy-langu

Inner join Vbfa as SalesLnk

on SalesLnk~vbeln = pu_vbeln and

SalesLnk~vbtyp_v = c_order

inner join Vbak on Vbak_{Vbeln = SalesLnk}Vbelv

Inner join Vbfa as DeliveryLnk

on DeliveryLnk~vbeln = pu_vbeln and

DeliveryLnk~vbtyp_v = c_Delivery

inner join Likp on Likp_{Vbeln = DeliveryLnk}Vbelv

where vbrk~vbeln = pu_Vbeln.

This code locates sales, delivery and payment terms info from a billing document number.

or

Here, this one also works fine :

select zfpcd_{cadivi zfpcd}proforma zfpcd_{factura zfpcd}aniofactura

zfpcd_{montousd zfpcd}montoap zfpcd_{ebeln zfpcd}inco1

zfpcd_{lifnr lfa1}name1 zcdvs_{status zfpcd}conint

into it_lista

from zfpcd inner join zcdvs

on zfpcd_{ebeln = zcdvs}ebeln

and zfpcd_{proforma = zcdvs}proforma

and zfpcd_{lifnr = zcdvs}lifnr

inner join lfa1

on zfpcd_{lifnr = lfa1}lifnr

where zcdvs~status = '04'.

regards

sreelatha gullapalli

[Former Member]

hi,

FOR ALL ENTRIES is for picking up the relavent data from the database which all are existing in the FOR ALL ENTRIES internal table.

I mean,

if you have 2 internal tables-

1 i_mara

2 i_marc

select * up to 10 rows from mara into table i_mara.

now you want to select the other relavent data of material from marc with only 10 records.

select * from marc into table i_marc for all entreis in i_mara

where material = i_mara-material.

if i_mara contians nothing data, the above statement will pick up all the data from the marc table.

Advantage is:

i_mara contains 10 records and you want to pick up all the relavent data from marc.

if for all entries is not there, you should use the following code:

loop at i_mara.

select * from marc into table i_marc

endloop.

This block of code is not at all sufficient and supportable to use.

i hope it can helps you at least.

<REMOVED BY MODERATOR>

sekhar

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 5:28 PM

[dhruv_shah3]

Hi,

Pls follow the [https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/6050] [original link is broken] [original link is broken] [original link is broken];

And you will find the everything abt ..........

Thanks & Regards,

Dhruv Shah

[Former Member]

Hi,

Regarding FOR ALL ENTRIES,

It is used for getting values for all the values from the internal table in the select statement.

Then use the inner joins for for getting different table fields in a single select statment.For e.g see the select statement below.

SELECT k_{ebeln k}ebelp k_{vbeln k}vbelp

FROM ekkn AS k INNER JOIN ekbe AS b ON k_{ebeln = b}ebeln

AND k_{ebelp = b}ebelp

INTO TABLE gi_purchase

FOR ALL ENTRIES

IN gi_sales

WHERE k~mandt EQ sy-mandt

AND k~vbeln EQ gi_sales-vbeln

AND k~vbelp EQ gi_sales-posnr

AND b~budat EQ p_date.1