topic Re: help to remove select statement from loop in Application Development and Automation Discussions

help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:29:10 GMT

please help me in removing select statements within loop statement from the below program,

please give me the code changes to be done

FORM 200_select_data_cont.

LOOP AT itab_cargo.

CLEAR: ekko, ekpo, eket, ekes.

CLEAR gr_quantity.

SELECT h_{bsart h}verkf i_{netpr i}matnr

INTO (ekko-bsart, ekko-verkf, ekpo-netpr, ekpo-matnr)

FROM ekko AS h

INNER JOIN ekpo AS i

ON h_{ebeln = i}ebeln

WHERE h~ebeln = itab_cargo-vgbel

AND i~ebelp = itab_cargo-vgpos

AND h~bsart IN s_bsart.

itab_cargo-bsart = ekko-bsart.

itab_cargo-netpr = ekpo-netpr. " * 100.

itab_cargo-matnr = ekpo-matnr.

itab_cargo-dept = ekko-verkf+9(3).

ENDSELECT.

IF sy-subrc = 0.

SELECT MIN( eindt ) INTO eket-eindt

FROM eket

WHERE ebeln = itab_cargo-vgbel

AND ebelp = itab_cargo-vgpos.

IF sy-subrc = 0.

itab_cargo-eindt = eket-eindt.

ENDIF.

Change by request of M.Soyama to get GR quantity (not PO quantity)
select bpmng into ekbe-bpmng
from ekbe
where ebeln = itab_cargo-vgbel
and ebelp = itab_cargo-vgpos.
gr_quantity = gr_quantity + ekbe-bpmng.
endselect.

SELECT dabmg INTO ekes-dabmg

FROM ekes

WHERE ebeln = itab_cargo-vgbel

AND ebelp = itab_cargo-vgpos

AND xblnr = itab_cargo-charg.

gr_quantity = gr_quantity + ekes-dabmg.

ENDSELECT.

IF sy-subrc = 0.

itab_cargo-dabmg = gr_quantity.

itab_cargo-tot_price = gr_quantity * itab_cargo-netpr.

ENDIF.

CLEAR mvke.

SELECT SINGLE mvgr2 FROM mvke

INTO mvke-mvgr2

WHERE matnr = itab_cargo-matnr

AND vkorg = 'CRJP'

AND vtweg = '00'.

IF sy-subrc = 0.

itab_cargo-dept = mvke-mvgr2.

ENDIF.

MODIFY itab_cargo.

ELSE.

DELETE itab_cargo.

ENDIF.

ENDLOOP.

DESCRIBE TABLE itab_cargo LINES lin_cnt.

ENDFORM. " 200_SELECT_DATA_CONT

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:32:02 GMT

Hi,

you can use for all entries instead of joins within the loop.

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:37:40 GMT

but i want to remove select statements and keep outside loop , so give example for how to proceed

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:39:34 GMT

1.First select statement using for all entries

2.Loop it

3.with in loop use Read statement

example

select * from ekko <condition>

seletc * from ekpo for all entries in it_ekko into table lt_ekpo where <condition>

seletc * from ekbe for all entries in it_ekko into table lt_ekbe where <condition>

SELECT mvgr2 FROM mvke INTO table lt_mvke WHERE matnr = itab_cargo-matnr AND vkorg = 'CRJP' AND vtweg = '00'.

loop at lt_ekpo.

read table lt_ekko with key ...

read table lt_ekbe into le_ekbr with key <condition

read table lt_mvke into wa_mvke index 1.

endloop.

Edited by: dharma raj on Jan 21, 2010 6:12 PM

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:40:31 GMT

Hi,

I suppose you want to fetch the data from table1 for every entry in internal table itab_cargo.

This is how you proceed

There is no need to use Loop endloop.

Example:

SELECT PGMID OBJECT OBJ_NAME FROM TADIR
                    INTO TABLE IT_TADIR
                    FOR ALL ENTRIES IN it_objname
                    WHERE OBJ_NAME = it_objname-obj_name AND
                    OBJECT IN ('REPS','PROG').

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:43:54 GMT

