05-18-2006 7:18 PM
Hi,
This stmt is taking long time to execute. Is there any way, we can modify it to perform well.
SELECT kna1~NAME1
vbak~ERNAM
vbak~erdat
vbak~KUNNR
vbak~VBELN
vbak~AUART
FROM VBAK
INNER JOIN kna1 on kna1kunnr = VBAKKUNNR
INTO TABLE ITAB_VBAK
WHERE VBAK~ERDAT IN SERDAT
AND VBAK~AUART IN SAUART.
Thanks
Veni.
05-18-2006 7:37 PM
HI veni,
change your code, and use for all entries.
data: begin of itab_vbak occurs 0,
vbeln
erdat
ERNAM
erdat
KUNNR
AUART
kunnr
name1
end of itab_vbak.
data: l_tabix type sy-tabix.
SELECT vbeln
erdat
ERNAM
erdat
KUNNR
AUART
FROM VBAK
INTO TABLE ITAB_VBAK
WHERE ERDAT IN SERDAT
AND AUART IN SAUART.
if sy-subrc = 0.
select kunnr
name1
from kna1
into table it_kna1
for all entries in itab_vbak
where kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
loop at itab_vbak.
l_tabix = sy-tabix.
read table it_kna1 with key kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
itab_vbak-name1 = it_kna1-name1.
modify itab_vbak index l_tabix.
endif.
endloop.
endif.
endif.
regards
vijay
05-18-2006 7:22 PM
You are using indexes:
VBAK Secondary index 'ERD': MANDT ERDAT (can use full index)
KNA1 Primary key: MANDT KUNNR (can use full primary key)
The problem now is in user selection.
05-18-2006 7:24 PM
I guess it really depends on what you put in your select options. There is an index over the ERDAT field in VBAK, so if you put something in that select-option is should run fairly fast. This code is working good for me when filling a value for S_ERDAT.
report zrich_0001 .
data: begin of ivbak occurs 0,
vbeln type vbak-vbeln,
auart type vbak-auart,
erdat type vbak-erdat,
ernam type vbak-ernam,
kunnr type kna1-kunnr,
name1 type kna1-name1,
end of ivbak.
select-options: s_erdat for ivbak-erdat,
s_auart for ivbak-auart.
select vbak~vbeln vbak~auart vbak~erdat
vbak~ernam kna1~kunnr kna1~name1
from vbak
inner join kna1
on kna1~kunnr = vbak~kunnr
into table ivbak
where vbak~erdat in s_erdat
and vbak~auart in s_auart.
loop at ivbak.
write:/ ivbak-vbeln, ivbak-auart,
ivbak-kunnr, ivbak-name1.
endloop.
Regards,
Rich Heilman
05-18-2006 7:25 PM
This should be OK so long as:
SERDAT is not empty.
All of the entries in SERDAT are simple (ie EQ, BT) and are (I)nclude.
Also - if it's BT, make sure the range isn't too large; otherwise, it may or may not be able to use the index, but it will be looking at so much data, that it won't matter.
Rob
Message was edited by: Rob Burbank
05-18-2006 7:37 PM
HI veni,
change your code, and use for all entries.
data: begin of itab_vbak occurs 0,
vbeln
erdat
ERNAM
erdat
KUNNR
AUART
kunnr
name1
end of itab_vbak.
data: l_tabix type sy-tabix.
SELECT vbeln
erdat
ERNAM
erdat
KUNNR
AUART
FROM VBAK
INTO TABLE ITAB_VBAK
WHERE ERDAT IN SERDAT
AND AUART IN SAUART.
if sy-subrc = 0.
select kunnr
name1
from kna1
into table it_kna1
for all entries in itab_vbak
where kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
loop at itab_vbak.
l_tabix = sy-tabix.
read table it_kna1 with key kunnr = itab_vbak-kunnr.
if sy-subrc = 0.
itab_vbak-name1 = it_kna1-name1.
modify itab_vbak index l_tabix.
endif.
endloop.
endif.
endif.
regards
vijay
05-18-2006 8:18 PM
Seems like you may have customer number so table VAKPA indexes orders by customer. Other index tables are valuable, too, like VAPMA (material).