topic Re: removing select statement from loop in Application Development and Automation Discussions

removing select statement from loop

Former Member — Thu, 10 May 2007 06:38:51 GMT

The following code is inside a loop....

hw to rewrite the following select statements which are inside a loop ... by performing them outside the loop because select inside a loop is a performance problem

SELECT SINGLE

knumh

FROM a004 INTO l_knumh

WHERE kappl EQ c_kappl

AND kschl EQ c_zpr0

AND vkorg EQ wa_t001w-vkorg

AND vtweg EQ wa_t001w-vtweg

AND matnr EQ l_matnr

AND datbi >= date

AND datab <=date.

IF sy-subrc EQ 0

SELECT knumh

kbetr

konwa

FROM konp INTO TABLE t_listprice

WHERE knumh EQ l_knumh.

IF sy-subrc EQ 0.

READ TABLE t_listprice INTO wa_listprice

WITH KEY knumh = l_knumh.

Re: removing select statement from loop

Former Member — Thu, 10 May 2007 06:51:56 GMT

You can use inner join.

Re: removing select statement from loop

Former Member — Thu, 10 May 2007 06:54:02 GMT

well if that select is in a loop, then probabaly beacause it NEEDS to be there.

you can NOT pull it out of the loop without loosing your functionality.

if you have performace problems there are several other things you could do.

e.G. Selecting all your A004 Data into an internal table BEFORE that loop, and replacing that select statement with a read table statement on your internal Table.

this will give you at least SOME more performance since you will not have LOTs of DB acesses with almost empty packages.

BTW same goes for your KONP Select

Message was edited by:

Florian Kemmer

Re: removing select statement from loop

Former Member — Thu, 10 May 2007 06:55:26 GMT

Hi Ashwin

I'm not able to understand ur requirement but upto wat i have understood is u want to make a loop for the below select statement if it is the case then u can follow this code or else explain ur requirement clearly


SELECT knumh FROM a004 INTO l_knumh
              WHERE kappl = c_kappl 
                    AND kschl = c_zpr0 
                    AND vkorg = wa_t001w-vkorg
                    AND vtweg = wa_t001w-vtweg
                   AND matnr = l_matnr
                   AND datbi >= date
                   AND datab <=date.
   IF sy-subrc EQ 0.
      Loop at l_knumh.

 
SELECT knumh kbetr konwa FROM konp INTO TABLE t_listprice
                   WHERE knumh = l_knumh.
         READ TABLE t_listprice INTO wa_listprice
    WITH KEY knumh = l_knumh.
MODIFY l_knumh.

endloop.

Re: removing select statement from loop

Former Member — Thu, 10 May 2007 07:03:24 GMT

Hi,

you can use "for all entries" concept here.

before the loop write the select statement,

SELECT SINGLE

knumh

FROM a004 INTO l_knumh

for all entries in (name of the looping internal table 't001w-vkorg')

WHERE kappl EQ c_kappl

AND kschl EQ c_zpr0

AND vkorg EQ name of the internal table-vkorg

AND vtweg EQ name of the internal table-vtweg

AND matnr EQ l_matnr

AND datbi >= date

AND datab <=date.

if sy-subrc = 0.

SELECT knumh

kbetr

konwa

FROM konp INTO TABLE t_listprice

for all entries in (the internal table from above select statement)

WHERE knumh EQ internal table from above select statement-knumh.

endif.

after that write your loop, inside of that loop by using the READ statement on internal table you can do.

Re: removing select statement from loop

RaymondGiuseppi — Thu, 10 May 2007 07:09:40 GMT

What are the criteria from the LOOP in your SELECT, it's not clear for me : l_knumh, wa_t001w records or l_matnr?

<i>You will have to execute a first SELECT INTO TABLE FOR ALL ENTRIES in your internal table, then a second SELECT using the result table, and then two embedded LOOP AT.</i>

Regards

Re: removing select statement from loop

former_member196280 — Thu, 10 May 2007 07:12:39 GMT

Use inner join. I hope below example will solve your issue

Example:

<b>SELECT * INTO CORRESPONDING FIELDS OF TABLE ia004

FROM a004 INNER JOIN konp

ON a004_{KNUMH = konp}KNUMH

WHERE

kappl EQ c_kappl

AND kschl EQ c_zpr0

AND vkorg EQ wa_t001w-vkorg

AND vtweg EQ wa_t001w-vtweg

AND matnr EQ l_matnr

AND datbi >= date

AND datab <=date.</b>

Reward point to all.

Regards,

SaiRam

Re: removing select statement from loop

RaymondGiuseppi — Thu, 10 May 2007 07:18:23 GMT

If you loop at t001w (say tab_t001w) try something like

* First table
  SELECT knumh
    FROM a004
    INTO TABLE tab_knumh
    FOR ALL ENTRIES IN tab_t001w
    WHERE kappl EQ c_kappl
      AND kschl EQ c_zpr0
      AND vkorg EQ tab_t001w-vkorg
      AND vtweg EQ tab_t001w-vtweg
      AND matnr EQ l_matnr
      AND datbi >= date
      AND datab <=DATE.
* Second table
  SELECT knumh kbetr konwa
    FROM konp
    INTO TABLE tab_listprice
    FOR ALL ENTRIES IN tab_knumh
    WHERE knumh EQ tab_knumh-knumh.
  SORT tab_listprice BY knumh.
  DELETE ADJACENT DUPLICATES FROM  tab_listprice COMPARING knumh.
* Loop at result
  LOOP AT tab_knumh.
    READ TABLE tab_listprice
      WITH KEY knumh = tab_knumh-knumh
      BINARY SEARCH.
    IF sy-subrc = 0.
      " your code here
    ENDIF.
  ENDLOOP.

Regards