topic Re: how to avoid select inside loop in Application Development and Automation Discussions

how to avoid select inside loop

Former Member — Tue, 16 Jan 2007 04:19:54 GMT

Dear Friends,

In one of my programs I am using 3 tables VBRK,VBRP and KONV.I have created one internal table with all fields which is necessary for output..My requirement was to check a particular condition type for (ZVAT) for a bill if it exists I want to select another condition value ( Value of PR00 condition). I selected data from VBRK,VBRP using inner join into an internal table But since it is not possible to select data from Konv using join I started a loop in the first internal table and used a two single select statement inside that loop (One for existence of first condition and second for value if first condition is true).

But I have learned from many sources that select inside a loop also affects the performance of the program like select – endselect.So anybody can suggest an alternate way for avoiding this situation ie. Select inside loop. I have faced similar situations in other programs also. I am attaching code of my program also..

So anybody can check this code and give an alternate solution to avoid select inside the loop and improve performance of my program

SELECT A_{VBELN A}FKART A_{KNUMV A}FKDAT A_{KUNAG B}POSNR B_{ARKTX B}VGBEL INTO TABLE ITAB1

FROM VBRK AS A JOIN VBRP AS B ON A_{VBELN = B}VBELN

WHERE A_{FKDAT IN DATE AND A}FKART NE 'S1' AND B_{VKBUR IN SOFFICE AND B}WERKS IN PLANT.

LOOP AT ITAB1 INTO ITAB1_WA.

SELECT SINGLE KWERT INTO (VATVAL) FROM KONV WHERE KNUMV = ITAB1_WA-KNUMV AND KPOSN = ITAB1_WA-POSNR

AND KSCHL = 'ZVAT'.

IF SY-SUBRC = 0.

ITAB1_WA-VAT = VATVAL.

SELECT SINGLE KWERT INTO (BASP) FROM KONV WHERE KNUMV = ITAB1_WA-KNUMV AND KPOSN = ITAB1_WA-POSNR

AND KSCHL = 'PR00'.

ITAB1_WA-VALUE = BASP .

ITAB1_WA-TOT = ITAB1_WA-VAT + ITAB1_WA-VALUE.

SELECT SINGLE NAME1 INTO (NAME1) FROM KNA1 WHERE KUNNR = ITAB1_WA-KUNAG.

ITAB1_WA-NAME = NAME1.

SELECT SINGLE J_1ILSTNO INTO (VATNO1) FROM J_1IMOCUST WHERE KUNNR = ITAB1_WA-KUNAG.

ITAB1_WA-VATNO = VATNO1.

MODIFY ITAB1 FROM ITAB1_WA.

CLEAR: VATVAL,BASP,NAME1,VATNO1.

ELSE.

DELETE ITAB1 WHERE BILLNO = ITAB1_WA-BILLNO.

ENDIF.

CLEAR: VATVAL,BASP,NAME1,VATNO1.

ENDLOOP.

Thanks in advance,

Joby

Re: how to avoid select inside loop

Former Member — Tue, 16 Jan 2007 04:26:05 GMT

declare internal tables....

write select statements using <b>FOR ALL ENTRIES</b>

after that loop at first internal table.

use read statements...

append data to final internal table.

endloop

Re: how to avoid select inside loop

anversha_s — Tue, 16 Jan 2007 04:28:40 GMT

hi,

1) use

for all entries

2) read the above table inside the loop

Regards

Anver

Re: how to avoid select inside loop

Former Member — Tue, 16 Jan 2007 04:39:13 GMT

Hi John,

Yes the solution is Select with For ALL ENTRIES IN & then read inside a loop.

Here it is how :

SELECT A_{VBELN A}FKART A_{KNUMV A}FKDAT A_{KUNAG B}POSNR B_{ARKTX B}VGBEL INTO TABLE ITAB1

FROM VBRK AS A JOIN VBRP AS B ON A_{VBELN = B}VBELN

WHERE A_{FKDAT IN DATE AND A}FKART NE 'S1' AND B_{VKBUR IN SOFFICE AND B}WERKS IN PLANT.

SELECT KNUMV KPOSN KWERT

INTO TABLE itab5 FROM KONV

FOR ALL ENTRIES IN ITAB1

WHERE KNUMV = ITAB1_WA-KNUMV

AND KPOSN = ITAB1_WA-POSNR

AND KSCHL = 'ZVAT'.

SORT ITAB5 by KNUMV KPOSN.

LOOP AT ITAB1.

Read table itab5 with key

KNUMV = ITAB1_WA-KNUMV

KPOSN = ITAB1_WA-POSNR BINARY SEARCH.

.........

ENDLOOP.

****Similarly replace all ur Select Single Queries inside the loop.

& use Read Table with Binary Search.

This will definately improve the performance of ur Program.

*********Reward useful Answers**************

Re: how to avoid select inside loop

Former Member — Tue, 16 Jan 2007 04:46:59 GMT

John,

Use for all entries here.

