When you need to filter and sort database data, use an SQL WHERE/ORDER component. This enables you to manually enter, from the MapForce graphical user interface, an SQL WHERE clause that filters data. Optionally, you can also specify an ORDER BY clause if you want to sort the recordset by a particular database field, in ascending or descending order.
The SQL WHERE/ORDER component must be connected to a table or field of a database mapping component. It is also possible to connect the SQL WHERE/ORDER to a Join component, if you need to filter the joined set or records (see Joining Database Data).
To add an SQL WHERE/ORDER component to the mapping:
1.On the Insert menu, click SQL WHERE/ORDER. By default, the SQL WHERE/ORDER component has the following structure:
2.Connect a source database table or field to the table/field item of the SQL WHERE/ORDER. For an example, open the mapping FilterDatabaseRecords.mfd from the folder <Documents>\Altova\MapForce2021\MapForceExamples\Tutorial\. In this mapping, the SQL WHERE/ORDER is used to filter from the source table "Person" all records where the last name begins with letter "M".
3.Double-click the header of the SQL WHERE/ORDER component (or right-click it and select Properties from the context menu). This opens the "SQL WHERE/ORDER Properties" dialog box.
4.Type the SQL WHERE clause in the text box at the top. Optionally, type the ORDER BY clause. The image above illustrates the WHERE and ORDER BY clauses defined in the FilterDatabaseRecords.mfd mapping (these settings are further explained below). For more examples, see Creating WHERE and ORDER BY Clauses.
The SQL WHERE/ORDER component used in the mapping FilterDatabaseRecords.mfd defines a WHERE clause as follows:
last_name LIKE :sqlparam
•"last_name" refers to the name of a database field in the connected table.
•"LIKE" is an SQL operator.
•":sqlparam" creates a parameter called "sqlparam" on the mapping.
Parameters in SQL WHERE/ORDER components are optional; they are useful if you want to pass a value to the WHERE clause from the mapping. Without parameters, the WHERE clause above could have been written as follows:
Last LIKE "M%"
This would retrieve all persons whose last name begins with letter "M". In order to make this query even more flexible, we added a parameter instead of "M%". This makes it possible to supply any other letter from the mapping (for example, "D", and thus retrieve people whose last name begins with "D" simply by changing a constant or a mapping input parameter). In the mapping above, the input letter comes from a simple input component called input. If you double-click the title bar of this component and open its properties, you will notice that "m" is given as a design-time execution value:
On the mapping, the SQL wildcard character % is provided by a constant, and then concatenated with the parameter value with the help of the concat function. This has the advantage that one does not have to type SQL wildcards at the command line if this mapping will run in another environment, like MapForce Server.
An important thing about SQL WHERE/ORDER components is that they change appearance depending on the settings defined in them. This way you can quickly view directly from the mapping what the SQL WHERE/ORDER component does, for example:
A WHERE clause has been defined.
A WHERE clause with a parameter has been defined. The parameter "Name" is visible under the "table/field" item.
A WHERE clause with a parameter has been defined. Additionally, an ORDER BY clause has been defined. The sorting is indicated by the A-Z sort icon.
Placing the mouse cursor over the SQL WHERE/ORDER header opens a tooltip displaying the various clauses that have been defined.