Result Substitution is applicable for Database Lookup filters, and Database communication points running in Out->In mode, where a message is being updated with some values retrieved via one or more database queries. Once the queries have been executed, the results can be inserted into message properties, fields, or both. The result substitution editor is at the bottom of both the SQL Editor and Stored Procedure Editor:
Result substitution can be defined for columns returned via a result set, or stored procedure output parameters that were defined for the current stored procedure call. Each row in the table is used to retrieve the value of a particular column or output parameter, and can be used to insert into both a field path or message property. If there is a need to insert the value into multiple message properties or multiple field paths, then the column or output parameter can be used in multiple rows.
A result substitution entry can be removed by clearing the column name.
Data for Result Substitution
The following table describes the data to be defined for result substitution:
Column |
Description |
|---|---|
Column |
This is the name of the column returned in the result set, or alternatively the output parameter name. If this is a stored procedure call and there is both an output parameter and column with the same name, the output parameter is used. |
Set Message Property |
The name of the message property to set with the value of the selected column or output parameter. If this cell is empty, no message properties are set. |
Set Message Field |
This is the field path that will receive the column or output parameter value. Field paths can be edited by clicking on the link, or by dragging a field from the Message Structure toolbox onto the cell. Automatically generated field paths will take account of statement iteration when generating the field path. If this cell is empty, no fields are set. |
Default Value |
The default value is used for column result substitution when no rows were returned in the result set. However, for this to occur, the appropriate option must be disabled in the Statement Options, refer to Statement Options for details. Default values are always ignored for result substitution of output parameters, because output parameters will always have a valid value when result substitution occurs. |
Condition |
Result substitution can be made conditional by adding a condition in this column. Click the Create Condition link to launch the Condition Editor, which enables you to create or edit a condition. |