Hello Veeru,

Is it so mandate for you to keep select inside loop. Hope you worry about performance.

Fetch the data first from the primary table and use for all entries for all others as per requirement.

Next loop the main internal table and read all the values from different tables using keys

You can also use binary search and sorting in your code.

Thanks,

Manas M.

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:45:39 GMT

Hi,

Do the below:

Select data from EKKO for all entries in itab_cargo into table it_ekko <add where conditions>.

Select data from EKPO for all entries in it_ekko into table it_ekpo <add where conditions>.

Hence you can avoid loop

Regards

Shiva

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:46:14 GMT

Hi,

Use for all entries to populate data. define one more internal table in which you can store data(ekko-bsart, ekko-verkf, ekpo-netpr, ekpo-matnr).

SELECT h~ebeln h~bsart h~verkf i~netpr i~matnr
INTO itab_new
FROM ekko AS h
INNER JOIN ekpo AS i
ON h~ebeln = i~ebeln
for all entries in itab_cargo
WHERE h~ebeln = itab_cargo-vgbel
AND i~ebelp = itab_cargo-vgpos
AND h~bsart IN s_bsart.

Loop at itab_cargo into wa_cargo.
Read table itab_new into wa_new where ebeln = itab_cargo-vgbel.   

wa_cargo-bsart = wa_new-bsart.
wa_cargo-netpr = wa_new-netpr. " * 100.
wa_cargo-matnr = wa_new-matnr.
wa_cargo-dept = wa_new-verkf+9(3).

Then modify table using 'Modify' transporting bsart netpr matnr dept.

Thanks,

Archana

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:47:46 GMT

please give example for this and for using binary search and sorting

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:48:48 GMT

Hi,

Sample code for FOR ALL ENTRIES

* Selecting data from EKKO on the basis of BUKRS,BSTYP,BSART and LIFNR
  SELECT EBELN BUKRS BSTYP BSART LIFNR RESWK EKGRP KUNNR
     FROM EKKO
     INTO TABLE IT_EKKO
     WHERE BUKRS IN S_BUKRS
     AND BSTYP IN S_BSTYP
     AND BSART IN S_BSART
     AND LIFNR IN S_LIFNR
     AND RESWK EQ P_WERKS.

  IF SY-SUBRC <> 0.
    V_CHECK = 'X'.
    MESSAGE I009.
    STOP.
  ENDIF.
*---------For selecting company code & name
  IT_EKKO_TEMP[] = IT_EKKO[].                              "Populating temporary table
  SORT IT_EKKO_TEMP BY BUKRS.                                   "Sorting the table by BUKRS
  DELETE ADJACENT DUPLICATES FROM IT_EKKO_TEMP COMPARING BUKRS. "deleting adjacent BUKRS duplicates

  IF IT_EKKO_TEMP[] IS NOT INITIAL.
    SELECT BUKRS BUTXT                    "populating compnay code & name in IT_T001
      FROM T001
      INTO TABLE IT_T001
      FOR ALL ENTRIES IN IT_EKKO_TEMP
      WHERE BUKRS = IT_EKKO_TEMP-BUKRS.
  ENDIF.

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:53:02 GMT

hi ,

sort intenal table on that field which you are going to use in binary search

FOR Example:-

SORT IT_T001 BY BUKRS.

  LOOP AT IT_EKKO INTO WA_EKKO.

 READ TABLE IT_T001 INTO WA_T001 WITH KEY BUKRS = WA_EKKO-BUKRS
                                                     BINARY SEARCH.
ENDLOOP.

regards

Gaurav

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:53:06 GMT

hI,

For reading table

1. sort the table first

2. read table with key

LOOP AT ITAB1 INTO WA1.
SORT ITAB BY FIELD. "Fieldname
READ TABLE ITAB INTO WA WITH KEY FIELD = WA1-FIELD1 BINARY SEARCH.
ENDLOOP.

Edited by: Radhika Parag Rajopadhye on Jan 21, 2010 1:53 PM

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 12:58:11 GMT

Hi,

