topic Reg : Select statement in Application Development and Automation Discussions

Reg : Select statement

mallikarjun_vaja — Sat, 26 May 2007 10:59:49 GMT

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.

Re: Reg : Select statement

Former Member — Mon, 28 May 2007 01:12:28 GMT

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

Re: Reg : Select statement

former_member186741 — Mon, 28 May 2007 01:47:09 GMT

TRY SOMETHING LIKE THIS:

SELECT EINA_{MATNR EINA}INFNR EINA_{LIFNR EINA}IDNLF EINA~WGLIF

EINA_{meins EINA}umren EINA_{umrez EINA}urzla EINA~regio

EINE_{EKORG EINE}WERKS EINE_{WAERS EINE}NETPR EINE_{esokz EINE}ekgrp EINE_{aplfz EINE}bstae EINE_{mwskz EINE}kzabs

EINE_{einh EINE}bprme EINE_{bpumn EINE}bpumz

INTO CORRESPONDING FIELDS OF TABLE IEXTRACT

from eina

JOIN EINE ON EINE_{INFNR = EINA}INFNR

JOIN EORD ON EORD_{MATNR = EINA}MATNR

AND EORD_{WERKS = EINE}WERKS

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.

Re: Reg : Select statement

Former Member — Mon, 28 May 2007 02:52:25 GMT

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

Re: Reg : Select statement

Former Member — Mon, 28 May 2007 02:58:36 GMT

hey let me know if it helped.

thanks

shivika

Message was edited by:

Shivika Bhorchi

Re: Reg : Select statement

mallikarjun_vaja — Tue, 29 May 2007 14:37:34 GMT

Hi Shivika,

thanks a lot for ur help.

my query is solved.

Re: Reg : Select statement

mallikarjun_vaja — Tue, 29 May 2007 15:22:24 GMT

Hi shivika,

I have given points for u

thanks once again.

bye

mallik

Re: Reg : Select statement

Former Member — Tue, 29 May 2007 23:05:41 GMT

hey great to know that your query is solved and thanx for the points too ..

cheers

shivika