topic Re: LOOP....ENDLOOP and SELECT Statement Issue in Application Development and Automation Discussions

LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 19:04:05 GMT

Hi All,

Following is the query.


*BSIK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 INTO CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSIK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.
*BSAK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 APPENDING CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSAK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.
"Here my ITAB has 465322 records.
LOOP AT ITAB.
SELECT SINGLE AWKEY INTO G_AWKEY FROM BKPF CLIENT SPECIFIED WHERE MANDT EQ SY-MANDT 
AND BUKRS EQ P_BUKRS AND BELNR EQ ITAB-BELNR AND GJAHR EQ ITAB-GJAHR.
IF SY_SUBRC = 0.
SELECT SINGLE EBELN INTO G_EBELN FROM RSEG CLIENT SPECIFIED WHERE MANDT EQ SY-MANDT 
AND BELNR EQ G_AWKEY(10) AND GJAHR EQ ITAB-GJAHR AND EBELN IN S_EBELN.
        IF NOT G_EBELN IS INITIAL.
        ITAB-EBELN = G_EBELN.
        MODIFY ITAB.
        ELSE.
        DELETE ITAB INDEX SY-TABIX.
        ENDIF.
ELSE.
DELETE ITAB INDEX SY_TABIX.
ENDIF.
ENDLOOP.

Here my LOOP......ENDLOOP is taking too much time (15 minutes). Can anybody suggest me what to do in this case. I have also tried FOR ALL ENTRIES. It is also taking lot of time. This is my first time I am working with FI data and Tables.I have also searched GOOGLE. But no use.Please suggest is there any other way to achieve this.

Thanks.

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 19:32:41 GMT

Hi Nani

You should do your data selection one time and out of Loop EndLopp

Try with this

IF  NOT ITAB[]  IS INITIAL

SELECT *  
INTO  TABLE  WT_BKPF
FROM BKPF CLIENT SPECIFIED 
FOR ALL ENTRIES IN ITAB
WHERE MANDT EQ SY-MANDT 
    AND BUKRS EQ P_BUKRS 
    AND BELNR EQ ITAB-BELNR 
   AND GJAHR EQ ITAB-GJAHR.

IF SY-SUBRC EQ 0.

SORT  WT_BKPF BY BELNR GJAHR . 

SELECT  * 
   INTO TABLE  WT_RSEG
FROM RSEG CLIENT SPECIFIED 
FOR ALL ENTRIES IN  ITAB 
   WHERE MANDT EQ SY-MANDT 
    AND GJAHR EQ ITAB-GJAHR 
    AND EBELN IN S_EBELN.

IF SY-SUBRC EQ 0.

   SORT  WT_RSEG  BY BELNR GJAHR  EBELN . 

ENDIF.


ENDIF.

LOOP AT ITAB

   READ TABLE  WT_BKPF  
   WITH KEY   BELNR =   ITAB-BELNR
                   GJAHR  =   ITAB-GJAHR
    BINARY SERACH.

  IF SY-SUBRC EQ 0.

    READ TABLE  WT_RSEG
     WITH KEY   BELNR =   ITAB-BELNR
                       GJAHR  =   ITAB-GJAHR
                      BELNR = WT_BKPF -G_AWKEY(10)
        BINARY SERACH.

  IF SY-SUBRC EQ 0.

        IF NOT    WT_RSEG-EBELN IS INITIAL.
        
            ITAB-EBELN =   WT_RSEG-EBELN.
            MODIFY ITAB.
    
         ENDIF.


  ELSE.

        DELETE ITAB INDEX SY-TABIX.
  ENDIF.


ELSE.


DELETE ITAB INDEX SY_TABIX.

ENDIF.
.


ENDLOOP

ENDIF.

Best Regards,

Hamza KOUACHI

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 19:41:36 GMT

Hi Hamza,

Thanks for the reply. Can you also tell me how to declare WT_BKPF and WT_RSEG?

Thanks.

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:00:18 GMT

Hi Nani,

there are more way to declare it

You can use this

DATA: WT_BKPF TYPE bkpf WITH HEADER LINE.

DATA: WT_RSEG TYPE rseg WITH HEADER LINE.