You can use FOR ALL ENTRIES clause.

For the first select statement, declare a seperate internal table it_temp that will hold your values.

Bring it outside the LOOP....ENDLOOP and use FOR ALL ENTRIES addition.

If you want to modify your internal table itab_cargo contents from it_temp, then Loop at itab_cargo table, read the record of the other table and modify the content.

As for the second select statement, select ebeln along with MIN(EINDT) and use FOR ALL ENTRIES addition.

I guess, the same applies for the others too.

Bottom Line: F1 on FOR ALL ENTRIES.

P.S. Probably, I should have posted just the last statement 😐

Re: help to remove select statement from loop

Former Member — Thu, 21 Jan 2010 13:01:59 GMT

Here is a sample code:-

SELECT werks lgort iblnr gjahr zldat sobkz bldat gidat budat
  FROM ikpf
  INTO CORRESPONDING FIELDS OF TABLE it_ikpf
  WHERE werks IN s_werks AND
        lgort IN s_lgort AND
        iblnr IN s_iblnr AND
        gjahr IN s_gjahr AND
        zldat IN s_zldat .

  DESCRIBE TABLE it_ikpf LINES res.
  IF res = 0.
    WRITE:/ 'No records matching the selection criteria'.
    EXIT.
  ENDIF.

  IF NOT it_ikpf[] IS INITIAL.
    SELECT iblnr zeili matnr abcin bstar buchm menge meins dmbtr
    waers mblnr mjahr wrtzl wrtbm
    FROM iseg
    INTO CORRESPONDING FIELDS OF TABLE it_iseg
    FOR ALL ENTRIES IN it_ikpf
    WHERE iblnr = it_ikpf-iblnr
    AND   matnr IN s_matnr
    AND   abcin IN s_abcin.
  ENDIF.

SELECT matnr matkl
  FROM mara
  INTO CORRESPONDING FIELDS OF TABLE it_mara
  FOR ALL ENTRIES IN it_iseg
  WHERE matnr = it_iseg-matnr.
SELECT matnr maktx
  FROM makt
  INTO CORRESPONDING FIELDS OF TABLE it_makt
  FOR ALL ENTRIES IN it_iseg
  WHERE matnr = it_iseg-matnr.

  SELECT werks abcin aninv ininv
  FROM t159c
  INTO CORRESPONDING FIELDS OF TABLE it_t159c
  FOR ALL ENTRIES IN it_iseg
  WHERE abcin = it_iseg-abcin
  AND   werks IN s_werks.

  SELECT matnr bwkey stprs
  FROM mbew
  INTO CORRESPONDING FIELDS OF TABLE it_mbew
  FOR ALL ENTRIES IN it_iseg
  WHERE matnr = it_iseg-matnr.

  SORT it_iseg BY matnr ASCENDING.

 LOOP AT it_iseg INTO wa_iseg.
    MOVE-CORRESPONDING wa_iseg TO wa_output.

    READ TABLE it_ikpf INTO wa_ikpf
                       WITH KEY iblnr = wa_iseg-iblnr
                       BINARY SEARCH.
    IF sy-subrc = 0.
      MOVE:   wa_ikpf-werks  TO  wa_output-werks,
              wa_ikpf-lgort  TO  wa_output-lgort,
              wa_ikpf-iblnr  TO  wa_output-iblnr,
              wa_ikpf-gjahr  TO  wa_output-gjahr,
              wa_ikpf-zldat  TO  wa_output-zldat,
              wa_ikpf-sobkz  TO  wa_output-sobkz,
              wa_ikpf-bldat  TO  wa_output-bldat,
              wa_ikpf-gidat  TO  wa_output-gidat,
              wa_ikpf-budat  TO  wa_output-budat.
    ENDIF.

READ TABLE it_makt INTO wa_makt
                       WITH KEY matnr = wa_iseg-matnr.

    IF sy-subrc = 0.
      MOVE: wa_makt-maktx TO wa_output-maktx.
    ENDIF.

and sooonnnnnnnnnnnnnnnnnnnnnnnnnnn.....

Manas M.