Skip to Content
author's profile photo Former Member
Former Member

pls tell me the use of for all entries in

hi all,

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

and what is benefits of it .

thanks and regards

vikas saini

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:29 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:30 AM

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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:35 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:36 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:36 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.