05-26-2007 11:59 AM
Hi, I have a piece of code in which select Statements has to be changed due to performance reasons.
the problem is :: in between SELECT and END SELECT -
there is SELECT SINGLE and other 2 SELECT STMT'S are used.
can anyone help me in changing the code.
this is my code ::
SELECT MATNR INFNR LIFNR IDNLF WGLIF
meins umren umrez urzla regio
INTO (EINA-MATNR, EINA-INFNR, EINA-LIFNR, EINA-IDNLF, EINA-WGLIF,
eina-meins, eina-umren, eina-umrez,
eina-urzla, eina-regio)
from eina
WHERE MATNR in S_MATNR AND
LIFNR EQ P_LIFNR.
SELECT SINGLE EKORG WERKS WAERS NETPR esokz ekgrp aplfz bstae mwskz kzabs
peinh bprme bpumn bpumz
INTO (EINE-EKORG, EINE-WERKS, EINE-WAERS, EINE-NETPR,
eine-esokz, eine-ekgrp, eine-aplfz, eine-bstae,
eine-mwskz, eine-kzabs, eine-peinh, eine-bprme,
eine-bpumn, eine-bpumz)
FROM EINE
WHERE INFNR EQ EINA-INFNR
AND EKORG EQ P_EKORG
AND ESOKZ EQ P_ESOKZ
AND WERKS IN P_WERKS.
CHECK SY-SUBRC = 0.
IF RB_NETPR EQ 'X'.
CHECK EINE-NETPR NE 0.
ENDIF.
clear a017.
select datab datbi
from a017
into (a017-datab, a017-datbi)
where kappl eq 'M'
and kschl eq 'PB00'
and lifnr eq eina-lifnr
and matnr eq eina-matnr
and ekorg eq eine-ekorg
and werks eq eine-werks
and esokz eq eine-esokz
and datab le sy-datum
and datbi ge sy-datum.
exit.
endselect.
clear eord.
select flifn
from eord
into eord-flifn
where matnr eq eina-matnr
and werks eq eine-werks
and vdatu LE sy-datum
and bdatu GE sy-datum
and flifn eq 'X'.
endselect.
MOVE: EINA-MATNR TO WA_EXTRACT-MATNR,
EINA-INFNR TO WA_EXTRACT-INFNR,
EINA-LIFNR TO WA_EXTRACT-LIFNR,
EINE-EKORG TO WA_EXTRACT-EKORG,
EINE-WERKS TO WA_EXTRACT-WERKS,
EINA-IDNLF TO WA_EXTRACT-IDNLF,
EINA-WGLIF TO WA_EXTRACT-WGLIF,
EINE-NETPR TO WA_EXTRACT-NETPR,
EINE-WAERS TO WA_EXTRACT-WAERS,
'G' to wa_extract-type,
eina-meins to wa_extract-meins,
eina-umren to wa_extract-umren,
eina-umrez to wa_extract-umrez,
eina-urzla to wa_extract-urzla,
eina-regio to wa_extract-regio,
eine-esokz to wa_extract-esokz,
eine-ekgrp to wa_extract-ekgrp,
eine-aplfz to wa_extract-aplfz,
eine-bstae to wa_extract-bstae,
eine-mwskz to wa_extract-mwskz,
eine-kzabs to wa_extract-kzabs,
eine-peinh to wa_extract-peinh,
eine-bprme to wa_extract-bprme,
eine-bpumn to wa_extract-bpumn,
eine-bpumz to wa_extract-bpumz,
a017-datab to wa_extract-datab,
a017-datbi to wa_extract-datbi,
eord-flifn to wa_extract-flifn,
w_info_text to wa_extract-info_text.
APPEND WA_EXTRACT TO IEXTRACT.
ENDSELECT.
05-28-2007 3:58 AM
hey let me know if it helped.
thanks
shivika
Message was edited by:
Shivika Bhorchi
05-28-2007 2:12 AM
Hi,
Remove your select .. endselect and get all the data into internal table and then use FOR ALL ENTRIES with your select query, you will see much improvement in your code performance.
Reward points if useful.
Regards,
Atish
05-28-2007 2:47 AM
TRY SOMETHING LIKE THIS:
SELECT EINAMATNR EINAINFNR EINALIFNR EINAIDNLF EINA~WGLIF
EINAmeins EINAumren EINAumrez EINAurzla EINA~regio
EINEEKORG EINEWERKS EINEWAERS EINENETPR EINEesokz EINEekgrp EINEaplfz EINEbstae EINEmwskz EINEkzabs
EINEeinh EINEbprme EINEbpumn EINEbpumz
INTO CORRESPONDING FIELDS OF TABLE IEXTRACT
from eina
JOIN EINE ON EINEINFNR = EINAINFNR
JOIN EORD ON EORDMATNR = EINAMATNR
AND EORDWERKS = EINEWERKS
and EORD~vdatu LE sy-datum
and EORD~bdatu GE sy-datum
and EORD~flifn eq 'X'.
WHERE EINA~MATNR in S_MATNR AND
ENIA~LIFNR EQ P_LIFNR AND
EINE~EKORG EQ P_EKORG AND
EINE~ESOKZ EQ P_ESOKZ AND
EINE~WERKS IN P_WERKS
.
IF RB_NETPR EQ 'X'.
DELETE IEXTRACT WHERE NETPR NE 0.
ENDIF.
select LIFNR MATNR EKORG WERKS ESOKZ datab datbi
from a017
into CORRESPONDING FIELDS OF TABLE IA017
FOR ALL ENTRIES IN IEXTRACT
where kappl eq 'M'
and kschl eq 'PB00'
and lifnr eq IEXTRACT-lifnr
and matnr eq IEXTRACT-matnr
and ekorg eq IEXTRACT-ekorg
and werks eq IEXTRACT-werks
and esokz eq IEXTRACT-esokz
and datab le sy-datum
and datbi ge sy-datum.
LOOP AT IEXTRACT INTO WA_EXTRACT.
CLEAR WA_EXTRACT.
MOVE:
'G' to wa_extract-type,
w_info_text to wa_extract-info_text.
*declare this table as sorted by the following fields or *sort it by them and change the read to use the 'binary *search' option
READ TABLE TA017 WITH KEY LIFNR = WA_EXTRACT-LIFNR
MATNR = WA_EXTRACT-MATNR
EKORG = WA_EXTRACT-EKORG
WERKS = WA_EXTRACT-WERKS
ESOKZ = WA_EXTRACT-ESOKZ.
IF SY-SUBRC = 0.
MOVE-CORRESPONDING TA017 TO WA_EXTRACT.
ENDIF.
MODIFY TABLE IEXTRACT FROM WA_EXTRACT.
ENDLOOP.
05-28-2007 3:52 AM
Hey Mallikarjun
Try this:
CLEAR: tbl_eina[],
tbl_eine[].
SELECT matnr infnr lifnr
idnlf wglif umren
umrez urzla regio
INTO TABLE tbl_eina
FROM EINA
WHERE matnr in S_MATNR
AND lifnr EQ P_LIFNR.
IF ( NOT tbl_eina[] IS INITIAL ).
SELECT SINGLE ekorg werks waers
netpr esokz ekgrp
aplfz bstae mwskz
kzabs peinh bprme
bpumn bpumz
INTO TABLE tbl_eine
FROM EINE
for all entries in tbl_eina
WHERE infnr EQ tbl_eina-infnr
AND ekorg EQ P_EKORG
AND esokz EQ P_ESOKZ
AND werks EQ P_WERKS.
.
CLEAR: wa_a017,
tbl_a017[].
SELECT lifnr
matnr
ekorg
werks
esokz
datab
datbi
INTO TABLE tbl_a017
FROM A017
FOR ALL ENTRIES IN tbl_eina
WHERE kappl EQ 'M'
AND kschl EQ 'PB00'
AND lifnr EQ tbl_eina-lifnr
AND matnr EQ tbl_eina-matnr
AND ekorg EQ p_ekorg
AND werks EQ p_werks
AND esokz EQ p_esokz
AND datab LE sy-datum
AND datbi GE sy-datum.
clear: tbl_eord[].
SELECT matnr
werks
flifn
INTO TABLE tbl_eord
FROM EORD
FOR ALL ENTRIES IN tbl_eina
WHERE matnr eq tbl_eina-matnr
AND werks EQ p_werks
AND vdatu LE sy-datum
AND bdatu GE sy-datum
AND flifn eq 'X'.
ENDIF.
clear wa_eina.
loop at tbl_eina into wa_eina.
MOVE: wa_eina-matnr TO wa_extract-matnr,
wa_eina-infnr TO wa_extract-infnr,
wa_eina-lifnr TO wa_extract-lifnr,
wa_eina-idnlf TO wa_extract-idnlf,
wa_eina-wglif TO wa_extract-wglif,
wa_eina-meins to wa_extract-meins,
wa_eina-umren to wa_extract-umren,
wa_eina-umrez to wa_extract-umrez,
wa_eina-urzla to wa_extract-urzla,
wa_eina-regio to wa_extract-regio.
CLEAR:wa_eine.
SORT tbl_eine BY infnr ekorg esokx werks.
READ TABLE tbl_eine into wa_eine
WITH KEY INFNR = wa_eina-infnr
EKORG = p_ekorg
ESOKZ = p_esokz
WERKS = p_werks
BINARY SEARCH.
CHECK wa_eine-NETPR NE 0.
MOVE: wa_eine-ekorg TO wa_extract-ekorg,
wa_eine-werks TO wa_extract-werks,
wa_eine-netpr TO wa_extract-netpr,
wa_eine-waers TO wa_extract-waers,
'G' TO wa_extract-type,
wa_eine-esokz TO wa_extract-esokz,
wa_eine-ekgrp TO wa_extract-ekgrp,
wa_eine-aplfz TO wa_extract-aplfz,
wa_eine-bstae TO wa_extract-bstae,
wa_eine-mwskz TO wa_extract-mwskz,
wa_eine-kzabs TO wa_extract-kzabs,
wa_eine-peinh TO wa_extract-peinh,
wa_eine-bprme TO wa_extract-bprme,
wa_eine-bpumn TO wa_extract-bpumn,
wa_eine-bpumz TO wa_extract-bpumz.
CLEAR wa_a017.
SORT tbl_a017 BY lifnr matnr ekorg werks esokz .
READ TABLE tbl_a017 into wa_a017
with key lifnr = wa_lifnr-lifnr
matnr = wa_eina-matnr
ekorg = wa_eine-ekorg
werks = wa_eine-werks
esokz = wa_eine-esokz
BINARY SEARCH.
MOVE: wa_a017-datab TO wa_extract-datab,
wa_a017-datbi TO wa_extract-datbi.
clear wa_eord.
SORT tbl_eord BY matnr werks.
READ TABLE tbl_eord into wa_eord
with key matnr =wa_eina-matnr
werks = wa_eine-werks
BINARY SEARCH.
MOVE: wa_eord-flifn TO wa_extract-flifn,
w_info_text to wa_extract-info_text.
APPEND WA_EXTRACT TO IEXTRACT.
clear : wa_eina,
wa_extract.
ENDLOOP.
I think this should help you in someway. Please check if the final data that you are getting in table iextarcct is the same as what you get when you execute your code below. If not you can always check in de-bug mode where you need to modify thr aboce code. i have not done any syntax check for the above code.
Also tbl_ here stands for the internal table and wa_ stands for the corresponding work areas. You will have to define them accorsingly in your program.
I hope this helps you.
Cheers
Shivika
Message was edited by:
Shivika Bhorchi
05-28-2007 3:58 AM
hey let me know if it helped.
thanks
shivika
Message was edited by:
Shivika Bhorchi
05-29-2007 3:37 PM
05-29-2007 4:22 PM
05-30-2007 12:05 AM
hey great to know that your query is solved and thanx for the points too ..
cheers
shivika