Dynamic Selections in the Database Program (SAP Library

Dynamic Selections in the Database Program

The statement

SELECTION-SCREEN DYNAMIC SELECTIONS FOR NODE|TABLE <node>.

declares a node <node> of a logical database for dynamic selections in the selection include.

To use the dynamic selections in the SELECT statements of the subroutine PUT_<node>, you must use the data object DYN_SEL. The data object DYN_SEL is automatically generated in the logical database program as follows:

TYPE-POOLS RSDS.

DATA DYN_SEL TYPE RSDS_TYPE.

You do not have to program these lines yourself. The data object DYN_SEL is available in the database program but not in a connected executable program.

The type RSDS_TYPE of the data object is defined in the type group RSDS as follows:

TYPE-POOL RSDS.

* WHERE-clauses ------------------------------

TYPES: RSDS_WHERE_TAB LIKE RSDSWHERE OCCURS 5.

TYPES: BEGIN OF RSDS_WHERE,
         TABLENAME LIKE RSDSTABS-PRIM_TAB,
         WHERE_TAB TYPE RSDS_WHERE_TAB,
       END OF RSDS_WHERE.

TYPES: RSDS_TWHERE TYPE RSDS_WHERE OCCURS 5.

* Expressions Polish notation ---------------

TYPES: RSDS_EXPR_TAB LIKE RSDSEXPR OCCURS 10.

TYPES: BEGIN OF RSDS_EXPR,
         TABLENAME LIKE RSDSTABS-PRIM_TAB,
         EXPR_TAB TYPE RSDS_EXPR_TAB,
       END OF RSDS_EXPR.

TYPES: RSDS_TEXPR TYPE RSDS_EXPR OCCURS 10.

* Selections as RANGES-tables -----------------

TYPES: RSDS_SELOPT_T LIKE RSDSSELOPT OCCURS 10.

TYPES: BEGIN OF RSDS_FRANGE,
         FIELDNAME LIKE RSDSTABS-PRIM_FNAME,
         SELOPT_T TYPE RSDS_SELOPT_T,
       END OF RSDS_FRANGE.

TYPES: RSDS_FRANGE_T TYPE RSDS_FRANGE OCCURS 10.

TYPES: BEGIN OF RSDS_RANGE,
         TABLENAME LIKE RSDSTABS-PRIM_TAB,
         FRANGE_T TYPE RSDS_FRANGE_T,
       END OF RSDS_RANGE.

TYPES: RSDS_TRANGE TYPE RSDS_RANGE OCCURS 10.

* Definition of RSDS_TYPE

TYPES: BEGIN OF RSDS_TYPE,
         CLAUSES TYPE RSDS_TWHERE,
         TEXPR   TYPE RSDS_TEXPR,
         TRANGE  TYPE RSDS_TRANGE,
       END OF RSDS_TYPE.

It is a deep structure with the following components:

CLAUSES

CLAUSES contains the dynamic selections entered by the user (or possibly program-internal selection criteria) as internal tables, which you can use directly in dynamic WHERE clauses.

CLAUSES is an internal table that contains another internal table WHERE_TAB as a component. Each line of the CLAUSES-TABLENAME column contains the name of a node designated for dynamic selections. For each of these database tables, the WHERE_TAB tables contain the selection criteria of the dynamic selections. The WHERE_TAB tables have a format that allows you to use them directly in dynamic WHERE clauses.

To use WHERE_TAB in the logical database, you must program the dynamic WHERE clause for each node <node> designated for dynamic selection in the corresponding subroutine PUT_<node>. For this, the corresponding internal table WHERE_TAB for <node> must be read from the data object DYN_SEL. The following example shows how you can use a local data object in the subroutine for this purpose:

Example

Suppose the database table SCARR is the root node of the logical database ZHK and that SPFLI is its only subordinate node.

The selection Include DBZHKSEL contains the following lines:

SELECT-OPTIONS S_CARRID FOR SCARR-CARRID.
SELECT-OPTIONS S_CONNID FOR SPFLI-CONNID.
SELECTION-SCREEN DYNAMIC SELECTIONS FOR TABLE SCARR.

The subroutine PUT_SCARR of the database program SAPDBZHK uses the dynamic selection as follows:

FORM PUT_SCARR.
  STATICS: DYNAMIC_SELECTIONS TYPE RSDS_WHERE,
           FLAG_READ.
  IF FLAG_READ = SPACE.
    DYNAMIC_SELECTIONS-TABLENAME = 'SCARR'.
    READ TABLE DYN_SEL-CLAUSES
               WITH KEY DYNAMIC_SELECTIONS-TABLENAME
               INTO DYNAMIC_SELECTIONS.
    FLAG_READ = 'X'.
  ENDIF.
  SELECT * FROM SCARR
         WHERE  CARRID IN S_CARRID
         AND   (DYNAMIC_SELECTIONS-WHERE_TAB).
    PUT SCARR.
  ENDSELECT.
ENDFORM.

The line of the internal table DYN_SEL-CLAUSES that contains the value SCARR in column DYN_SEL-CLAUSES-TABLENAME is read into the local structure DYNAMIC_SELECTIONS. The STATICS statements and the FLAG_READ field ensure that table DYN_SEL is only read once during each program execution. The table DYNAMIC_SELECTIONS-WHERE_TAB is used in the dynamic WHERE clause.

Each executable program that uses ZHK as its logical database and contains a NODES or TABLES statement for SCARR or SPFLI now offers dynamic selections for the fields in table SCARR on its selection screen. The dynamic WHERE clause means that the logical database only reads the lines that satisfy the selection conditions on the selection screen and in the dynamic selections.

TEXPR

TEXPR contains the selections of the dynamic selections in an internal format (Polish notation).You can use this format with function modules FREE_SELECTIONS_INIT and FREE_SELECTIONS_DIALOG in order to work with dynamic selections within a program (for more information, see the documentation of these function modules).

TRANGE

TRANGE contains the selections from the dynamic selection as RANGES tables that you can use with the IN operator in a WHERE clause or logical expression.

TRANGE is an internal table that contains another internal table FRANGE_T as a component. Each line in the column TRANGE-TABLENAME contains the name of a node that is designated for dynamic selections. For each of these nodes, the FRANGE_T tables contain the selection criteria of the dynamic selections in the format of RANGES tables. FRANGE_T contains a FIELDNAME column that contains the fields of the node for which the RANGES tables are defined. The other component of FRANGE_T, SELOPT_T, contains the actual RANGES tables.

With TRANGE, you can access the selections of individual database columns directly . Furthermore, it is easier to modify selection criteria stored in the RANGES format than those stored in the WHERE clause format. The following example shows how you can use local data objects of the corresponding subroutine PUT_<node> to work with TRANGE:

Example

Suppose the database table SCARR is the root node of the logical database ZHK and that SPFLI is its single branch.

The selection Include DBZHKSEL contains the following lines:

SELECT-OPTIONS S_CARRID FOR SCARR-CARRID.
SELECT-OPTIONS S_CONNID FOR SPFLI-CONNID.
SELECTION-SCREEN DYNAMIC SELECTIONS FOR TABLE SCARR.

The subroutine PUT_SCARR of the database program SAPDBZHK uses the dynamic selection as follows:

FORM PUT_SCARR.
  STATICS: DYNAMIC_RANGES TYPE RSDS_RANGE,
DYNAMIC_RANGE1 TYPE RSDS_FRANGE,
DYNAMIC_RANGE2 TYPE RSDS_FRANGE,
FLAG_READ.
  IF FLAG_READ = SPACE.
    DYNAMIC_RANGES-TABLENAME = 'SCARR'.
    READ TABLE DYN_SEL-TRANGE
WITH KEY DYNAMIC_RANGES-TABLENAME
INTO DYNAMIC_RANGES.
    DYNAMIC_RANGE1-FIELDNAME = 'CARRNAME'.
    READ TABLE DYNAMIC_RANGES-FRANGE_T
WITH KEY DYNAMIC_RANGE1-FIELDNAME
INTO DYNAMIC_RANGE1.
    DYNAMIC_RANGE2-FIELDNAME = 'CURRCODE'.
    READ TABLE DYNAMIC_RANGES-FRANGE_T
WITH KEY DYNAMIC_RANGE2-FIELDNAME
INTO DYNAMIC_RANGE2.
    FLAG_READ = 'X'.
  ENDIF.
  SELECT * FROM SCARR
WHERE CARRID IN S_CARRID
AND CARRNAME IN DYNAMIC_RANGE1-SELOPT_T
AND CURRCODE IN DYNAMIC_RANGE2-SELOPT_T.
    PUT SCARR.
  ENDSELECT.
ENDFORM.

The line of the internal table DYN_SEL-TRANGE that contains the value 'SCARR' in column DYN_SEL-CLAUSES-TABLENAME is read into the local table DYNAMIC_RANGES. The nested tables DYNAMIC_RANGES-FRANGE_T are read into the local tables DYNAMIC-RANGE1 and DYNAMIC-RANGE2 according to the contents of DYNAMIC_RANGES-FIELDNAME. The STATICS statements and the FLAG_READ field assure that the tables are read only once each time the executable program is executed. After the READ statements, the nested tables SELOPT_T of the local tables contain the RANGES tables for the columns CARRNAME and CURRCODE of the database table SCARR.

The tables SELOPT_T are used in the SELECT statement directly as selection tables. Besides CARRNAME, CURRCODE and the primary key, there are no further columns in the database table SCARR. Therefore, this logical database has the same function as the logical database in the above example using the CLAUSES component.