Skip to Content
author's profile photo Former Member
Former Member

For All Entries

HI All ,

With reference to below code how can i use for all entries in this case.

As i need to compare with one of the field from the table that is used for all entreis.

i.e t_atab-yycatal <> ' '. this also should be condired.

So in this case how can i use for all entries command.

*********************************************************************

LOOP AT t_atab.

IF t_atab-yycatal <> ' '.

SELECT bkbetr bkpein b~konwa INTO (t_atab-kbetr_pr00, t_atab-kpein_pr00, t_atab-konwa_pr00)

FROM a528 AS a

INNER JOIN konp AS b

ON aknumh = bknumh

WHERE a~yycatal = t_atab-yycatal

AND a~kschl = 'PR00'

AND a~datbi >= sy-datum

AND a~datab <= sy-datum

AND a~vkorg = t_atab-vkorg

AND a~vtweg = t_atab-vtweg.

ENDSELECT.

MODIFY t_atab.

endloop.

******************************************************

Any input will be helpful

Regards,

Guru

Add a comment
10|10000 characters needed characters exceeded

Related questions

8 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2007 at 07:15 AM

    Hi guru,

    try to keep select outside loop like this ,

    SELECT bkbetr bkpein bkonwa avkorg avtweg ayycatal INTO it_a528_konp

    FROM a528 AS a

    INNER JOIN konp AS b

    ON aknumh = bknumh

    WHERE a~kschl = 'PR00'

    AND a~datbi >= sy-datum

    AND a~datab <= sy-datum.

    sort table it_a528_konp.

    loop at t_atab.

    IF t_atab-yycatal <> ' '.

    read table it_a528_konp into wa_a528_konp with key yycatal = t_atab-yycatal and

    vkorg = t_atab-vkorg and

    vtweg = t_atab-vtweg.

    t_atab-kbetr_pr00 = wa_a528_konp-kbetr.

    t_atab-kpein_pr00 = wa_a528_konp-kpein.

    t_atab-konwa_pr00 = wa_a528_konp-konwa.

    MODIFY t_atab.

    endif.

    endloop.

    Regards,

    Sheron

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 05, 2007 at 07:23 AM

    30k means 30.000 is not really much, and should never need 5hours.

    I guess your internal table has 30.000 entries.

    I would recommend you to try 100 entries in t_atab and run the SQL trace.

    Check the join, how long does it need?

    How many records are in the two DB tables? What are the key fields, what are the fields of secondary indices. If your join does not work in this way it will not become better with a FOR ALL ENTRIES.

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2007 at 07:34 AM

    sorry, the last recommendation is no improvement, leaving out the condition on a~yycatal can increase the selected records dramatically (can, I do not know the actual situtation).

    !!!! AND NEVER use loop itab1, read itab2 when the read does not use a binary search, i.e. is a sorted table or a standard table with binary search !!!!!

    I read this in every second posting and it is simply a big performance bug!

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2007 at 11:29 AM

    Hi,

    Try

    DELETE table t_atab where yycatal is INITIAL. "Delete all entries where yycatak is initial.
    
    SELECT b~kbetr b~kpein b~konwa INTO (t_atab-kbetr_pr00, t_atab-kpein_pr00, t_atab-konwa_pr00)
    FROM a528 AS a
    INNER JOIN konp AS b
    ON a~knumh = b~knumh
    FOR ALL ENTRIES IN t_atab
    WHERE a~yycatal = t_atab-yycatal
    AND a~kschl = 'PR00'
    AND a~datbi >= sy-datum
    AND a~datab <= sy-datum
    AND a~vkorg = t_atab-vkorg
    AND a~vtweg = t_atab-vtweg.
    MODIFY t_atab. " Modify in SELECT.. ENDSELECT You dont need one more loop.
    ENDSELECT.
    

    Regards,

    Sesh

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2007 at 11:33 AM

    Hi,

    SELECT b~kbetr b~kpein b~konwa INTO (t_atab-kbetr_pr00, t_atab-kpein_pr00, t_atab-konwa_pr00)
    FROM a528 AS a
    INNER JOIN konp AS b
    ON a~knumh = b~knumh
    for all entries in t_itab      " Here is the for all entries command
    WHERE a~yycatal = t_atab-yycatal
    AND a~kschl = 'PR00'
    AND a~datbi >= sy-datum
    AND a~datab <= sy-datum
    AND a~vkorg = t_atab-vkorg
    AND a~vtweg = t_atab-vtweg.

    Regards

    Sudheer

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2007 at 12:00 PM

    Hi Guru,

    first my suggestion is dont use SELECT statement with in loop.

    Hope follwoing code is helpful for u reg performance wise and for all entries in.

    Data : t_atab_temp like t_atab occurs 0 with head line.

    data :begin of tbl_konp occurs 0,

    yycatal ,

    kbetr,

    kpein,

    konwa,

    end of tbl_konp.

    MOVE t_atab to t_atab_temp .

    DELETE t_atab_temp where t_atab_temp-yycatal is initial.

    if not t_atab_temp[] is initial.

    SELECT ayycatal bkbetr bkpein bkonwa

    INTO table tbl_konp

    FROM a528 AS a

    INNER JOIN konp AS b

    ON aknumh = bknumh

    FOR ALL ENTRIES IN t_atab_temp

    WHERE a~yycatal = t_atab_temp-yycatal

    AND a~kschl = 'PR00'

    AND a~datbi >= sy-datum

    AND a~datab <= sy-datum

    AND a~vkorg = t_atab_temp-vkorg

    AND a~vtweg = t_atab_temp-vtweg.

    if sy-subrc = 0.

    sort tbl_konp by yycatal

    endif.

    if not tbl_knop[] is initial.

    loop at t_atab.

    clear tbl_konp.

    read tbl_konp with key t_atab_temp-yycatal binary search.

    if sy-subrc = 0.

    t_atab-kbetr_pr00 = tbl_knop-kbetr.

    t_atab-kpein_pr00 = tbl_knop-kpein.

    t_atab-konwa_pr00= tbl_knop-konwa.

    MODIFY t_atab transporting kbetr kpein konwa.

    clear t_atab.

    endif.

    endloop.

    endif.

    <b>NOTE : Make sure the fileds used in WHERE clause should be KEYfields and and they should be same order in table A528. if it then peformance wise good.</b>

    <b>Reward with points if useful.</b>

    Regards,

    Vijay

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2007 at 01:10 PM

    Why do you want to use the 'For all entries'? Is there really a performance problem with your coding? Combining joins with FOR ALL ENTRIES can make things worse. Always check whehter your join itself shows good performance!

    Note, the select must be a select single, you can use only one line. And you modify only if you get a result!

    Siegfried

    LOOP AT t_atab.

    IF t_atab-yycatal <> ' '.

    SELECT <b>SINGLE</b> bkbetr bkpein b~konwa INTO (t_atab-kbetr_pr00, t_atab-kpein_pr00, t_atab-konwa_pr00)

    FROM a528 AS a

    INNER JOIN konp AS b

    ON aknumh = bknumh

    WHERE a~yycatal = t_atab-yycatal

    AND a~kschl = 'PR00'

    AND a~datbi >= sy-datum

    AND a~datab <= sy-datum

    AND a~vkorg = t_atab-vkorg

    AND a~vtweg = t_atab-vtweg.

    IF sy-subrc EQ 0.

    MODIFY t_atab.

    ENDIF.

    endloop.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      hi Boes,

      Yes there is performence issue.

      This code is taking 5 hours to execute as there are 30k records .

      So i was asked to improve the performence.

      But stuck up in coding this condition . IF t_atab-yycatal <> ' '.

      as i am using the t_atab as the table for all entries and above condition also should be satisfied by comparing with same table .

      Regards,

      Guru

  • author's profile photo Former Member
    Former Member
    Posted on Sep 05, 2007 at 05:37 AM

    Hi! Your select statement seems getting much complicated.

    Try the following once whether it may be useful.

    Create separate internal tables for KONP and A528. Get the data ifrom KONP first as per your requirement. Then get the data from A528 for these records. Then you can use FOR ALL ENTRIES for A528 internal table to fill t_atab.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.