Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

for all entries

Former Member
0 Kudos

hi

to put two fields from diffrent database tables in where condtion how z it possible in for all entries.

5 REPLIES 5

Former Member
0 Kudos

Hi,

Say for example first you fetch the data into internal table(ITAB) which consists of MATNR and WERKS fields.

then

if not itab[] is initial.

Select amatnr bwerks into table ITAB1 from mara as a join marc as b

on amatnr = bmatnr

for all entries in ITAB

where a~matnr = itab-matnr and

b~werks = itab-werks.

endif.

Regards,

Anji

Former Member
0 Kudos

hi

heres the example:

SELECT matnr mtart

FROM mara

INTO TABLE t_mara

WHERE matnr IN s_matnr.

IF NOT t_mara[] IS INITIAL.

SELECT matnr maktx

FROM makt

INTO TABLE t_makt

FOR ALL ENTRIES IN t_mara

WHERE matnr EQ t_mara-matnr.

ENDIF.

regards,

madhu

Former Member
0 Kudos

Hi Rasheed,

please analyse one small example :

IF NOT i_out[] IS INITIAL.

SELECT * FROM mard INTO TABLE i_mard

FOR ALL ENTRIES IN i_out

WHERE matnr EQ i_out-matnr AND

werks IN s_iwerks.

ENDIF.

Regards,

George

  • plz reward points to helpful answers

Former Member
0 Kudos

hi rasheed,

i think this code will help you for your query.

TABLES: ZVEMP,ZVIJDEP.

DATA: BEGIN OF ITAB OCCURS 0,

EMPPID LIKE ZVEMP-EMPPID,

EMPNAM LIKE ZVEMP-EMPNAM,

EMPAGE LIKE ZVEMP-EMPAGE,

EMPDESIG LIKE ZVEMP-EMPDESIG,

END OF ITAB.

DATA: BEGIN OF JTAB OCCURS 0,

EMPPID LIKE ZVIJDEP-EMPPID,

EMPNAM LIKE ZVIJDEP-EMPNAM,

EMPDEP LIKE ZVIJDEP-EMPDEP,

EMPDESIG LIKE ZVIJDEP-EMPDESIG,

PHONENO LIKE ZVIJDEP-PHONENO,

END OF JTAB.

DATA: BEGIN OF KTAB OCCURS 0,

EMPPID LIKE ZVEMP-EMPPID,

EMPNAM LIKE ZVEMP-EMPNAM,

EMPAGE LIKE ZVEMP-EMPAGE,

EMPDESIG LIKE ZVEMP-EMPDESIG,

EMPDEP LIKE ZVIJDEP-EMPDEP,

PHONENO LIKE ZVIJDEP-PHONENO,

END OF KTAB.

SELECT-OPTIONS: EMPPID FOR ZVEMP-EMPPID.

START-OF-SELECTION.

SELECT EMPPID EMPNAM EMPAGE EMPDESIG FROM ZVEMP APPENDING CORRESPONDING

FIELDS OF TABLE ITAB

WHERE

EMPPID IN EMPPID.

*WHERE EMPPID = JTAB-EMPPID.

*APPEND ITAB.

LOOP AT ITAB.

SELECT EMPPID EMPDEP PHONENO FROM ZVIJDEP APPENDING CORRESPONDING FIELDS

OF

TABLE JTAB

*FOR

*ALL

*ENTRIES IN ITAB

WHERE EMPPID = ITAB-EMPPID.

*APPEND JTAB.

*ENDSELECT.

ENDLOOP.

*APPEND JTAB.

  • ENDSELECT.

*

  • ENDSELECT.

*

*

*

*

WRITE:/2(15) 'EMP ID',20(15) 'EMP NAME',40(15) 'EMP AGE',60(15) 'EMP

DESIGNATION', 80(15) 'EMP DEP', 100(15) 'EMP PHONE NO'.

WRITE:/ SY-ULINE.

LOOP AT ITAB.

MOVE-CORRESPONDING ITAB TO KTAB. APPEND KTAB.

ENDLOOP.

LOOP AT JTAB.

LOOP AT KTAB WHERE EMPPID = JTAB-EMPPID.

MOVE-CORRESPONDING JTAB TO KTAB. MODIFY KTAB INDEX SY-TABIX.

ENDLOOP.

ENDLOOP.

LOOP AT KTAB.

WRITE:/ KTAB-EMPPID UNDER 'EMP ID',KTAB-EMPNAM UNDER 'EMP NAME',

KTAB-EMPAGE UNDER 'EMP AGE',KTAB-EMPDESIG UNDER 'EMP DESIGNATION',

KTAB-EMPDEP UNDER 'EMP DEP',KTAB-PHONENO UNDER 'EMP PHONE NO'.

ENDLOOP.

regards

vijay

rewards if answer is helpfull.

Former Member
0 Kudos

hi

good

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.

go through this link

http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912

thanks

mrutyun^