07-05-2006 2:51 PM
I have an internal table itab with fields vbeln, posnr and erdat fields. Here all the erdat field values are blank and yet to be gotten from other table vbap.
Now I am planning to use 'For all entries' here. I mean 'For all entries' in itab, I want to go to go to vbap and get the corresponding erdat. Can you tell me how to use 'For all entries' here?
Thanks in advance.
07-05-2006 2:54 PM
Hi Nuren,
Always have a check of internal table content before applying FOR ALL ENTRIES option.
DATA:
BEGIN OF ITAB OCCURS 0,
VBELN TYPE VBAK-VBELN,
POSNR TYPE VBAK-POSNR,
ERDAT TYPE VBAK-ERDAT,
END OF ITAB.
DATA:
BEGIN OF ITAB_VBAP OCCURS 0,
VBELN TYPE VBAP-VBELN,
ERDAT TYPE VBAP-ERDAT,
END OF ITAB_VBAP.
IF ITAB[] IS NOT INITIAL.
SELECT VBELN ERDAT
FROM VBAP
INTO TABLE ITAB_VBAP
FOR ALL ENTRIES IN ITAB
WHERE VBELN = ITAB-VBELN.
ENDIF.
Thanks,
Vinay
Message was edited by: Vinaykumar G
07-05-2006 2:54 PM
Hi,
check not itab[] is initial.
select erdat
into table i_erdat
for all entries in itab
where vbeln = itab-vbeln and
posnr = itab-posnr.
Regards.
Mireia
07-05-2006 2:55 PM
hi nuren,
check if itab is initial or not first.
if not itab[] is initial.
select ...
into table itab1
for all entries in itab.
07-05-2006 2:57 PM
select erdat
from vbap
into corresponding fields of table itab
for all entries in itab
where vbeln eq itab-vbeln and
posnr eq itab-posnr.
This should solve your problem
07-05-2006 2:57 PM
You cannot use FOR ALL ENTRIES to meet your requirement. If you use FOR ALL entries and ERDAT in the where clause your select will return rows where ERDAT is space.
If you want to select rows independent of the value in ERDAT then only mention VBELN and POSNR in the where clause. Do not mention ERDAT in the where clause.
-Kiran
07-05-2006 3:00 PM
If ur appserver is 6.10 and higher you can use the same itab for all entries and into in the select statement.
if not itab[] is intial.
select erdat from vbap into corresponding fields of table itab
for all entries in itab
where vbeln eq itab-vbeln
and posnr = itab-posnr.
endif.
Regards
Sridhar
07-05-2006 3:00 PM
Hai Nerun
Go through the following Tips for improving Performence
For all entries
The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.
The plus
Large amount of data
Mixing processing and reading of data
Fast internal reprocessing of data
Fast
The Minus
Difficult to program/understand
Memory could be critical (use FREE or PACKAGE size)
Some steps that might make FOR ALL ENTRIES more efficient:
Removing duplicates from the driver table
Sorting the driver table
If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:
FOR ALL ENTRIES IN i_tab
WHERE mykey >= i_tab-low and
mykey <= i_tab-high.
For example
********************************************************************
T A B L E S U S E D *
********************************************************************
TABLES: EKKO,EKPO,KONV,LFA1,T001.
********************************************************************
S E L E C T I O N S C R E E N *
********************************************************************
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-000.
SELECT-OPTIONS:
S_LIFNR FOR EKKO-LIFNR,
S_BUKRS FOR EKKO-BUKRS.
SELECTION-SCREEN END OF BLOCK B1.
********************************************************************
D A T A D E C L A R A T I O N S *
********************************************************************
DATA: BEGIN OF IT_EKKO OCCURS 0,
BUKRS LIKE EKKO-BUKRS,
LIFNR LIKE EKKO-LIFNR,
EBELN LIKE EKKO-EBELN,
KNUMV LIKE EKKO-KNUMV,
END OF IT_EKKO.
DATA: BEGIN OF IT_EKPO OCCURS 0,
EBELN LIKE EKPO-EBELN,
EBELP LIKE EKPO-EBELP,
MATNR LIKE EKPO-MATNR,
INFNR LIKE EKPO-INFNR,
MENGE LIKE EKPO-MENGE,
MEINS LIKE EKPO-MEINS,
NETPR LIKE EKPO-NETPR,
END OF IT_EKPO.
SELECT BUKRS LIFNR EBELN KNUMV
FROM EKKO
INTO TABLE IT_EKKO
WHERE LIFNR IN S_LIFNR
AND BUKRS IN S_BUKRS.
IF NOT IT_EKKO[] IS INITIAL.
SELECT EBELN EBELP MATNR INFNR MENGE MEINS NETPR
FROM EKPO
INTO TABLE IT_EKPO
FOR ALL ENTRIES IN IT_EKKO
WHERE EBELN = IT_EKKO-EBELN.
ENDIF.
Thanks & regards
Sreenivasulu P
07-05-2006 4:11 PM
Hi Nuren,
I am assuming that your having SALES header data (DB Table: VBAK) and then fetching SALES item data(DB Table : VBAP).
Note: POSNR field not present in VBAK table(atleast mine)
Consider this code
REPORT z_test.
TYPES :BEGIN OF str_vbak,
vbeln TYPE vbak-vbeln, "Key fields in VBAK (Sales Document: Header Data)
erdat TYPE vbak-erdat,
END OF str_vbak.
TYPES :BEGIN OF str_vbap,
vbeln TYPE vbap-vbeln, "Key fields in VBAP (Sales Document: Item Data)
posnr TYPE vbap-posnr, "Key fields in VBAP
erdat TYPE vbap-erdat,
END OF str_vbap.
DATA : it_vbak TYPE STANDARD TABLE OF str_vbak ,
it_vbap TYPE STANDARD TABLE OF str_vbap .
START-OF-SELECTION.
SELECT vbeln
erdat
FROM vbak
INTO TABLE it_vbak.
IF it_vbak[] IS NOT INITIAL. <b>"Must be checked else all entries will get selected</b>
SELECT vbeln
posnr
erdat
INTO TABLE it_vbap
FROM vbap
FOR ALL ENTRIES IN it_vbak
WHERE vbeln = it_vbak-vbeln AND <b>"Also try to include key fields if possible</b>
erdat = it_vbak-erdat.
ENDIF.
I had written a program where I have tested the performance of FOR ALL ENTRIES and JOINS. In this I fetching Material Header Data(MARA) and Material Plant Data(MARC). Probably you may find it useful.
REPORT ztest_fralljoin.
TABLES : mara, marc.
TYPES : BEGIN OF tp_mara,
matnr TYPE mara-matnr,
mtart TYPE mara-mtart,
END OF tp_mara.
TYPES : BEGIN OF tp_marc,
matnr TYPE mara-matnr,
werks TYPE marc-werks,
pstat TYPE marc-pstat,
END OF tp_marc.
TYPES : BEGIN OF tp_final,
matnr TYPE mara-matnr,
mtart TYPE mara-mtart,
werks TYPE marc-werks,
pstat TYPE marc-pstat,
END OF tp_final.
DATA: start_time TYPE sy-uzeit,
end_time TYPE sy-uzeit,
difference TYPE sy-uzeit.
DATA : t_mara TYPE STANDARD TABLE OF tp_mara,
t_marc TYPE STANDARD TABLE OF tp_marc,
t_frall TYPE STANDARD TABLE OF tp_final,
t_injoin TYPE STANDARD TABLE OF tp_final.
DATA : wa_mara TYPE tp_mara,
wa_marc TYPE tp_marc,
wa_frall TYPE tp_final.
DATA : w_flreads TYPE sy-tabix,
w_inreads TYPE sy-tabix.
SELECT-OPTIONS: s_matnr FOR mara-matnr.
START-OF-SELECTION.
REFRESH : t_mara, t_marc, t_injoin, t_frall.
PERFORM join_select.
PERFORM forall_select.
*&--------------------------------------------------------------------*
*& Form join_select
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM join_select.
GET TIME FIELD start_time.
SELECT a~matnr a~mtart b~werks b~pstat
INTO TABLE t_injoin
FROM mara AS a INNER JOIN marc AS b
ON a~matnr = b~matnr
WHERE a~matnr IN s_matnr.
w_inreads = sy-dbcnt.
GET TIME FIELD end_time.
difference = end_time - start_time.
WRITE: /001 'Time for getting consolidating data using INNER JOIN:', difference,
/005 'Number of entries:', w_inreads.
ENDFORM. "join_select
*&--------------------------------------------------------------------*
*& Form forall_select
*&--------------------------------------------------------------------*
* text
*---------------------------------------------------------------------*
FORM forall_select.
GET TIME FIELD start_time.
SELECT matnr mtart INTO TABLE t_mara FROM mara
WHERE matnr IN s_matnr.
IF NOT t_mara[] IS INITIAL.
SELECT matnr werks pstat INTO TABLE t_marc
FROM marc
FOR ALL ENTRIES IN t_mara
WHERE matnr = t_mara-matnr.
ENDIF.
SORT t_mara BY matnr.
SORT t_marc BY matnr.
CLEAR : wa_marc, wa_mara, wa_frall, w_flreads.
LOOP AT t_marc INTO wa_marc.
READ TABLE t_mara INTO wa_mara WITH KEY matnr = wa_marc-matnr.
wa_frall-matnr = wa_mara-matnr.
wa_frall-mtart = wa_mara-mtart.
wa_frall-werks = wa_marc-werks.
wa_frall-pstat = wa_marc-pstat.
APPEND wa_frall TO t_frall.
w_flreads = w_flreads + 1.
ENDLOOP.
GET TIME FIELD end_time.
difference = end_time - start_time.
WRITE: /001 'Time for getting consolidating data using FOR ALL ENTRIES:', difference,
/005 'Number of entries:', w_flreads.
ENDFORM. "join_select
Regards,
Arun Sambargi.
Message was edited by: Arun Sambargi