Search Helps in the Database Program
The statement
PARAMETERS p AS SEARCH PATTERN FOR TABLE node.
defines a framework in the selection include for a search help on the selection screen.
The key fields returned by the search help are placed in the internal table ldb_SP, from where they can be used by the database program. The subroutine PUT_ldb_SP is then called instead of PUT_root (where ldb is the name of the logical database).
In the subroutine PUT_ldb_SP, the logical database can use the information from the internal table to make the actual database access more efficient. It triggers the event GET root with the statement PUT root, where root is the name of the root node. It is often useful to call the subroutine PUT_root from PUT_ldb_SP. PUT root can then select the data and trigger the corresponding GET event in the PUT root statement. However, for technical reasons, the PUT root statement should always occur in a subroutine whose name begins PUT_root. The structure of the internal table ldb_SP and other automatically-generated tables is displayed as a comment in the generated source code of the database program. How the tables are used is also documented in the source code.
Let us look at a logical database ZZF with the root node KNA1 that is linked to the search help DEBI.
Let the selection Include DBZHKSEL contain the following lines:
SELECT-OPTIONS SKUNNR FOR KNA1-KUNNR.
PARAMETERS P_SP AS SEARCH PATTERN FOR NODE KNA1.
The source code of the database program now includes more comment lines which indicate that the following tables and fields were created in addition to those listed under :
Internal table ZZF_SP:
This table has the following data type:
DATA: BEGIN OF ZZF_SP OCCURS 1000,
KUNNR
LIKE KNA1-KUNNR,
END OF
ZZF_SP.
The search help selections for the user generate a hit list in the output fields of the search help. The hit list is available to the database program through the table ZZF_SP.
Internal table SP_FIELDS:
If a collective search help is assigned to the logical database, an elementary search help will normally only fill a selection of the output fields of the collective search help. The program can find out from table SP_FIELDS which fields are filled. Its structure is:
DATA: BEGIN OF SP_FIELDS OCCURS 10.
INCLUDE STRUCTURE RSSPFIELDS.
DATA: END OF SP_FIELDS.
The field SP_FIELDS-SUPPLIED is not equal to space if a value was assigned to the field in SP_FIELDS-FIELDNAME by the search help.
Internal table SP_TABLES:
If the search help contains fields from different tables, the program can find out from the table SP_TABLES which ones are covered by the search help. Its structure is:
DATA: BEGIN OF SP_TABLES OCCURS 10.
INCLUDE STRUCTURE RSSPTABS.
DATA: END OF SP_TABLES.
The field SP_TABLES-SUPPLIED is not equal to space if a value was assigned to the table in SP_FIELDS-TABLENAME by the search help.
Field SP_EVENTS:
This is a field with length 200. Each byte in SP_EVENTS stands for a table in the logical database structure (for example, the first character stands for the root node). The contents of the individual positions have the following meaning for the corresponding node:
– The node is addressed in the application program using the GETstatement. The search help has assigned values for the key fields to SP_ldb.
– The node is addressed in the application program using the GETstatement. The search help has not assigned values for the key fields to SP_ldb.
– The node is not addressed in the application program using the GET statement, but the search help has assigned values for the key fields to SP_ldb.
– The node is not addressed in the application program using the GET statement, and the search help has not assigned values for the key fields to SP_ldb.
If the user selects all suppliers in the search help on the selection screen whose sort field begins with ABC, and this applies to customer numbers 17, 125, and 230, the above tables will be filled as follows:
ZZF_SP:
KUNNR |
17 |
125 |
230 |
SP_FIELDS:
FIELDNAME |
SUPPLIED |
KUNNR |
X |
SP_TABLES:
TABLENAME |
SUPPLIED |
KNA1 |
X |
The inactive subroutine PUT_ZZF_SP can, for example, be modified and activated as follows in order to use the entries from the internal table ZZF_SP:
FORM PUT_ZZF_SP.
CASE SP_EVENTS(1).
WHEN 'X' OR 'M'.
PERFORM PUT_KNA1_SP.
WHEN OTHERS.
PERFORM PUT_KNA1.
ENDCASE.
ENDFORM.
FORM PUT_KNA1_SP.
SELECT * FROM KNA1 FOR ALL ENTRIES IN ZZF_SP
WHERE KUNNR = ZZF_SP_KUNNR.
PUT KNA1.
ENDSELECT.
ENDFORM.
The system uses the table GET_EVENTS to check whether the application program contains a GETstatement for KNA1, or whether the search help has assigned values for the key fields. If this is true, the system calls PUT_KNA1_SP. This contains a SELECT loop for KNA1, in which the lines corresponding to the key fields in ZZF_SP are read. The SELECT loop contains the statement PUT KNA1.
Another possibility would be:
FORM PUT_ZZF_SP.
* Füllen der Selektionstabelle aus ZZF_SP
IF SP_EVENTS(1) NE
SPACE.
CLEAR
SKUNNR. REFRESH SKUNNR.
MOVE:
'I' TO SKUNNR-SIGN,
'EQ'
TO SKUNNR-OPTION.
LOOP
AT ZZF_SP.
MOVE
ZZF_SP-KUNNR TO SKUNNR-LOW.
APPEND
SKUNNR.
ENDLOOP.
ENDIF.
* Select data and call GET KUNNR
READ TABLE
GET_EVENTS WITH KEY 'KNA1'.
IF SY-SUBRC = 0
AND GET_EVENTS-KIND NE SPACE.
PERFORM
PUT_KUNNR.
ENDIF.
This deletes the selection table SKUNNR for KNA1 and fills it with the values from ZZF_SP. The program uses the table GET_EVENTS to check whether the application program contains a GET statement for KNA1. If so, it calls the subroutine PUT_KUNNR. Here, the data from KNA1 is read according to the selection table SKUNNR, and the PUT KNA1 statement is executed.
If the database access uses a search help, you should of course, use dynamic selections and field selections.
You can also use search helps to improve performance. You can program different database accesses depending on the tables and fields that are used and filled using the internal tables GET_EVENTS, SP_FIELDS, and SP_TABLES. For example, you can use a view or a join and place the entries in internal tables, which you can process later and then trigger the corresponding GET events.