These steps show the equivalent method of restricting the number of rows selected in a Mapping node:
$db:select_1.BROKER50.JDOE.RESOLVEASSESSOR.ASSESSORTYPE = 'WBI' or $db:select_1.BROKER50.JDOE. RESOLVEASSESSOR.ASSESSORTYPE = $source/tns:msg_tagIA81CONF/AssessorType
A $db:select entry retrieves all qualifying rows, so it is possible that more than one row is retrieved. By default, the selection is treated as repeating, which is indicated by the ‘for’ row below $db:select in the Spreadsheet pane.
After you have configured the $db:select, populate the target message from the database by dragging the database column from the Source Pane to the message element in the Target pane. The mapping is indicated by a line between the database column in the Source pane and the element in the Target pane. An entry for this map in Xpath format also appears in the Spreadsheet pane. Triangular icons appear in the Source and Target panes next to objects that have been mapped.
By default a $db:select entry is accompanied by a 'for' row that iterates over the select result set. Ensure that your 'for' row is in the correct position for your mapping. The behavior of the map is determined by the position of the 'for' row in the Spreadsheet pane. For example, if the results of the $db:select statement matched 5 rows in the database and the 'for' row is located above the $target entry in the Spreadsheet pane, then 5 complete messages are output by the mapping node. If the 'for' row is positioned within the message body, then one message is generated with 5 repeating elements in the message body.
A mapping can contain multiple 'for' rows associated with a $db:select entry that perform a single database select and iterate over the results multiple times. For example, multiple 'for' rows can be used in conditional mappings, where an individual 'for' row is used with a 'condition' or an 'else'.
For example: fn:sum or fn:count.
Database table join
Table Column Row 1 Row 2 Row 3 Row 4 ORDER PRODUCT_ID 456 456 345 123 QUANTITY 100 200 300 400 PRODUCT PART_NUMBER 123 456 789 012 PART_NAME pen pencil paperclip glue PRICE 0.25 0.15 0.02 0.99
$db:select.MY_DB.SCHEMA1.ORDER.PRODUCT_ID=$db:select.MY_DB.SCHEMA2.PRODUCRT.PART_NUMBER
Row 1 Row 2 Row 3 PRODUCT_ID 456 456 123 QUANTITY 100 200 400 PART_NUMBER 456 456 123 PART_NAME pencil pencil pen PRICE 0.15 0.15 0.25You can then use the 'for' row to iterate through the results set in the same way as results from a single table.