For All Entries

[Former Member]

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 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

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

[seshatalpasai_madala]

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

[Former Member]

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

[Former Member]

hi sudheer,

Whare shall i use the following condition.

IF t_atab-yycatal <> ' '.

You have not considered this.

Regards,

Guru

[Former Member]

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 a_{yycatal b}kbetr b_{kpein b}konwa

INTO table tbl_konp

FROM a528 AS a

INNER JOIN konp AS b

ON a_{knumh = b}knumh

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

[former_member194613]

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> 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

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.

[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

[Former Member]

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.

[former_member194613]

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

[Former Member]

Hi guru,

try to keep select outside loop like this ,

SELECT b_{kbetr b}kpein b_{konwa a}vkorg a_{vtweg a}yycatal INTO it_a528_konp

FROM a528 AS a

INNER JOIN konp AS b

ON a_{knumh = b}knumh

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

[Former Member]

HI mathew,

Thanks for your effort.

Your idea really worked out in performence improving .

thanks for your time and patience .

Best regards,

Guru

[former_member194613]

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