on 04-28-2008 1:15 PM
HI All,
1. What is ment by for all entries in SAP ABAP.
2. How to write code for for all entries.
3. What is main pre-requisite for for all entries.
Bye,
Srinivas
Hi Srinu,
For all entries is used to replace complex and time taking Joins.
2.
select vbeln from vbak
into table i_vbeln
where vblen in s_vbeln.
if sy-subrc = 0.
select vbeln posnr
from vbap
into table i_vbap
for all entries in i_vbak
where vbeln = i_vbap-vbeln.
endif.
3. pre requisites
make sure the driver table(in the ex: i_vbak), is not initial
sort the internal table before deleting duplicate entries.
delete the duplicate entries before using for all entries.
in the ex. above, vbeln is unique and hence no duplicate entries.
regards,
madhu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
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:
1.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.
2.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.
3.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.
Wat exactly FOR ALL ENTRIES means:
You can only use FOR ALL ENTRIES IN ...WHERE ...in a SELECT statement.
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).
for example:
*SELECT * FROM sflight INTO wa_sflight*
FOR ALL ENTRIES IN ftab
WHERE CARRID = ftab-carrid AND
CONNID = ftab-connid AND
fldate = '20010228'.
this condition, return all entries of the sflight
thnx
Sravani
Plz reward points if helpful....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
here is a select query where i have used for all entries...just check...
PARAMETER : P_NO TYPE EKKO-EBELN OBLIGATORY.
SELECT EBELN LIFNR
FROM EKKO
INTO TABLE IT_EKKO
WHERE EBELN = P_NO.
SELECT LIFNR NAME1 LAND1 ORT01 REGIO
FROM LFA1
INTO TABLE IT_LFA1 FOR ALL ENTRIES IN IT_EKKO
WHERE LIFNR = IT_EKKO-LIFNR.
SELECT EBELP EMATN AEDAT NETWR MTART
FROM EKPO
INTO TABLE IT_EKPO FOR ALL ENTRIES IN IT_EKKO
WHERE EBELN = IT_EKKO-EBELN .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
For example -
DATA: TAB_SPFLI TYPE TABLE OF SPFLI,
TAB_SFLIGHT TYPE SORTED TABLE OF SFLIGHT
WITH UNIQUE KEY TABLE LINE,
WA LIKE LINE OF TAB_SFLIGHT.
SELECT CARRID CONNID
INTO CORRESPONDING FIELDS OF TABLE TAB_SPFLI
FROM SPFLI
WHERE CITYFROM = 'NEW YORK'.
SELECT CARRID CONNID FLDATE
INTO CORRESPONDING FIELDS OF TABLE TAB_SFLIGHT
FROM SFLIGHT
FOR ALL ENTRIES IN TAB_SPFLI
WHERE CARRID = TAB_SPFLI-CARRID AND
CONNID = TAB_SPFLI-CONNID.
LOOP AT TAB_SFLIGHT INTO WA.
AT NEW CONNID.
WRITE: / WA-CARRID, WA-CONNID.
ENDAT.
WRITE: / WA-FLDATE.
ENDLOOP.
When using FOR ALL ENTRIES you need to make sure that table which you are using should not be empty else it will SELECT all the data from that table.
I hope it helps.
Thanks,
Vibha
Please mark all the useful answers
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Its a replacement of innerjoin.
prerequesite:for internal table on which you are using for all entries that table is should not be empty.like here it_mkpf .
SELECT
budat " migo date
mblnr " migo number
xblnr " reference migo no
tcode2 " tcode
mjahr " year
FROM mkpf
INTO TABLE it_mkpf
WHERE mblnr LIKE '5%'
AND ( mjahr = '2008'
OR mjahr = '2007' )
AND budat IN so_budat
AND tcode2 = 'MIGO_GR' .
"DELETE it_mkpf[] WHERE budat not IN so_budat. " 25.03.2008
IF it_mkpf[] IS INITIAL.
MESSAGE i002(zamit).
LEAVE LIST-PROCESSING.
ENDIF.
SORT it_mkpf[] BY mblnr.
SELECT werks " plant
mblnr " migo no
matnr " material no
menge " migo qty
erfmg " migo FA
lifnr " Supplier
dmbtr " value
bpmng " PO qty
ebeln " po no
ebelp " po item no
elikz " close or not
mjahr " year
FROM mseg
INTO TABLE it_mseg
FOR ALL ENTRIES IN it_mkpf
WHERE mblnr = it_mkpf-mblnr
AND mjahr = it_mkpf-mjahr
"AND mblnr LIKE '5%'
AND werks IN p_werks.
Hope this would help.
please reward points if clear.
Amit.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
For all Entries can be used instead of JOIN between 2 tables
Prerequisite: The table used in for all entries in not empty and has some common fields with the table you want to fetch data into.
Example:
SELECT * FROM QALS INTO CORRESPONDING FIELDS OF TABLE I_DATA WHERE PRUEFLOS IN S_PRUEF AND
HERKUNFT EQ '89' AND ENSTEHDAT IN S_CDATE AND
MATNR IN S_MATNR .
IF NOT I_DATA IS INITIAL.
SELECT MATNR MAKTX FROM MAKT INTO TABLE I_MAKT FOR ALL ENTRIES IN I_DATA
WHERE MATNR = I_DATA-MATNR .
ENDIF
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
8 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.