regarding nested selects...........

[Former Member]

Hi friends,

i want to use one query in that i have to use neseted selects.

can any one tel me example with considering performane also.

regards,

madhu.

[Former Member]

Hi madhu,

SELECT tabname

INTO ls_dd02l

FROM dd02l

WHERE tabname LIKE c_feld

AND buffered IS NOT NULL.

SELECT tabname fieldname keyflag position

INTO CORRESPONDING FIELDS OF ls_dd03l

FROM dd03l

WHERE tabname = ls_dd02l-tabname

AND fieldname IN ('MANDT', 'CLIENT', 'CLNT')

AND keyflag = 'X'.

IF ( sy-subrc EQ 0 ).

wa-tabname = ls_dd03l-tabname.

wa-fieldname = ls_dd03l-fieldname.

wa-keyflag = ls_dd03l-keyflag.

wa-position = ls_dd03l-position.

APPEND wa TO itab.

ENDIF.

ENDSELECT.

ENDSELECT.

There are no practical limits to nested SELECTs, but as other repliers have already pointed out they quickly become a performance hog. I have stopped using them for years, and when I teach ABAP performance classes I also quote them as one of the top "don'ts". With nested selects, the number of calls to the DBMS can easily become enormous, putting the database server under stress.

A good alternative - the one I favour most myself - is to use SELECT INTO TABLE for the"outer" select, followed by appropriate SELECT FOR ALL ENTRIES statements to handle the inner levels.

ABAP joins are another alternative. When properly written, a join will be faster than a nested select. The problem is however that very often joins are not properly written. SELECT statements should always respect the KISS principle (keep it simple, stupid) and with a join it is very easy to violate that rule. A join over 2 tables should probably be OK, but with 3 or more tables you want to be very careful. If you really want to code a complex join, I recommend that you also code the same logic with different means (INTO TABLE/FOR ALL ENTRIES or even nested selects) and then compare that with the join.

regards,

Jayan.

[former_member585060]

Hi,

Instead of Nested selects use like this

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

Go to SE30 and glick on Tips and Tricks

Look at SQL interface.

Regards,

Krishna

[former_member787646]

Hi

Instead of using NESTED SQL statements use FOR ALL ENTRIES as far as performance is concerned.

Hope it helps.

Murthy

[Former Member]

Nested selects should be avoided,

+ If all tables are not buffered. If they are buffered, then the nested selects are the best solution!

+ Joins should be the best alternative. But the performance of the joins should be checked (as everything) by the SQL trace. And maybe youe first version is not optimal.

+ For all entries can be an option, I would actually only recommend it, if the join does not work.

+ the above mentioned subselect is a very special option, which can not always be used. But if it is applicable, then it can be the best solution.

Siegfried

[Former Member]

Hi,

Nested select can be avoided by using two internal table and merging those two internal tables into third itab.

ex.

select field into table itab1 from ekko

where ebeln in s_ebeln.

select fields into table itab2 from ekpo

for all entries in itab1 where

ebeln = itab1-ebeln.

loop at itab1 into wa_itab1.

move-corresponding wa-tab1 to wa-final.

read table itab2 with key ebeln = it_header binary search transporting no fields.

loop at itab2 into wa_itab2 from sy-tabix.

if itab2-ebeln <> itab1-ebeln.

exit.

endif.

move-corresponding wa-tab2 to wa-final.

append wa-final to itab_final .

endloop.

endloop.

This will avoid nested select and processing will be fast according to performance tuning.

Edited by: Shobhit Bansal on Aug 21, 2008 1:05 PM

[Former Member]

check the link you find a example:

With luck,

Pritam.