REPORT ZZBE0001.
************************************************************************
* * * Examples of optimized reading of database tables.
* * *
* * * A couple of forms encapsulating loading and reading of table t001.
* * * Whether the code should be converted to a function module is in
* * * the end a matter of form :-)
************************************************************************
* * * The following is the brainchild of :
* * *   Bent Hansen Systems Consultant Cap Gemini A/S (Denmark)
* * *   and is supplied as is - no warranties / no money return'd.
************************************************************************
TABLES : T001.
DATA : COMPANY_NAME LIKE T001-BUTXT,
       COUNTRY_CODE LIKE T001-LAND1.

* ... calling :
  CLEAR T001.
  T001-BUKRS = '1000'. "or variable or parameter or whatever ....
  PERFORM READ_T001.
  IF SY-SUBRC = 0.
*   ... t001 contains valid data
  ELSE.
*   ... error handling
  ENDIF.
* ...
* ...
* ...
  CLEAR T001.
  T001-BUTXT = COMPANY_NAME.
  T001-LAND1 = COUNTRY_CODE.
  PERFORM ALTREAD_T001.
  IF SY-SUBRC = 0.
*   ... t001 contains valid data
  ELSE.
*   ... error handling
  ENDIF.


*-----------------------------------------------------------------------
* SAMPLE FORM : load db-table to internal table.
* Precondition : db-table declared in global TABLES - statement,
*                prior to call key-fields of the structure 'T001' is
*                assigned values for the desired record.
*-----------------------------------------------------------------------
* APPLICABILITY :
*    The db-table should contain few records that are accessed a large
*    number of times each during the run of your program.
*-----------------------------------------------------------------------
FORM READ_T001.
* * * length of primary key in bytes
CONSTANTS : KEY_LEN TYPE I VALUE 7.
* * * structure copying primary key
STATICS : BEGIN OF T001_KEY,
            MANDT LIKE T001-MANDT,
            BUKRS LIKE T001-BUKRS,
          END OF T001_KEY,
* * * internal table containing all (selected) records.
          I_T001 LIKE T001 OCCURS 10 WITH HEADER LINE,
* * * result of reading with current contents of t001_key.
          T001_SUBRC LIKE SY-SUBRC.

* * * at first call
  IF T001_KEY IS INITIAL.
    T001_KEY-MANDT = SY-MANDT.
* * * physical read : NOTE order by to allow the SQL-engine to optimize
* * *                 retrieval order at time of disc-access.
    SELECT * FROM T001 INTO TABLE I_T001
      ORDER BY PRIMARY KEY.
  ENDIF.

  IF T001_KEY-BUKRS = T001-BUKRS.
* * * if key is the same as last time => the result will be the same
    SY-SUBRC = T001_SUBRC.
  ELSE.
    T001_KEY-BUKRS = T001-BUKRS.
    READ TABLE I_T001 WITH KEY T001_KEY BINARY SEARCH.
    IF SY-SUBRC <> 0.
* * * read table ... binary search will return
*                0 - found,
*                4 - not found in table, or
*                8 - key larger than last key in itab.
* so in order to conform with "standard" results from db-reads :
      T001_SUBRC = SY-SUBRC = 4.
    ENDIF.
  ENDIF.
  IF SY-SUBRC = 0.
* * * transfer data to record in global data-space.
    T001 = I_T001.
  ENDIF.
ENDFORM.

*-----------------------------------------------------------------------
* SAMPLE FORM : variation of the above. Conditions are the same but
* you need to read records by non-key fields.
* In this example the name and country-key of the company is given.
*-----------------------------------------------------------------------
FORM ALTREAD_T001.
* * * length of freely selected search-key in bytes.
CONSTANTS : KEY_LEN TYPE I VALUE 28.
* * * structure copying search key
STATICS : BEGIN OF T001_KEY,
            KEY_BUTXT LIKE T001-BUTXT, " char 25
            KEY_LAND1 LIKE T001-LAND1, " char 3
          END OF T001_KEY.

* * * You may list all the fields to avoid duplicates of search-key,
*     but I never did get that type-writing class.
STATICS : BEGIN OF I_T001 OCCURS 10.
* * * the search-key must be the first part of the internal table.
     INCLUDE STRUCTURE T001_KEY.
     INCLUDE STRUCTURE T001.
STATICS : END OF I_T001.

STATICS : T001_SUBRC LIKE SY-SUBRC.

* * * at first call
  IF T001_KEY IS INITIAL.
* * * physical read :
    SELECT * FROM T001 INTO CORRESPONDING FIELDS OF I_T001.
* * * fill in the search-key fields
      I_T001-KEY_BUTXT = T001-BUTXT.
      I_T001-KEY_LAND1 = T001-LAND1.
      APPEND I_T001.
    ENDSELECT.
* * * in this form you have to manually sort the itab.
    SORT I_T001 BY BUTXT ASCENDING LAND1 ASCENDING.
  ENDIF.

* * * NB : as this is NOT a unique key there may be more than one record
* * *      that satisfies the criteria. In this example we are content
* * *      with any of those.
  IF T001_KEY-KEY_BUTXT = T001-BUTXT AND
     T001_KEY-KEY_LAND1 = T001-LAND1.
    SY-SUBRC = T001_SUBRC.
  ELSE.
    T001_KEY-KEY_BUTXT = T001-BUTXT.
    T001_KEY-KEY_LAND1 = T001-LAND1.
    READ TABLE I_T001 WITH KEY T001_KEY BINARY SEARCH.
    IF SY-SUBRC <> 0.
      T001_SUBRC = SY-SUBRC = 4.
    ENDIF.
  ENDIF.
  IF SY-SUBRC = 0.
    MOVE-CORRESPONDING I_T001 TO T001.
* * * .. or - possibly faster :
    T001 = I_T001+KEY_LEN.
  ENDIF.
ENDFORM.