Show TOC

Background documentationDB Lookup Expression

 

The DB lookup expression performs a database lookup from within BRFplus. It is used to access the records stored in a data dictionary (DDIC) table or to retrieve information about a table.

Features

Modes of Operation

  • Data Retrieval Mode

    In this mode, the data is retrieved from a database table. The expression performs a simple selection operation in the specified table. The selection is defined by the following settings:

    • Selection Flag

      Specifies whether a multiple select (All Entries) or a single select (Single Entry) should be performed.

      Note Note

      If you choose All Entries for the selection, the result data object assigned to the DB lookup expression must be of table type.

      End of the note.

    • Table Name

      Specifies the database table from which data is read. You can access any table available in the data dictionary of the backend system. It is not necessary to bind the database table to a BRFplus table data object.

    • Field Conditions

      Specifies the conditions applied to the different table fields for refining the search result. Based on the field conditions, the WHERE clause of the performed selection is built.

    The value that is actually selected from the table depends on the result data object type:

    • If the result data object is an element with a name corresponding to one of the table fields, the expression only selects that one value.

    • If the result data object is a structure, then the database fields corresponding to the structure fields are read.

    • If the result data object is a table, then the source table fields corresponding to the result table fields are read. As opposed to using an elementary or structure data object, multiple entries can be read.

    Note Note

    The system tries to map the context data object fields to the fields of the result data object, based on the field type and name. However, there is no guarantee that the mappings are complete or adequate for the given use case. You should therefore always check the field mappings and adjust them where necessary. For more information, see the Field Mapping section below.

    End of the note.

  • Aggregation Mode

    In this mode, the result of an aggregation function applied to a database table is returned. The following aggregation functions are supported:

    • Maximum

    • Minimum

    • Sum

    • Average

    • Count

    The aggregation functions can be applied on distinct fields or on entire table records (all fields in a row). The following example illustrates the effects of changing this setting:

    Example Example

    You create a DB lookup expression that reads from DDIC table T100, which is used for storing message short texts in an SAP system. The expression shall return the number of records (aggregation function Count) with German short texts (SPRSL = DE). In a given system, the expression may yield the following results, depending on the distinction setting:

    Setting

    Result

    Comment

    Select Count of Records From T100 With SPRSL = DE

    110650

    Total of table rows with the language field (SPRSL) set to DE

    Select Count of ARBGB From T100 With SPRSL = DE

    1615

    Total of message classes (ARBGB) with at least one message in language DE

    Select Count of SPRSL From T100 With SPRSL = DE

    1

    Total of unique table rows with the SPRSL field set to DE. Though there are more than 100000 rows in the table that satisfy the condition, the distinct selection for the SPRSL field lets the search stop after the first match because the second match would already yield a duplicate.
    End of the example.

    It depends on the type of an elementary data object to determine which aggregation functions are supported:

    Type

    Supported Aggregation Functions

    Number, Amount, Quantity

    All aggregation functions

    Boolean, Text

    Maximum, Minimum, Count

    Timepoint

    Count

  • Existence Check Mode

    In this mode, the result data object yields either true or false depending on whether a line exists in the DDIC table that satisfies the WHERE condition.

Field Mapping

When you are working with a DB lookup expression in data retrieval mode, this means that you want to collect a particular subset of records from the database table used as the context data object, and that you want to transfer the selected records into a BRFplus table data object used as the expression's result data object.

Generally, the two table data objects involved are not identical (because otherwise, you would overwrite the original table content with the result of the selection). As a consequence, you need to define a mapping relationship between the fields from the source table to the fields in the target table. The maximum number of possible relationships is determined by the number of fields available in the target table. To define the field mapping, you can use the following features:

  • Automatic mapping

    As soon as you have defined both the source and the target table, the system automatically scans the source table for fields with the same name and type as the fields in the target table. Each matching source field is assigned to the corresponding target field. If you are familiar with the ABAP programming language, you may already know this mechanism that is available with the MOVE-CORRESPONDING statement. The automatic field mapping is just a proposal and can be overwritten manually.

  • Manual mapping

    You can manually associate source fields to the target fields. This is useful to map fields that have the same semantic but different names, so that the automatic mapping mechanism cannot recognize their correspondence. You can also manually choose from the source fields for already existing automatic mappings.

    For manual mapping, the system offers you the list of source fields in a menu. However, this menu is restricted to the first nine fields of the table. If the source table contains more fields, you can access these fields by choosing Select other element from the menu.

  • Field aggregation and grouping

    You can define that a target field receives an aggregated value derived from the values found in the corresponding source field. For this, the system offers the same aggregation functions that are also available in aggregation mode of operation (maximum, minimum, average, count, sum). For an overview of the supported aggregation functions for different data types, see the respective table in the Modes of Operation section above.

    If you use field aggregation, all other target fields that are currently not using aggregation are automatically assigned the Group By flag. This is to make sure that only the set of unique tuples of these fields is written to the target table, rather than repeating the value of the aggregated field for each single record in the selection.

    Note Note

    If an aggregated source field is of type amount or quantity, the system automatically adds a grouping by the currencies or units of measure that appear in the source field. This additional grouping is not visualized in the DB lookup UI in the BRFplus workbench, but you can check it in the SQL view of the selection criteria.

    End of the note.

  • SQL view

    All the settings that you make with respect to field mapping, aggregation, and grouping have an impact on the way how the system accesses the table used as the expression's context data object. If you are a programmer, you may find it helpful to see the SQL code that the system generates in order to reflect the settings that you make in the UI. For this, the system offers you an insight into the SQL representation of the current settings. You can have a look at the SQL statement by choosing View SQL.

  • Restoring default settings

    After having applied different manual modifications to the default field mapping, it may sometimes turn out that the expression behavior changes into an undesired direction. In such a situation, it can be helpful to undo all the changes and start right from the beginning. For that purpose, you can choose Restore Default Mapping. This function reverts all manual mappings and restores the automatic mapping based on the MOVE-CORRESPONDING logic.

Constraints

The DB lookup expression is subject to limitations with respect to the complexity of the database access command (WHERE clause) that the system generates depending on the given fields and conditions. The following limitations apply:

  • Maximum number of fields: 255

  • Maximum number of conditions: 255

More Information

Creating a DB Lookup Expression

Data Binding

Table Operation Expression