use of for all entries in abap

[Former Member]

Please tell me how to use "for all entries" in linking two

tables for retrieve informations by using corresponding fields in both the tables.plz send the codings.

[Former Member]

hi,

say if u want to display material and its text. u can use this concept.

~from the master table i'm selecting the materials based on the input s_matnr.

~then for the materials selected above , i have to get their corresponding description.

ie:

select matnr from mara into table i_matnr

where matnr in s_matnr.

if sy-subrc = 0.

select matnr maktx

from makt into table i_makt

for all entries in i_matnr

where matnr = i_mara-matnr.

endif.

This is similar to INNER JOIN concept. get the common data from 2 tables based on the input. Sometimes Join will give poor performance , n there u can use FOR ALL ENTRIES.

[Former Member]

Hi,

sample code:

*Select Order number and Last action date for the Disconnection document number

SELECT discno discact actdate ordernum

FROM ediscact

INTO TABLE lt_disc_act

FOR ALL ENTRIES IN lt_status

WHERE discno = lt_status-discno

AND ordernum = space.

This code will select data for all values already in internal table lt_status where discno = lt_status-discno.

This increases efficiency of code.

Reward points if helpful answer.

[Former Member]

Hi,

Please see the attached code.

SELECT belnr

gjahr

bukrs

awkey

INTO TABLE i_bkpf

FROM bkpf

FOR ALL ENTRIES IN i_bsak

WHERE belnr = i_bsak-belnr

AND gjahr = i_bsak-gjahr

AND bukrs = i_bsak-bukrs.

[Former Member]

Hi,

You can see the sample code.

SELECT bukrs

belnr

gjahr

blart

bldat

budat

monat

xblnr

FROM bkpf

INTO TABLE it_header

WHERE bukrs IN ccode AND

belnr IN docno AND

blart IN doctype AND

gjahr IN year AND

budat IN period.

IF it_header IS NOT INITIAL.

SELECT bukrs

belnr

buzei

zuonr

budat

bschl

wrbtr

kostl

gjahr

shkzg

hkont

waers

FROM bsis

INTO TABLE it_item

FOR ALL ENTRIES IN it_header

WHERE bukrs = it_header-bukrs AND

gjahr = it_header-gjahr AND

belnr = it_header-belnr.

here, checking the condition IF it_header IS NOT INITIAL. is mandatory otherwise if it is initial

it will lead to short dump.

Edited by: Srikanth Kadiyala on Jun 2, 2008 12:48 PM

[Former Member]

REPORT YVCHIERSEQ .

TYPE-POOLS:SLIS.

DATA:T_VBAK TYPE STANDARD TABLE OF VBAK INITIAL SIZE 0,

T_VBAP TYPE STANDARD TABLE OF VBAP INITIAL SIZE 0,

W_VBAK TYPE VBAK,

W_VBAP TYPE VBAP,

G_VBELN TYPE VBAK-VBELN,

W_FIELDCAT TYPE SLIS_FIELDCAT_ALV,

T_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV,

W_KEYINFO TYPE SLIS_KEYINFO_ALV.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME.

SELECT-OPTIONS:S_VBELN FOR G_VBELN.

SELECTION-SCREEN END OF BLOCK B1.

INITIALIZATION.

CLEAR:W_VBAK,

W_VBAP.

REFRESH:T_VBAK,

T_VBAP.

START-OF-SELECTION.

PERFORM SUB_GET_VBAK.

PERFORM SUB_GET_VBAP.

PERFORM SUB_DISP_OUTPUT.

FORM SUB_GET_VBAK.

SELECT * FROM VBAK INTO TABLE T_VBAK WHERE VBELN IN S_VBELN.

ENDFORM.

FORM SUB_GET_VBAP.

SELECT * FROM VBAP INTO TABLE T_VBAP FOR ALL ENTRIES IN T_VBAK WHERE

VBELN = T_VBAK-VBELN.

ENDFORM.

FORM SUB_DISP_OUTPUT.

W_FIELDCAT-COL_POS = 1.

W_FIELDCAT-FIELDNAME = 'VBELN'.

W_FIELDCAT-SELTEXT_M = 'SALESORDER'.

W_FIELDCAT-TABNAME = 'VBAK'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = 2.

W_FIELDCAT-FIELDNAME = 'ERNAM'.

W_FIELDCAT-SELTEXT_M = 'NAME'.

