topic Re: FOR ALL ENTRIES COMMAND in Application Development and Automation Discussions

FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 06:57:09 GMT

I am confused as 2 wat does the FOR ALL ENTRIES command exactly do.

Plz help me out...

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 06:59:03 GMT

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

Reward points if useful

Regards

Anji

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 07:01:05 GMT

Use of FOR ALL Entries

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

rewards if helpfull

Regards

Pavan

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 07:01:34 GMT

When you are trying to fetch data from table, we are restricting using where condition by passing the value...this is one method...

During run time, we want to restrict the data retrival based on another internal table....for that we use FOR ALL ENTRIES

for example

itab1 has company_code as field and have value 100 200 & 300

If you use select * from T001 into itab2 for all entires in itab1 where bukrs = itab1-company_code....only values 100, 200 & 300 if exist in table t001 will be fetched.

reward if useful/..

Regards

Suresh

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 07:01:44 GMT

hi mohit,

if u have selected some records from ekko into <itab>,

then if want select records from ekpo which are related to header fields from ekko then u can use for all entries code, as follows

if not itab[] is initial.

select ebeln

ebelp from ekpo

into <itab1>

for all entries in <itab>

where eblen = <itab-ebeln>

endif.

regards,

seshu.

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 07:02:04 GMT

Hi,

with the FOR ALL ENTRIES command u get get the data based on another internal table.

Means based on ur selection u have filled one internal table. based on that criteria u want another filter,then u can use this command. using where command in condition

reagards,

shardul shah

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 07:03:11 GMT

Hi,

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

SELECTING RECORDS FROM BKPF TABLE BASED ON THE CONDITION *

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

SELECT bukrs gjahr budat belnr blart

FROM bkpf INTO TABLE itab_bkpf

WHERE bukrs IN s_bukrs AND

gjahr EQ p_year AND

budat IN s_budat.

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

SELECTING DEBIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *
NUMBER SELECTED FROM BKPF *

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

IF NOT itab_bkpf[] IS INITIAL.

SELECT bukrs gjahr belnr buzei

hkont shkzg wrbtr pswsl

dmbtr sgtxt zuonr

FROM bseg INTO TABLE itab_bseg_debit

FOR ALL ENTRIES IN itab_bkpf

WHERE bukrs EQ itab_bkpf-bukrs AND

belnr EQ itab_bkpf-belnr AND

gjahr EQ itab_bkpf-gjahr AND

hkont IN s_dbacct AND

shkzg EQ c_debit AND

dmbtr IN s_amt.

We can use FOR ALL ENTRIES command

in this way.

Using this we can select records depending

on the first select statement.

Use to avoid Join condition.

Reward for useful answers.

<b>Regards,

Jackie.</b>

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 07:07:10 GMT

hi mohit,

itab1 -- we r having some records from pa0001

in itab2 we hav to select the join date for all the employees who are in itab1.

then instead of using a this way....

loop at itab1

select *

end loop.

we can use FOR ALL ENTRIES like this

if itab1[] is not initial.

select pernr begda

from pa0002

for all entries in itab1

into table itab2

where pernr eq itab1-pernr.

endif.

reply back for any more clarifications..

with reagrds,

S.barani

Message was edited by:

S BHARANIDARAN

Re: FOR ALL ENTRIES COMMAND

Former Member — Tue, 22 May 2007 07:07:39 GMT

Hi,

1) For all entries is used when you have to fetch the data from a DB table based

on the values of an itab.

2) Suppose u hv selected particular MATNR values from MARA and now for these

material number u hv to get the some filed from MARC u can user FOR ALL

ENTRIES in ITAB_MARA.

3) FOR ALL ENTRIES resuces select query in nested loops

Thanks

Sandeep

Reward if useful