Retrieving data from a data connection by using a mapped query

A mapped query is used to retrieve one or more rows from a data connection for a business object field, based on a complete SQL SELECT query or stored procedure call.

About this task

At run time, the SQL query retrieves all rows that match the query. One business object instance is created for each row returned, replacing the original business object.

Defining a mapped query mapping group involves:

  • Creating a new mapped query mapping group
  • Adding fields to the mapped query mapping group
Parent topic: Retrieving data from a data connection by using a mapped expression

Creating a new mapped query mapping group

Procedure

  1. In the Event Explorer view, double-click the business object name to open the business object in its editor. By default, the business object is opened in the Overview tab.
  2. Click the Data Mappings tab.
  3. Select the first field to add to the new mapping group in the Database Mappings table, then click New Mapping Group. The New Mapping Group wizard opens.
    Note: The New Mapping Group button is disabled when the field is not eligible as a member of a mapping group.
  4. For the Enrichment type field, select Mapped query.
  5. Select the Data connection from the available list.
    Note: If the field is already associated with a data connection, you cannot change this value.
  6. For the Field mapping field, enter the name of a column of the result set that is produced by your SQL query or stored procedure call.
    Note: Enter only the column name, for example City; and do not qualify the column name with a table name, for example Customer.City. The Field mapping field is case-sensitive.
  7. Click Next.
  8. Enter the Group expression, which is the complete SQL SELECT query or stored procedure call. You can press Ctrl+Spacebar to open the context assist menu. From the menu, you can select an entry by double-clicking the name.
    Note: If you use a stored procedure, the stored procedure must return a result set.
    • To build a static expression, enter any valid SQL SELECT query or stored procedure call. For example, SELECT Customer_Number, City FROM Customer WHERE Customer_Number = 1234.
    • To build an expression with variable substitutions, either reference a business object field in the WHERE clause or reference a business object field in an argument of the stored procedure by using $(business object field name) syntax. For example, SELECT Customer_Number, City FROM Customer WHERE Customer_Number = $(customer number). At run time, the value of the selected business object field is used as the variable value.

      For string variables, the referenced business object field must not contain bounding quotation marks. They are automatically provided when the SQL is built at run time.

  9. Click Finish.

Results

You have created a new mapped query mapping group with one field.

What to do next

You might want to add more fields to the mapped query mapping group.

Adding fields to the mapped query mapping group

Procedure

  1. Select the next field to add to the mapping group that you created in Creating a new mapped query mapping group in the Database Mappings table, then click Add to Mapping Group. The Add to Mapping Group wizard opens.
  2. Select the Mapping group from the list of available numbered mapping groups. The wizard displays the enrichment type, data connection, and table information that you already defined for this mapping group.
  3. For the Field mapping field, enter the name of a column of the result set that is produced by your SQL query or stored procedure call.
    Note: Enter only the column name, for example City; and do not qualify the column name with a table name, for example Customer.City. The Field mapping field is case-sensitive.
  4. Click Finish.

What to do next

Continue to add fields until your mapping group is complete. Save your changes.