SELECT SINGLE KWERT INTO (VATVAL) FROM KONV

INTO TABLE ITAB2

FOR ALL ENTRIES IN ITAB

WHERE KNUMV = ITAB1-KNUMV AND

KPOSN = ITAB1_WA-POSNR

AND KSCHL = 'ZVAT'.

SORT ITAB2 BY KPOSN KSCHL.

LOOP AT ITAB1 INTO ITAB1_WA.

READ TABLE ITAB2 WITH KEY KPOSN = ITAB1_WA-POSNR

AND KSCHL = 'ZVAT' BINARY SEARCH.

ENDLOOP.

Do like above for all select statements which are in loop.

Pls. reward

Re: how to avoid select inside loop

Former Member — Tue, 16 Jan 2007 04:57:03 GMT

Hi Varughesee,

I encountered similar situation in my program & coded it in following way that won't hinder performance.

SELECT A_{EQUNR B}DATAB B_{ILOAN C}SWERK

INTO TABLE ITEQ

FROM ( EQUI AS A INNER JOIN EQUZ AS B

ON A_{EQUNR = B}EQUNR ) INNER JOIN ILOA AS C

ON B_{ILOAN = C}ILOAN

WHERE A~EQART = 'ESTPL'

AND B~IWERK = 'M011'

AND B~INGRP = 'SLM'

AND B_{DATAB GE SDATE-LOW AND B}DATAB LE SDATE-HIGH

AND C~SWERK IN S_WERK.

IF SY-SUBRC = 0.

SELECT ILOAN

TPLNR

MSGRP FROM ILOA

INTO CORRESPONDING FIELDS OF TABLE ITABOA

FOR ALL ENTRIES IN ITEQ

WHERE ILOAN = ITEQ-ILOAN.

SORT ITABOA BY ILOAN.

LOOP AT ITEQ.

READ TABLE ITABOA WITH KEY ILOAN = ITEQ-ILOAN BINARY SEARCH.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITABOA TO ITEQ.

MODIFY ITEQ TRANSPORTING TPLNR MSGRP.

ENDIF.

ENDLOOP.

SELECT TPLNR PLTXT FROM IFLO

INTO CORRESPONDING FIELDS OF TABLE ITABLO

FOR ALL ENTRIES IN ITEQ

WHERE TPLNR = ITEQ-TPLNR.

SORT ITABLO BY TPLNR.

LOOP AT ITEQ.

READ TABLE ITABLO WITH KEY TPLNR = ITEQ-TPLNR BINARY

SEARCH.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITABLO TO ITEQ.

MODIFY ITEQ TRANSPORTING PLTXT.

ENDIF.

ENDLOOP.

ENDIF.

Hope this helps.

Reward if helpful.

Regards,

Sipra

Re: how to avoid select inside loop

Former Member — Tue, 16 Jan 2007 07:21:53 GMT

Thank you all for your replies.But please note my requirement, I want to select the value (kwert) corresponding to 'PR00' condition if there another record with same key exists with 'ZVAT' condition , otherwise I want to delete the record.Please help me to solve this problem

Re: how to avoid select inside loop

Former Member — Tue, 16 Jan 2007 07:28:42 GMT

Hi John,

Yes ur requirements r meet.

Here it is how :

SELECT A_{VBELN A}FKART A_{KNUMV A}FKDAT A_{KUNAG B}POSNR B_{ARKTX B}VGBEL INTO TABLE ITAB1

FROM VBRK AS A JOIN VBRP AS B ON A_{VBELN = B}VBELN

WHERE A_{FKDAT IN DATE AND A}FKART NE 'S1' AND B_{VKBUR IN SOFFICE AND B}WERKS IN PLANT.

SELECT KNUMV KPOSN KWERT

INTO TABLE itab5 FROM KONV

FOR ALL ENTRIES IN ITAB1

WHERE KNUMV = ITAB1_WA-KNUMV

AND KPOSN = ITAB1_WA-POSNR

AND KSCHL = 'ZVAT'.

SORT ITAB5 by KNUMV KPOSN.

SELECT KNUMV KPOSN KWERT

INTO TABLE itab6 FROM KONV

FOR ALL ENTRIES IN ITAB1

WHERE KNUMV = ITAB1_WA-KNUMV

AND KPOSN = ITAB1_WA-POSNR

AND KSCHL = 'PR00'.

SORT ITAB6 by KNUMV KPOSN.

LOOP AT ITAB1.

Read table itab5 with key

KNUMV = ITAB1_WA-KNUMV

KPOSN = ITAB1_WA-POSNR BINARY SEARCH.

if sy-subrc = 0.

Read table itab6 with key

KNUMV = ITAB1_WA-KNUMV

KPOSN = ITAB1_WA-POSNR BINARY SEARCH.

if sy-subrc = 0.

............

...............

endif.

endif

ENDLOOP.

GOT IT !!!!!!!!!!!!!!!!

*********Reward useful Answers**************