NESTED SELECT - ENDSELECT

former_member329386 — Thu, 29 Nov 2007 17:34:18 GMT

HI ALL

I HAVE ONE DOUBT. IF WE HAVE STATEMENT LIKE

SELECT

ENDSELECT

THEN WE CAN USE

SELECT

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

ENDSELECT

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

SELECT

ENDSELECT

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

Thanks

Re: NESTED SELECT - ENDSELECT

Former Member — Thu, 29 Nov 2007 17:41:42 GMT

Use inner join instead of writing nested select stmts.

Regards,

Naveen.

Re: NESTED SELECT - ENDSELECT

Former Member — Thu, 29 Nov 2007 18:38:19 GMT

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 nested selects is a technique with low performance.

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:

ABAP Dictionary Views

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.

Joins in the FROM Clause

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.

Subqueries in the WHERE and HAVING Clauses

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.

Using Internal Tables

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

Re: NESTED SELECT - ENDSELECT

Former Member — Thu, 29 Nov 2007 18:59:27 GMT

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.

topic NESTED SELECT - ENDSELECT in Application Development and Automation Discussions

NESTED SELECT - ENDSELECT

Re: NESTED SELECT - ENDSELECT

Re: NESTED SELECT - ENDSELECT

Re: NESTED SELECT - ENDSELECT