Filter and Sort Database Data
When you need to filter and sort database data, use the SQL/NoSQL-WHERE/ORDER component. This enables you to manually enter an SQL WHERE clause that filters data. Optionally, you can also specify an ORDER BY clause if you want to sort the record set by a particular database field, in ascending or descending order.
The SQL/NoSQL-WHERE/ORDER component must be connected to a table or field of a database mapping component. It is also possible to connect an SQL/NoSQL-WHERE/ORDER component with a Join component if you need to filter the joined set or records. For more information, see Joining Database Data.
Add an SQL/NoSQL-WHERE/ORDER component
To add an SQL/NoSQL-WHERE/ORDER component to the mapping, follow the instructions below:
1.Go to the Insert menu and click SQL/NoSQL-WHERE/ORDER. By default, this component has the following structure:
2.Connect a source database table or field to the table/field item of the SQL/NoSQL-WHERE/ORDER component. You can find the sample mapping FilterDatabaseRecords.mfd (see screenshot below) in the following folder: <Documents>\Altova\MapForce2024\MapForceExamples\Tutorial\. In this mapping, the SQL/NoSQL-WHERE/ORDER component takes the data from the source table users, filters all its records and selects only those where the last name begins with the letter M (see the explanation in the subsection below).
3.Double-click the header of the SQL/NoSQL-WHERE/ORDER component. Alternatively, right-click it and select Properties from the context menu. This opens the dialog box SQL/NoSQL-WHERE/ORDER Properties.
4.Type an SQL WHERE clause in the text box at the top. In our example, the SQL Where clause is as follows: last_name LIKE :sqlparam. Optionally, type an 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.
Parameters in SQL/NoSQL-WHERE/ORDER components
The SQL/NoSQL-WHERE/ORDER component used in the mapping FilterDatabaseRecords.mfd has the following WHERE clause: last_name LIKE :sqlparam, where last_name refers to the name of the database field in the connected table; LIKE is an SQL operator; :sqlparam creates a parameter called sqlparam in the mapping.
Parameters in the SQL/NoSQL-WHERE/ORDER component 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 the letter M. In order to make this query even more flexible, we have added a parameter instead of "M%". This makes it possible to supply any other letter from the mapping: e.g., D, and thus retrieve people whose last name begins with D by changing a constant or a mapping input parameter. In the mapping above, the input letter comes from an 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 (see screenshot below).
In the mapping, the SQL wildcard character % is provided by a constant. This wildcard character is then concatenated with the parameter value with the help of the concat function. The advantage is that you do not have to type SQL wildcards in the command line if this mapping runs in another environment (e.g., MapForce Server).
Appearance of SQL/NoSQL-WHERE/ORDER components
SQL/NoSQL WHERE/ORDER components change their appearance depending on the settings defined in them. This way you can quickly view directly from the mapping what the SQL/NoSQL WHERE/ORDER component does (see table below).
 | 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. |
If you place the mouse cursor over the SQL/NoSQL WHERE/ORDER header, you will see a tooltip displaying the various clauses that have been defined.