W_FIELDCAT-TABNAME = 'VBAK'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = 3.

W_FIELDCAT-FIELDNAME = 'VBTYP'.

W_FIELDCAT-SELTEXT_M = 'SD CATEGORY'.

W_FIELDCAT-TABNAME = 'VBAK'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = 4.

W_FIELDCAT-FIELDNAME = 'KUNNR'.

W_FIELDCAT-SELTEXT_M = 'SOLD TO PARTY'.

W_FIELDCAT-TABNAME = 'VBAK'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = 5.

W_FIELDCAT-FIELDNAME = 'NETWR'.

W_FIELDCAT-SELTEXT_M = 'NET VALUE'.

W_FIELDCAT-TABNAME = 'VBAK'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

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

W_FIELDCAT-COL_POS = 1.

W_FIELDCAT-FIELDNAME = 'POSNR'.

W_FIELDCAT-SELTEXT_M = 'SALES ITEM'.

W_FIELDCAT-TABNAME = 'VBAP'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = 2.

W_FIELDCAT-FIELDNAME = 'MATNR'.

W_FIELDCAT-SELTEXT_M = 'MATERIAL NO'.

W_FIELDCAT-TABNAME = 'VBAP'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = 3.

W_FIELDCAT-FIELDNAME = 'MATKL'.

W_FIELDCAT-SELTEXT_M = 'MAT GROUP'.

W_FIELDCAT-TABNAME = 'VBAP'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = 4.

W_FIELDCAT-FIELDNAME = 'NETWR'.

W_FIELDCAT-SELTEXT_M = 'NET VALUE'.

W_FIELDCAT-TABNAME = 'VBAP'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

W_KEYINFO-HEADER01 = 'VBELN'.

W_KEYINFO-ITEM01 = 'VBELN'.

CALL FUNCTION 'REUSE_ALV_HIERSEQ_LIST_DISPLAY'

EXPORTING

• I_INTERFACE_CHECK = ' '

I_CALLBACK_PROGRAM = SY-CPROG

• I_CALLBACK_PF_STATUS_SET = ' '

• I_CALLBACK_USER_COMMAND = ' '

• IS_LAYOUT =

IT_FIELDCAT = T_FIELDCAT

• IT_EXCLUDING =

• IT_SPECIAL_GROUPS =

• IT_SORT =

• IT_FILTER =

• IS_SEL_HIDE =

• I_SCREEN_START_COLUMN = 0

• I_SCREEN_START_LINE = 0

• I_SCREEN_END_COLUMN = 0

• I_SCREEN_END_LINE = 0

• I_DEFAULT = 'X'

• I_SAVE = ' '

• IS_VARIANT =

• IT_EVENTS =

• IT_EVENT_EXIT =

i_tabname_header = 'VBAK'

i_tabname_item = 'VBAP'

• I_STRUCTURE_NAME_HEADER =

• I_STRUCTURE_NAME_ITEM =

is_keyinfo = W_KEYINFO

• IS_PRINT =

• IS_REPREP_ID =

• I_BYPASSING_BUFFER =

• I_BUFFER_ACTIVE =

• IMPORTING

• E_EXIT_CAUSED_BY_CALLER =

• ES_EXIT_CAUSED_BY_USER =

tables

t_outtab_header = T_VBAK

t_outtab_item = T_VBAP

• EXCEPTIONS

• PROGRAM_ERROR = 1

• OTHERS = 2

.

IF sy-subrc <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM.

[Former Member]

hi

'FOR ALL ENTRIES' is used to improve ur system performance

its good practice to write code which use less db access.

'FOR ALL ENTRIES' is used with internal tables. when u need data from three tables . u can do it in two ways..

1) fetch data for single itab and use it with 'FOR ALL ENTRIES'

and populate the other tables.

or

2) use inner join for taking data from two table and place it in itab1. now use 'FOR ALL ENTRIES' with another selct statmt and use the itab1 and populate the 3rd itab

sample code for the same:

select vbeln

posnr

matwa

matkl

from vbap into table vbap_it where vbeln in salesdoc.

select vbeln

erdat

ernam

from vbak into table vbap_it1 for all entries in vbap_it

where vbeln = vbap_it-vbeln.

use inner in ur prog. and populate itab1

then use for all entries in next select query

reward points if helpful

regards

mano

[Former Member]

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.

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.

Reward all helpfull answers.

Regards.

Jay