topic Re: for all entries usage in Application Development and Automation Discussions

for all entries usage

Former Member — Wed, 05 Jul 2006 13:51:12 GMT

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.

Re: for all entries usage

Former Member — Wed, 05 Jul 2006 13:54:08 GMT

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

Re: for all entries usage

Former Member — Wed, 05 Jul 2006 13:54:23 GMT

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

Re: for all entries usage

Former Member — Wed, 05 Jul 2006 13:55:35 GMT

hi nuren,

check if itab is initial or not first.

if not itab[] is initial.

select ...

into table itab1

for all entries in itab.

Re: for all entries usage

Former Member — Wed, 05 Jul 2006 13:57:25 GMT

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

Re: for all entries usage

Former Member — Wed, 05 Jul 2006 13:57:34 GMT

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

Re: for all entries usage

sridhar_k1 — Wed, 05 Jul 2006 14:00:31 GMT

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

Re: for all entries usage

Former Member — Wed, 05 Jul 2006 14:00:39 GMT

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

Re: for all entries usage

Former Member — Wed, 05 Jul 2006 15:11:11 GMT

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