query optimization

[Former Member]

hello to all,

i've one problem. in a report there was this query present:

SELECT * FROM mkpf WHERE budat IN date AND

MBLNR IN MBLNR.

SELECT * FROM mseg WHERE mblnr = mkpf-mblnr AND

mjahr = mkpf-mjahr.

MOVE-CORRESPONDING mseg TO zmseg.

APPEND zmseg. CLEAR zmseg.

ENDSELECT.

ENDSELECT.

becoz of nested SELECT it was too much time to execute. so i've made following changes:

SELECT mblnr mjahr budat xblnr FROM mkpf into corresponding fields of table tmkpf WHERE budat IN date and MBLNR IN MBLNR.

if sy-subrc eq 0.

SELECT mblnr mjahr bwart matnr werks lifnr ebeln ebelp FROM mseg into corresponding fields of table zmseg for all entries in tmkpf WHERE mblnr = tmkpf-mblnr AND mjahr = tmkpf-mjahr.

endif.

but when i checked ZMSEG table for both the queries it is showing different number of records.

what could be goin wrong with the query? kindly help.........

thank you.

[Former Member]

SELECT mblnr mjahr budat xblnr FROM mkpf into corresponding fields of table tmkpf WHERE budat IN date and MBLNR IN MBLNR.

if sy-subrc eq 0.

SORT TABLE TMKPF BY MBLNR MJAHR.

DELETE ADJACENT DUPLICATES FROM TABLE TMKPF COMPARING MBLNR MJAHR.

SELECT mblnr mjahr bwart matnr werks lifnr ebeln ebelp FROM mseg into corresponding fields of table zmseg for all entries in tmkpf WHERE mblnr = tmkpf-mblnr AND mjahr = tmkpf-mjahr.

endif.

Do the necessary modifications as shown above.

Hope this helps you.

Regards,

Pavan.

[Former Member]

Hi,

Better use this view...

WB2_V_MKPF_MSEG

Then u need only one Select...

Reward points if it helps..

Regards,

Omkar.

[Former Member]

Hi,

Instead of using SELECT...ENDSELECT why don't you try using SELECT INTO TABLE <itab> WHERE <cond>.

If you do so then there won't be any nested selects.

Hope it was useful.

Thanks,

Sandeep.

[Former Member]

Hi,

First select data into MKPF.

SELECT * FROM mkpf into table it_mkpf WHERE budat IN date AND

MBLNR IN MBLNR.

then if not it_mkpf[] is initial.

SELECT * FROM mseg into table it_mseg

for all entries in it_mkpf

WHERE mblnr = it_mkpf-mblnr AND

mjahr = it_mkpf-mjahr.

endif.

Then combine both itabs by looping it.

loop at it_mkpf

read table it_mseg.....

store it in it_output.

endloop.

Also search for more index and keys.

Reward if useful!

[Former Member]

hi,

check it with removing the sy-subrc condition.

[Former Member]

Hi

The second query is fine and good

use the 2nd Option queires using just fields and for all entries of mkpf..

first one Select * ..endselect..not advisable..

<b>Reward points for useful Answers</b>

Regards

Anji