you can also declare TYPES with only fields wich you need

Best Regards,

Hamza KOUACHI

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:04:15 GMT

Hi,

Also, make a field symbol of your internal table and work area type that will considerably improve the performance.

Please go thru the Document: http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb387a358411d1829f0000e829fbfe/content.htm

Hope this helps you!

Reetesh

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:05:29 GMT

Hi Hamza,

I have declared the internal tables as you said. But my select * from RSEG is taking too much time. I can say it is forever thru that select * statement.

I think it's a performance issue. Any suggestions?

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:08:51 GMT

Hi Reetesh,

Thanks for the reply. Can you please give me an example/example link instead of sap help link.

Thanks.

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:13:56 GMT

As a rule NEVER do a SELECT *, unless you really want all the records which is very unlikely. And also use key fields in where conditions and remove MANDT field from your query.

As discussed earlier you <FS> instead of Internal Tables and Work area to get better result because this uses reference (memory address) hence to and fro is quicker.

Thank you!

Reetesh

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:22:31 GMT

Hi ,

Use delete adjacent duplicates command like mentioned below.


itab2 = itab.

delete ADJACENT DUPLICATES FROM itab2 COMPARING gjahr.
SELECT  * 
   INTO TABLE  WT_RSEG
   FROM RSEG CLIENT SPECIFIED 
   FOR ALL ENTRIES IN  ITAB2 
          WHERE MANDT EQ SY-MANDT 
          AND GJAHR EQ ITAB2-GJAHR 
          AND EBELN IN S_EBELN.

Thanks,

Vijay

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:25:35 GMT

Here you go:

http://help-abap.zevolving.com/2009/12/use-of-field-symbols-vs-work-area/

http://sample-code-abap.blogspot.com/2008/07/sap-abap-dynamic-internal-table-and.html

Reetesh

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:34:02 GMT

Nani - have you run transaction ST05 to see where the problem is?

Rob

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 20:35:47 GMT


As a rule NEVER do a SELECT *, unless you really want all the records which is very unlikely. And also use key fields in where conditions and remove MANDT field from your query.

SELECT * does not return all the records. It simply returns all of the fields of a single record. It is unlikely that this would be a big problem.

Rob

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 22:20:59 GMT

Hi Rob,

Thanks for the reply.

I have run SE30 and not able to get thru the statement.


SELECT  belnr gjahr ebeln  INTO TABLE  WT_RSEG
FROM RSEG FOR ALL ENTRIES IN  ITAB 
   WHERE GJAHR EQ ITAB-GJAHR 
       AND EBELN IN S_EBELN.

I think my WHERE condition is not satisfied because I have not

specified BELNR in my WHERE condition. I tried specifying

BELNR in WHERE condition as below.


SELECT  belnr gjahr ebeln  INTO TABLE  WT_RSEG
FROM RSEG FOR ALL ENTRIES IN ITAB 
   WHERE belnr eq itab-awkey     "I have also tried belnr eq itab-awkey+0(10)
       AND GJAHR EQ ITAB-GJAHR 
       AND EBELN IN S_EBELN.

But it is giving me an error saying that "When you use the addition "FOR ALL ENTRIES IN itab" the fields "BELNR" and "ITAB_AWKEY" must have the same type and same length.

How do I need to declare my BELNR/AWKEY. Please suggest.

Thanks.

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Tue, 08 Nov 2011 22:42:11 GMT

hi,

or check the LDB ( Logical data base which suits for your requirement)

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Wed, 09 Nov 2011 00:58:12 GMT

Hi All,

This is how I have modified my code.


*BSIK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 INTO CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSIK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.
*BSAK
SELECT A~LIFNR A~BELNR A~XBLNR A~BLART A~BUDAT A~GJAHR A~SHKZG A~DMBTR 
A~WRBTR A~DMBT1 A~DMBT2 B~NAME1 APPENDING CORRESPONDING FIELDS OF TABLE ITAB 
FROM BSAK AS A INNER JOIN LFA1 AS B ON A~LIFNR EQ B~LIFNR WHERE BUKRS EQ 
P_BUKRS AND BUDAT IN S_BUDAT AND A~LIFNR IN S_LIFNR AND A~BELNR IN S_BELNR 
AND A~BLART IN S_BLART.

