LibraryTech NotesUsing SQL-WHERE in MapForce

When you are using MapForce for a data mapping project that includes a database source, you can use the SQL-WHERE component to filter the data on a granular level before writing it to the target data structure, whether that be another database, an XML file, a flat file, an EDI message, or a Web service.

Once you have inserted a source database in your MapForce data mapping design using the database connection wizard, follow the steps below to configure a SQL-WHERE component:

1) Click the SQL-WHERE condition on the MapForce toolbar to insert a SQL-WHERE condition in your data mapping design.

2) Drag a connecting line to link the database table you wish to filter to the Table input field on the SQL-WHERE component.

3) MapForce automatically opens the SQL-WHERE properties dialog and displays the SELECT statement that was auto-generated based on the table you connected to the input field (above). Enter the parameter(s) and condition(s) for the WHERE statement and set the parameter type (string, decimal, etc.). Then click OK to close the dialog.

4) To supply a value for the parameter of the filter, insert a ‘constant’ component using the MapForce toolbar button. In this example, we are filtering the Employee table of the database to map employees with the title “MANAGER” to the Manager field in the target data structure, so the value of the constant is “MANAGER”.

5) Map the result field of the SQL-WHERE component to the desired table in the target data structure, and map other fields as required.

6) Click the Output tab to see immediate results, or generate program code to implement your mapping (royalty-free) as a software application.

See the MapForce Online Help file for more information on creating SQL-WHERE filters.

If you are not already a MapForce user, you can download a free, 30-day trial to check out this feature.