01-19-2006 11:20 AM
01-19-2006 11:22 AM
Hi Franklin!
Here is the docu:
FOR ALL ENTRIES WHERE
Syntax
... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...
Effect
If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.
The internal table itab must have a structured line type and the component comp must be compatible with the column col.
The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if FOR ALL ENTRIES is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.
The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set.
Notes
In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO.
The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement.
Example
Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.
PARAMETERS p_city TYPE spfli-cityfrom.
TYPES: BEGIN OF entry_tab_type,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
END OF entry_tab_type.
DATA: entry_tab TYPE TABLE OF entry_tab_type,
sflight_tab TYPE SORTED TABLE OF sflight
WITH UNIQUE KEY carrid connid fldate.
SELECT carrid connid
FROM spfli
INTO CORRESPONDING FIELDS OF TABLE entry_tab
WHERE cityfrom = p_city.
SELECT carrid connid fldate
FROM sflight
INTO CORRESPONDING FIELDS OF TABLE sflight_tab
FOR ALL ENTRIES IN entry_tab
WHERE carrid = entry_tab-carrid AND
connid = entry_tab-connid.
Regards,
Christian
01-19-2006 11:23 AM
Hi Franklin,
Here is an Example:
Example
Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.
PARAMETERS p_city TYPE spfli-cityfrom.
TYPES: BEGIN OF entry_tab_type,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
END OF entry_tab_type.
DATA: entry_tab TYPE TABLE OF entry_tab_type,
sflight_tab TYPE SORTED TABLE OF sflight
WITH UNIQUE KEY carrid connid fldate.
SELECT carrid connid
FROM spfli
INTO CORRESPONDING FIELDS OF TABLE entry_tab
WHERE cityfrom = p_city.
SELECT carrid connid fldate
FROM sflight
INTO CORRESPONDING FIELDS OF TABLE sflight_tab
FOR ALL ENTRIES IN entry_tab
WHERE carrid = entry_tab-carrid AND
connid = entry_tab-connid.
REgards,
Ravi
01-19-2006 11:28 AM
Example :
SELECT VBELN POSNR INTO TABLE IT_VBAP
FROM VBAP
WHERE VBELN = P_ORDER.
SELECT POSNR BANFN INTO TABLE IT_VBEP
FROM VBEP
FOR ALL ENTRIES IN IT_VBAP
WHERE VBELN = IT_VBAP-POSNR
AND POSNR = IT_VBAP-POSNR.
01-19-2006 11:30 AM
Example :
SELECT VBELN POSNR INTO TABLE IT_VBAP
FROM VBAP
WHERE VBELN = P_ORDER.
If not IT_VBAP[] initial.
SELECT POSNR BANFN INTO TABLE IT_VBEP
FROM VBEP
FOR ALL ENTRIES IN IT_VBAP
WHERE VBELN = IT_VBAP-POSNR
AND POSNR = IT_VBAP-POSNR.
endif.
01-19-2006 11:26 AM
Hi,
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.
Example:
DATA: BEGIN OF LINE,
CARRID TYPE SPFLI-CARRID,
CONNID TYPE SPFLI-CONNID,
CITYFROM TYPE SPFLI-CITYFROM,
CITYTO TYPE SPFLI-CITYTO,
END OF LINE,
ITAB LIKE TABLE OF LINE.
LINE-CITYFROM = 'FRANKFURT'.
LINE-CITYTO = 'BERLIN'.
APPEND LINE TO ITAB.
LINE-CITYFROM = 'NEW YORK'.
LINE-CITYTO = 'SAN FRANCISCO'.
APPEND LINE TO ITAB.
SELECT CARRID CONNID CITYFROM CITYTO
INTO CORRESPONDING FIELDS OF LINE
FROM SPFLI
FOR ALL ENTRIES IN ITAB
WHERE CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.
WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.
ENDSELECT.
01-19-2006 11:27 AM
Hi Franklin,
Have a look at demo programs
DEMO_SELECT_FOR_ALL_ENTRIES_1
DEMO_SELECT_FOR_ALL_ENTRIES_2.
Thanks
Lakshman
01-19-2006 11:28 AM
Hi,
select vbeln
posnr
from vbap
into table it_vbap
where vbeln in s_vbeln.
if sy-subrc = 0.
select vbeln
posnr
...
from lips
into table it_lips
for all entries in it_vbap
where vgbel = it_vbap-vbeln
and vgpos = it_vbap-posnr.
endif.
regards
vijay
01-19-2006 11:30 AM
Hi Franklin,
1. Why for all entries is required?
U must have used JOINS
where two tables are joined
These tables must be DATABASE tables.
2. What if u have some data
in internal table which
u require to LINK
with some dbtable for fetching data !
In such cases, FOR ALL ENTRIES
is used
LINKING DBTABLE <----
> INTERNAL TABLE
in a sql query !
3. For syntax (very simple to use)
just see F1 help on FOR ALL ENTRIES.
regards,
amit m.
01-19-2006 11:35 AM
Hi franklin galz,
Effect:
When U use for all entries it takes all the data according to ur criteria.
Example:
SELECT avkorg avtweg aspart afkdat aerdat avbeln a~kunag
bname1 arfbsk
FROM vbrk AS a
JOIN kna1 AS b ON akunag = bkunnr
INTO CORRESPONDING FIELDS OF TABLE it1
WHERE a~fkdat IN p_fkdat
AND a~vbeln IN p_vbeln
AND a~vkorg IN p_vkorg
AND a~vtweg IN p_vtweg
AND a~spart IN p_spart.
SELECT vbeln vgbel
INTO CORRESPONDING FIELDS OF TABLE it2
FROM vbrp
FOR ALL ENTRIES IN it1
WHERE vbeln = it1-vbeln.
Here IT1 is filled with all VBELN Entries.
Now For all IT1-VBELN entry instead of using select statement in loop, u can use for all entries statement.
So it2 is filled from VBRP according to it1-vbeln entries.
Advantage : Number of database trip is reduced. It will increase the performance of ur report by reducing execution time.
Regards,
Digesh Panchal