topic Re: How to avoid the below nested select statement in Application Development and Automation Discussions

How to avoid the below nested select statement

Former Member — Tue, 27 Apr 2010 13:07:33 GMT

Please any one help me how this select statemet is working and how to avoid the nesetd select statement .

if we avoid below nested , does it improve performace ?

select field1 field2

into table w_feeds

from ZTable as t

where field2 in r_feedf1

and POSITION_POSTDT =

( SELECT MAX( position_postdt ) FROM zTable

where position_postdt le r_pdate-high

and field1 = t~field1 ).

Thanks in Advace.

Former Member — Tue, 27 Apr 2010 13:28:30 GMT

add POSITION_POSTDT field into w_feeds. define an internal tab e.g.data: tmp_wfeeds like w_feeds.

select field1 field2 position_postdt

into table w_feeds

from ZTable as t

where field2 in r_feedf1

and POSITION_POSTDT le r_pdate-high.

sort w_feeds descending by POSITION_POSTDT .

read table w_feeds index 1 into tmp_wfeeds.

Former Member — Tue, 27 Apr 2010 13:30:19 GMT

Hi,

Instead of nested query go for two separate queries. I see you are querying on the same table...so better go by this approach

select field1 field2 POSITION_POSTDT

into table w_feeds

from ZTable

where field2 in r_feedf1.

Remove the where condition on POSITION_POSTDT

Sort the table w_feeds by POSITION_POSTDT Descending; So you will get data pertaining to Max Position_Postdt.

Finally delete the other entries which are not Max.

This will enhance the performance over the nested query.

Regards

Shiva

Edited by: Shiva Kumar Tirumalasetty on Apr 27, 2010 7:00 PM

Former Member — Wed, 28 Apr 2010 07:42:13 GMT

Thanks