Doubt regarding 'For All Entries' statement

[nivin_varkey]

Hi,

while checking the performance of my SQL statements i stumbled upon something which made me put this query, it would be good if you could help me clear the doubt.

Scenario: To select the selling price conditions for articles(materials) belonging to a particular sales organization from table 'A073'.

let the driver internal table have the following fields.

data: begin of wa_driver,

matnr like mara-matnr, (Material)

vrkme like mvke-vrkme, (sales Unit)

(and some other fields....)

end of wa_driver,

it_driver like table of wa_driver

with key matnr vrkme.

now the selection using 'FOR ALL ENTRIES' statement.

if it_driver[] is initial.

else.

select matnr vrkme knumh

into table it_result

for all entries in it_driver

where kappl = 'V'

and kschl = (my condition)

and vkorg = (my vkorg)

and vtweg = (my vtweg)

and matnr = it_driver-matnr

and vrkme = it_driver-vrkme

and datbi ge sy-datum

and datab le sy-datum.

endif.

i executed the statement but traced it using ST05 and checked the execution plan. The execution happens fine, but the selection which happens is actually complex than it looks..as the optimizer makes all the possible combinations of matnr and vrkme and then makes the selection. that is:

if it_driver has

matnr vrkme

1 EA

2 CAR

the actual selection happens for the combinations:

1 EA

1 CAR

2 EA

2 CAR

why does this happen.. and how can we make this not to happen???

PLEASE: this is not the case of IT_DRIVER being empty.....

[nivin_varkey]

Hi,

while checking the performance of my SQL statements i stumbled upon something which made me put this query, it would be good if you could help me clear the doubt.

Scenario: To select the selling price conditions for articles(materials) belonging to a particular sales organization from table 'A073'.

let the driver internal table have the following fields.

data: begin of wa_driver,

matnr like mara-matnr, (Material)

vrkme like mvke-vrkme, (sales Unit)

(and some other fields....)

end of wa_driver,

it_driver like table of wa_driver

with key matnr vrkme.

now the selection using 'FOR ALL ENTRIES' statement.

if it_driver[] is initial.

else.

select matnr vrkme knumh

into table it_result

for all entries in it_driver

where kappl = 'V'

and kschl = (my condition)

and vkorg = (my vkorg)

and vtweg = (my vtweg)

and matnr = it_driver-matnr

and vrkme = it_driver-vrkme

and datbi ge sy-datum

and datab le sy-datum.

endif.

i executed the statement but traced it using ST05 and checked the execution plan. The execution happens fine, but the selection which happens is actually complex than it looks..as the optimizer makes all the possible combinations of matnr and vrkme and then makes the selection. that is:

if it_driver has

matnr vrkme

1 EA

2 CAR

the actual selection happens for the combinations:

1 EA

1 CAR

2 EA

2 CAR

why does this happen.. and how can we make this not to happen???

PLEASE: this is not the case of IT_DRIVER being empty.....

[Former Member]

Check or the internal table beforre using FOR ALL ENTRIES.

If not itab[] is initial.

Use for all entries here.

Endif.

[Former Member]

if it_driver[] is not initial.

select matnr vrkme knumh

into table it_result

for all entries in it_driver

where kappl = 'V'

and kschl = (my condition)

and vkorg = (my vkorg)

and vtweg = (my vtweg)

and matnr = it_driver-matnr

and vrkme = it_driver-vrkme

and datbi ge sy-datum

and datab le sy-datum.

endif.

FOR ALL ENTRIES will fetch all the records from the table if that table is empty.

Regards

srikanth

[Former Member]

Hi,

When ever you want to use FOR ALL ENTRIES in ITAB, please make sure you check the internal table ITAB is not empty otherwise you end up getting into huge data which kind of gets into infinite loop pass and hence takes up too much time.

Correct usage:

IF not ITAB[] is initial.

select * from <table>

for all entries in ITAB

where <table-field> = itab-field.

Endif.

[Former Member]

hi,

<b> if not it_driver[] is initial.

it_driver_temp[] = it_driver[].

sort it_driver_temp by matnr vrkme.

delete adjacent duplicates from it_driver_temp comparing matnr vrkme.

select....

for all entries in it_driver_temp

....

and matnr = it_driver_temp-matnr

and vrkme = it_driver_temp-vrkme

....

endif.

</b>

move all the entries in it_driver into it_driver_temp and sort it by matnr vrkme and delete adjacent duplicates and use this it_driver_temp in for all entries.

Regards,

Sailaja.

[nivin_varkey]

hi sailaja..

i can delete the duplicate entries.. fine but how to stop the unnecessary selections..

[Former Member]

Hello,

It is a practice that you should always check the internal table whether it is initial or not before using it in a select statement with FOR ALL ENTRIES.

Because even if the internal table is initial and if you use it in the select statement it will fetch all the rows from the other table in the query.

Regs,

Venkat Ramanan N

[Former Member]

I am not 100% sure of the background ..but it seems that it takes the cartesian product of the unique values and runs through the select statement...so in your case it has takes into account 4 combinations.

I feel the only way out is to use only 1 field basically try to concatenate the fields into one and use the offset in the where condition.

Regards

Anurag

[nivin_varkey]

hi anurag..

can u please explain a little bit..

[Former Member]

if it_driver has

matnr vrkme

1 EA

2 CAR

Basically FOR ALL ENTRIES is considering each field as a separate selection and would try and select the entries for each combination...so in your case with the above internal table..it is checking for

1 EA

1 CAR

2 EA

2 CAR

Regards

Anurag

[nivin_varkey]

Amole: they are not different sale area.

Anurag: how can we stop that from happening.. any change in syntax..??

[christian_wohlfahrt]

Hi!

I never heard of such a behavior before (I should have got to much results).

I wrote a small test program to check the behavior and made a SQL-trace: SAP on ORACLE 10.2.0.2.0 is working correct (just two groups of where-clauses combined with OR for the two entries in it_driver).

I think this is a wrong SQL-statement translation (open SQL -> native SQL). Check for OSS-notes of your database / create a OSS-message and let SAP analysis this problem.

Regards,

Christian

[Former Member]

Hi,

matnr vrkme may be in diffrent sales area

this may be the reason for selection

combinations:

1 EA

1 CAR

2 EA

2 CAR

Regards

Amole