IF NOT ITAB[] IS INITIAL.
    SELECT BELNR GJAHR AWKEY INTO TABLE IT_BKPF
                             FROM BKPF FOR ALL ENTRIES IN ITAB
                             WHERE BUKRS EQ P_BUKRS
                               AND BELNR EQ ITAB-BELNR
                               AND GJAHR EQ ITAB-GJAHR.
    IF SY-SUBRC EQ 0.
      SORT IT_BKPF BY BELNR GJAHR.
 "This select statement is taking forever. (performance issue)
      SELECT BELNR GJAHR EBELN INTO TABLE IT_RSEG   
                               FROM RSEG FOR ALL ENTRIES IN ITAB
                               WHERE GJAHR EQ ITAB-GJAHR
                                 AND EBELN IN S_EBELN.
      IF SY-SUBRC EQ 0.
        SORT IT_RSEG BY BELNR GJAHR EBELN.
      ENDIF.
    ENDIF.
    LOOP AT ITAB.
      READ TABLE IT_BKPF INTO WA_BKPF WITH KEY BELNR =   ITAB-BELNR
                GJAHR =   ITAB-GJAHR BINARY SEARCH.
      IF SY-SUBRC EQ 0.
        READ TABLE IT_RSEG INTO WA_RSEG WITH KEY BELNR =   ITAB-BELNR
                   GJAHR =   ITAB-GJAHR BINARY SEARCH.
* BELNR = WA_BKPF-AWKEY(10) BINARY SEARCH.
                      "I am getting error-Cant pass BELNR two times
        IF SY-SUBRC EQ 0.
          IF NOT WA_RSEG-EBELN IS INITIAL.
            ITAB-EBELN =   WA_RSEG-EBELN.
            MODIFY ITAB.
          ENDIF.
        ELSE.
          DELETE ITAB INDEX SY-TABIX.
        ENDIF.
      ELSE.
        DELETE ITAB INDEX SY-TABIX.
      ENDIF.
    ENDLOOP.
  ENDIF.

Any suggestions. Please help. Examples are really appreciated.Also let me know if there is any other way to achieve this. I have not worked with Field symbols earlier. If using FIELD SYMBOLS solves the issues please let me know how do I need to use them in my case.

Thanks.

Edited by: Rob Burbank on Nov 10, 2011 10:17 AM

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Wed, 09 Nov 2011 04:21:32 GMT

oops, well that is what I meant to say, may be it was not clear, enough! Though what is clear is when you look to optimize your query you should not use '*' as the best thing you can do.

Reetesh

Re: LOOP....ENDLOOP and SELECT Statement Issue

yuri_ziryukin — Wed, 09 Nov 2011 08:38:28 GMT

Well, Nani, you have made many mistakes in your report.

1) When selecting data from BSIK and BSAK you get over 400.000 entries. If you look at them, they are "item-based". So for each accounting document you'll have many items in your ITAB.

Your next SELECT statement from BKPF is using only document header.

So, this is your first mistake.

So if you really need to prove the existence of the corresponding entries in BKPF, you should copy ITAB to another table with the same structure, sort it by BELNR, GJAHR and delete adjacent duplicates before making a select from BKPF:

ITAB2[] = ITAB[],
sort ITAB2 by BELNR GJAHR.
delete adjacent duplicates from ITAB2 comparing BELNR GJAHR.

2) Why do you need a check against BKPF? If I understand the table structire correctly, BSIK and BSAK are "index" tables. From my understanding there will ALWAYS be an entry in BKPF for the corresponding enries in BSAK and BSIK. Otherwise it's a data inconsistency issue which needs to be resolved.

If my understanding is correct, the select from BKPF and subsequent READ TABLE inside the loop should be removed.

3) Your select from RSEG is completely useless in a way you have it now.

I guess you need to change it like this (keep in mind that you need to implement point 1 before this select):

IF ITAB2[] IS NOT INITIAL.

SELECT BELNR GJAHR EBELN INTO TABLE IT_RSEG

FROM RSEG FOR ALL ENTRIES IN ITAB2

