pls tell me the use of for all entries in

[Former Member]

hi all,

pls tell me the use of for all entries in abap.

and what is benefits of it .

thanks and regards

vikas saini

[Former Member]

Hi,

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

Regards,

Satish

[Former Member]

Vikas,

When you have lot of records in your internal table based on that if you want to select data from other database table taht time

instead of below statement

LOOP AT itab.

select ......

where keyvalue = itab-keyvalue.

ENDLOOP.

you can go for FOR ALL ENTRIES.

SELECT required fields from database table

into itab

fro all entries in itab

where key value = itab-keyvalue.

USE :

in case 1. every time you are selecting the data from database table .So every time request is going database sever so performance of program will down.

case2 you are slecting the all the records atonce from database table .Performance of the program will be more.

Don't forget to reward if useful.....

[Former Member]

Hi

We can use FOR ALL ENTRIES to replace the Inner Joins in order to fetch data from 2 or more tables.

FOR ALL ENTRIES improves the performance over Inner Joins

SELECT .. FOR ALL ENTRIES was created in OPEN SQL at a time when it was not yet possible to perform database JOINs (this was not supported for all SAP-approved DBMS). The connection between the inner and outer database tables is

created in ABAP. If you want to replace nested SELECT statements with FOR ALL ENTRIES, this only eliminates the nesting and its disadvantages partially. In general,you bundle the inner SELECT statements into packages, for example, of 5 statements each. This reduces the transfer effort required and avoids identical SQL statements. The access sequence is defined in the ABAP coding, like for nested SELECT statements. As mentioned above, if you use FOR ALL ENTRIES, you have to make sure that the driving table is not initial and does not contain any duplicate entries. As in the case of nested SELECT statements, the decision of whether an INNER JOIN or OUTER JOIN is performed is also made in the ABAP program for SELECT ..FOR ALL ENTRIES. If you want to read large data volumes, you should only use

FOR ALL ENTRIES in exceptional cases.

[Former Member]

Hi,

if the number of table or 2 then you can go for joins

if the number of tables are more than 2 then use for all entries

because if you use more than 3 tables in the data base connection will be there for that 3 tables up to displaying the data

if you use for all entries then no need of joining the tables

finally

for all entries have more performance than joins

A join is of course the more efficient programming, it can built the result set in one step. And a join is much more powerful than a FOR ALL ENTRIES, there are several types of joins (OUTER JOINS, complicated hashed merge joins where only the total result set is small) which can not be built with FOR ALL ENTRIES.

The FOR ALL ENTRIES is only better, if the join has problems to determine the correct access path. In joins the database optimizer has to decide which table should be taken first with which index. In the first access the WHERE condition is exploited. For the next access additional information is added from the second table exploiting the ON condition and the WHERE condition. If the optimzer can not determine the correct access, because it misjudges the selectivity, then a FOR ALL ENTRIES can be faster. InNthe FOR ALL ENTRIES, you must program the correct order, selective access first, less selective later. This problem becomes more probable if more tables are involved in the join.

But very often it is possible to influence the optimizer decision by database hints.

Therefore you should always check the join performance with the SQL trace and if there is a problem try to optimize the join. Only if nothing helps, then you should switch to the FOR ALL ENTRIES, but you will need the analysis anyway, otherwise you will not know how to program the FOR ALL ENTRIES.

If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.

The internal table itab must have a structured line type and the component comp must be compatible with the column col.

The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if FOR ALL ENTRIES is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set.

Notes

In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO.

The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement.

If the additions PACKAGE SIZE or UP TO n ROWS are specified together with FOR ALL ENTRIES, they are not passed to the database system but are applied instead to the resulting set once all selected rows on the application server have been imported.

With duplicated rows in the resulting set, the addition FOR ALL ENTRIES has the same effect as if addition DISTINCT were specified in the definition of the selection quantity. Unlike DISTINCT, the rows are not deleted from the database system but are deleted on the application server from the resulting set.

Addition FOR ALL ENTRIES is only possible for WHERE conditions of the SELECT statement.

Example

Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

PARAMETERS p_city TYPE spfli-cityfrom.

TYPES: BEGIN OF entry_tab_type,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

END OF entry_tab_type.

DATA: entry_tab TYPE TABLE OF entry_tab_type,

sflight_tab TYPE SORTED TABLE OF sflight

WITH UNIQUE KEY carrid connid fldate.

SELECT carrid connid

FROM spfli

INTO CORRESPONDING FIELDS OF TABLE entry_tab

WHERE cityfrom = p_city.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

FOR ALL ENTRIES IN entry_tab

WHERE carrid = entry_tab-carrid AND

connid = entry_tab-connid.

or

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

Reward points if useful.

Regards,

Harini.S

[Former Member]

Hi,

For all entries ...

Check out this code.

select vbeln posnr matnr from vbap into table lt_vbap

where matnr = ' '.

if lt_vbap[] is not initial.

select vbeln audat from vbak into table lt_vbak

for all entries in lt_vbap

where vbeln = lt_vbap-vbeln.

endif.

as i am finding the records of vbak based on the records that i get from vbap. so i am using for all entries.

another thing.

if i am not using the if condition before retriving the records from vbak then it will retrive all records present in the vbak irrespective of the for all entries .

regards,

Santosh Thorat

[Former Member]

hi,

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.

Hope this is helpful, Do reward.