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

Regarding Select using FOR ALL ENTRIES (FAEI)

Hi all,

Please help me with Select using FOR ALL ENTRIES (FAEI)

Thanks in advns,

Das.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 17, 2008 at 06:40 AM

    Hi,

    This version of the SELECT statement was the only way to join tables until SAP release 3.0E. It is recommended that the SQL JOIN be used. However, this is an alternate method of joining tables that may be used in some circumstances. Remember, SQL JOIN doesnÂ’t utilize the SAP internal database buffer.

    Example:

    SELECT EKGRP FROM T024

    INTO TABLE T_024.

    SELECT MATNR WERKS EKGRP FROM MARC

    INTO TABLE T_MARC

    FOR ALL ENTRIES IN T_024

    WHERE EKGRP = T_024-EKGRP.

    Prerequisites:

    The driver table cannot be empty. This would cause one full table scan.

    In general, keep the driver table as small and unique (in regards to the fields that would be needed for referencing in the FAEI) as possible. If duplicate entries are not deleted, identical data is read unnecessarily from the database.

    If FAEI is used, the selected field list must be a superset of all the key fields of the database tables used in SQL. This is absolutely required to avoid data loss since the DB interface in R/3 returns unique sets of the result set to the application program. Failing to ensure this may cause problems when you want to get all records from a database table that match a certain condition.

    Avoid using FAEI on a small DB table (like configuration tables). This could potentially cause full table scans for each FAEI packet. Even if it does index range scan, it is better to get data from the small DB tables by individual SELECT without the use of FAE.

    Always use FAEI in conjunction with a DB index otherwise it would cause multiple full table scans (one table scan for each FAEI packet).

    The first field(s) of the DB index should refer to the fields of the FAEI driver table in the SQL WHERE clause. Do not leave gaps between index fields in the WHERE clause. Inefficient index access will impact every FAEI packets.

    FAEI works best when retrieving a small percentage of data from a Database table. When a large percentage of data is needed, consider getting data out of the DB table without FAEI implementation, and then do additional filtration in your application

    Reward points if found helpfull...

    Cheers,

    Chandra Sekhar.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 17, 2008 at 05:49 AM

    Hi Das..

    IF NOT gt_open_po[] IS INITIAL.

    SELECT vbeln vposn vbegdat venddat

    FROM veda

    INTO TABLE gt_contract

    FOR ALL ENTRIES IN gt_open_po

    WHERE vbeln = gt_open_po-vbeln AND

    vposn = gt_open_po-posnr.

    SORT gt_contract BY vbeln posnr.

    ENDIF.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 17, 2008 at 05:49 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.

    Example

    DATA: BEGIN OF LINE,

    CARRID TYPE SPFLI-CARRID,

    CONNID TYPE SPFLI-CONNID,

    CITYFROM TYPE SPFLI-CITYFROM,

    CITYTO TYPE SPFLI-CITYTO,

    END OF LINE,

    ITAB LIKE TABLE OF LINE.

    LINE-CITYFROM = 'FRANKFURT'.

    LINE-CITYTO = 'BERLIN'.

    APPEND LINE TO ITAB.

    LINE-CITYFROM = 'NEW YORK'.

    LINE-CITYTO = 'SAN FRANCISCO'.

    APPEND LINE TO ITAB.

    SELECT CARRID CONNID CITYFROM CITYTO

    INTO CORRESPONDING FIELDS OF LINE

    FROM SPFLI

    FOR ALL ENTRIES IN ITAB

    WHERE CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

    WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

    ENDSELECT.

    Please reward points if solved your issue.

    Regards,

    Renjith Michael.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 17, 2008 at 05:53 AM

    Hi Das,

    I will send a sample code for how to use for all entires ok..Necesarry condition is two table must have one common filed ok.

    data: begin of itab occurs 0,

    vbeln like vbak-vbeln,

    erdat like vbak-erdat,

    end of itab.

    data: begin of itab1 occurs 0,

    vbeln like vbap-vbeln,

    posnr like vbap-posnr,

    end of itab1.

    select vbeln posnr

    form vbap

    for all entries in itab where vbeln = itab-vbeln.

    Reward points if helpful

    Kiran Kumar.G.A

    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.