WHERE BELNR EQ ITAB2-BELNR AND

GJAHR EQ ITAB2-GJAHR

AND EBELN IN S_EBELN.

ENDIF.

4) Use field symbols for ITAB and IT_RSEG.

Adjust your coding:

field-symbols: <fs_itab> like line of itab,
             <fs_rseg> like line of it_rseg.

LOOP AT ITAB assigning <fs_itab>.
* Think about removing the below check completely! See my point 2.
      *READ TABLE IT_BKPF INTO WA_BKPF WITH KEY BELNR =   ITAB-BELNR*
                *GJAHR =   ITAB-GJAHR BINARY SEARCH.*
      IF SY-SUBRC EQ 0.
        READ TABLE IT_RSEG ASSIGNING <FS_RSEG> WITH KEY BELNR = <FS_ITAB>-BELNR
                   GJAHR =  <FS_ITAB>-GJAHR BINARY SEARCH.
        IF SY-SUBRC EQ 0.
          IF NOT <FS_RSEG>-EBELN IS INITIAL.
            <FS_ITAB>-EBELN = <FS_RSEG>-EBELN.
*            MODIFY ITAB. "not needed anymore
          ENDIF.
        ELSE.
          DELETE ITAB. "index specification not required in loop
        ENDIF.
      ELSE.
        DELETE ITAB. "index specification not required in loop
      ENDIF.
    ENDLOOP.

Regards,

Yuri

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Thu, 10 Nov 2011 09:25:11 GMT

Hi,

Quite strange that nobody pointed out some of the performance standards: Use SORTED tables with unique keys (Removes any SORT command). DO NOT use statement DELETE inside your LOOP. Instead use a DELETE WHERE outside...

Kr,

Manu.

Re: LOOP....ENDLOOP and SELECT Statement Issue

yuri_ziryukin — Thu, 10 Nov 2011 09:42:37 GMT


Hi,
> 
1) Use SORTED tables with unique keys (Removes any SORT command).
2) DO NOT use statement DELETE inside your LOOP. Instead use a DELETE WHERE outside...
> 
> Kr,
> Manu.

1) Yes, sorted table would be an option, but since when using sorted tables and moreover UNIQUE keys became a STANDARD? And I would like to see a proof link.

2) Really? Why? And please show me the coding sample to delete all entries from ITAB1 where the corresponding entry does not exist in ITAB2 using DELETE WHERE outside of the loop.

Re: LOOP....ENDLOOP and SELECT Statement Issue

Former Member — Thu, 10 Nov 2011 09:58:33 GMT

1) Sorted table would prevent the use of an inefficient SORT command on such big table, which is quite enough to avoid using standard one

2) Well, I think this is quite obvious that 1 DELETE is more efficient that many nested DELETE...

This could be done easily by maintaining a flag on itab instead of statement DELETE in your example.

here you are:


LOOP AT ITAB assigning <fs_itab>.
* Think about removing the below check completely! See my point 2.
      *READ TABLE IT_BKPF INTO WA_BKPF WITH KEY BELNR =   ITAB-BELNR*
                *GJAHR =   ITAB-GJAHR BINARY SEARCH.*
      IF SY-SUBRC EQ 0.
        READ TABLE IT_RSEG ASSIGNING <FS_RSEG> WITH KEY BELNR = <FS_ITAB>-BELNR
                   GJAHR =  <FS_ITAB>-GJAHR BINARY SEARCH.
        IF SY-SUBRC EQ 0.
          IF NOT <FS_RSEG>-EBELN IS INITIAL.
            <FS_ITAB>-EBELN = <FS_RSEG>-EBELN.
*            MODIFY ITAB. "not needed anymore
          ENDIF.
        ELSE.
          "DELETE ITAB. "index specification not required in loop
          <fs_itab>-del = 'X'.
        ENDIF.
      ELSE.
        "DELETE ITAB. "index specification not required in loop
         <fs_itab>-del = 'X'.
      ENDIF.
ENDLOOP.
DELETE itab WHERE del = 'X'.

Kr,

Manu.

Edited by: Manu D'Haeyer on Nov 10, 2011 10:58 AM