07-13-2006 1:58 PM
07-13-2006 2:01 PM
Hello,
When you want to select from a table based on your selections that you have already done. you go for for all entires.
you can also use joins instead of for all entries..
Reward if it helps.
Regards,
krishna
07-13-2006 2:02 PM
Hi
When u need to upload the data from a table once:
DATA: T_VBAP LIKE TABLE OF VBAP WITH HEADER LINE,
T_MARA LIKE TABLE OF MARA WITH HEADER LINE.
SELECT * FROM VBAP INTO TABLE T_VBAP WHERE VBELN IN SO_VBLEN.
SELECT * FROM MARA INTO TABLE T_MARA
FOR ALL ENTRIES IN T_VBAP
WHERE MATNR = T_VBAP-MATNR.
This statament is like:
LOOP AT T_VBAP.
SELECT SINGLE * FROM MARA INTO T_MARA
WHERE MATNR = T_VBAP-MATNR.
APPEND T_MARA.
ENDLOOP.
But the first type of select is better for the performance.
Max
07-13-2006 2:02 PM
Hi,
FOR ALL ENTRIES is used in select statements referencing another internal table. For example,
select * from dbtab1
into table it_dbtab1.
select * from dbtab2
into table it_dbtab2
for all entries in it_dbtab1
where field1 = it_dbtab1-field1
and field2 = it_dbtab1-field2.
Regards.
Please award points if useful.
07-13-2006 2:03 PM
When you loop an itab wit select inside loop for every entry. Example:
This:
LOOP AT t_ekko.
select * from EKPO
WHERE ebeln EQ t_ekko-ebeln.
ENDLOOP.
Can be replaced by:
select * from ekpo
for all entries in t_ekko
where ebeln EQ t_ekko-ebeln.
Avoiding loop-endloop
Regards.
07-13-2006 2:03 PM
Hi vijayreddyk,
1. we use FOR ALL ENTRIES,
if we want to select some entries from DB Table,
w.r.t to
some list which we already have, in another internal table.
2. EG
We have a list of 2 companies BUKRS, in our internal table.
We want to get data from T001 table,
for these 2 companies only.
3. to get a taste of it, just copy paste in new program.
4.
REPORT ABC.
DATA : ITAB LIKE TABLE OF T001 WITH HEADER LINE.
DATA : T001 LIKE TABLE OF T001 WITH HEADER LINE.
ITAB-BUKRS = '1000'.
APPEND ITAB.
ITAB-BUKRS = '1100'.
APPEND ITAB.
SELECT * FROM T001
INTO TABLE T001
FOR ALL ENTRIES IN ITAB
WHERE BUKRS = ITAB-BUKRS.
BREAK-POINT.
regards,
amit m.
07-13-2006 2:28 PM
Hai
if you retrive data from more than one table then use 'FOR ALL ENTRIES' statement instead of using Joins
it will improves the performence
and also it will delete duplcate entries
check the following Code
data : begin of it_mara occurs 0,
matnr like mara-matnr,
meins like mara-meins,
end of it_mara.
data : begin of it_makt occurs 0,
matnr like makt-matnr,
maktx like makt-maktx,
end of it_mara.
clear it_mara.
refresh it_mara.
select matnr
meins
from mara
into table it_mara
where mtart = 'ROH'.
if su-subrc = 0.
sort it_mara by matnr.
endif.
if not it_mara[] is initial.
clear it_makt.
refresh it_makt.
select matnr
maktx
from makt
into table it_makt
for all entries in it_mara
where matnr = it_mara-matnr and
spras = sy-langu.
endif.
Regards
Sreeni
select
07-13-2006 2:45 PM
hi vijay,
check this
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.
Pls. do visit the following link for more info,
https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/2986 [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken]
... FOR ALL ENTRIES IN itab WHERE cond
Effect
Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).
Example
Displaying the occupancy of flights on 28.02.2001:
TYPES: BEGIN OF ftab_type,
carrid TYPE sflight-carrid,
connid TYPE sflight-connid,
END OF ftab_type.
DATA: ftab TYPE STANDARD TABLE OF ftab_type WITH
NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,
free TYPE I,
wa_sflight TYPE sflight.
Suppose FTAB is filled as follows:
CARRID CONNID
--------------
LH 2415
SQ 0026
LH 0400
SELECT * FROM sflight INTO wa_sflight
FOR ALL ENTRIES IN ftab
WHERE CARRID = ftab-carrid AND
CONNID = ftab-connid AND
fldate = '20010228'.
free = wa_sflight-seatsocc - wa_sflight-seatsmax.
WRITE: / wa_sflight-carrid, wa_sflight-connid, free.
ENDSELECT.
The statement has the same effect as:
SELECT DISTINCT * FROM sflight INTO wa_sflight
WHERE ( carrid = 'LH' AND
connid = '2415' AND
fldate = '20010228' ) OR
( carrid = 'SQ' AND
connid = '0026' AND
fldate = '20010228' ) OR
( carrid = 'LH' AND
connid = '0400' AND
fldate = '20010228' ).
free = wa_sflight-seatsocc - wa_sflight-seatsmax.
WRITE: / wa_sflight-carrid, wa_sflight-connid, free.
ENDSELECT.
Notes
You can only use ... FOR ALL ENTRIES IN itab WHERE cond in a SELECT statement.
In the logical condition cond, the symbol itab-f is always a replacement symbol, and should not be confused with the component f of the header line of the internal table itab. The internal table itab does not need to have a header line.
Each component of the internal table that occurs in a replacement symbol in the WHERE condition must have exactly the same type and length as the corresponding component in the database table.
You cannot use replacement symbols in comparisons in LIKE, BETWEEN, or IN expressions.
If you use FOR ALL ENTRIES IN itab, you cannot use ORDER BY f1 ... fn in the ORDER-BY clause.
If you use FOR ALL ENTRIES IN itab, you cannot use a HAVING clause as well.
If u find it useful mark the points
Regards,
Naveen