03-12-2007 12:20 PM
hi
to put two fields from diffrent database tables in where condtion how z it possible in for all entries.
03-12-2007 12:25 PM
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
03-12-2007 12:26 PM
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
03-12-2007 12:28 PM
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
03-12-2007 12:29 PM
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.
03-12-2007 12:35 PM
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^