Skip to Content

NESTED SELECT - ENDSELECT

HI ALL

I HAVE ONE DOUBT. IF WE HAVE STATEMENT LIKE

SELECT

SELECT

ENDSELECT

ENDSELECT

THEN WE CAN USE

SELECT

READ TABLE ................

ENDSELECT

i AM FACING A PROBLEM WHERE I HAVE TO USE THIS STATEMENT

SELECT

SELECT

SELECT

ENDSELECT

ENDSELECT

ENDSELECT

CAN ANY ONE GIVE ME AN ALTERNATIVE FOR THIS WITH SUITABLKE EXAMPLE.

Thanks

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 06:38 PM

    Hi,

    Nested SELECT loops mean that the number of accesses in the inner loop is multiplied by the number of accesses in the outer loop.

    Using <b>nested selects is a technique with low performance</b>.

    Instead of nested SELECT statements the joins INNER or OUTER should be used or VIEWS should be created in ABAP DICTIONARY if possibel another

    alternative is to use

    FOR ALL ENTRIES IN itab

    If we use FOR ALL ENTRIES IN itab , care should be taken that it is not

    empty otherwise DBMS will return all the rows from the table specified

    in the FROM clause of SELECT statement .

    You can use the following techniques to avoid nested SELECT statements:

    <b>ABAP Dictionary Views</b>

    You can define joins between database tables statically and systemwide as views in the ABAP Dictionary. Such views can be used by all ABAP programs. One of their advantages is that fields that are common to both tables (join fields) are only transferred once from the database to the application server.

    Views in the ABAP Dictionary are implemented as inner joins. If the inner table contains no lines that correspond to lines in the outer table, no data is transferred. This is not always the desired result. For example, when you read data from a text table, you want to include lines in the selection even if the corresponding text does not exist in the required language. If you want to include all of the data from the outer table, you can program a left outer join in ABAP.

    The links between the tables in the view are created and optimized by the database system. Like database tables, you can buffer views on the application server. The same buffering rules apply to views as to tables. In other words, it is most appropriate for views that you use mostly to read data. This reduces the network load and the amount of physical I/O in the database.

    <b>Joins in the FROM Clause</b>

    You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

    The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

    The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

    <b>Subqueries in the WHERE and HAVING Clauses</b>

    Another way of accessing more than one database table in the same Open SQL statement is to use subqueries in the WHERE or HAVING clause. The data from a subquery is not transferred to the application server. Instead, it is used to evaluate conditions in the database system. This is a simple and effective way of programming complex database operations.

    <b>Using Internal Tables</b>

    It is also possible to avoid nested SELECT loops by placing the selection from the outer loop in an internal table and then running the inner selection once only using the FOR ALL ENTRIES addition. This technique stems from the time before joins were allowed in the FROM clause. On the other hand, it does prevent redundant data from being transferred from the database.

    Regards,

    Maha

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 06:59 PM

    Hi,

    Please use one of the following

    1. Create views and select from the view. Alternatively you could also use inner joins.

    2. FOR all entries is also a good option. Please remember to give complete primary keys in the where clause, also select all the primary keys as a part of your field list in select.

    Nested select is good to learn in the initial stages, but is not used in professional coding due to performance issues. Infact in Code review checklist of all projects this is a 'not to do' item.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 05:41 PM

    Use inner join instead of writing nested select stmts.

    Regards,

    Naveen.

    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.