Filtering and Sorting Database Data (SQL WHERE/ORDER)

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > Databases and MapForce >

Filtering and Sorting Database Data (SQL WHERE/ORDER)

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, a 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).

 

Adding a 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:

mf_sql-where

2.Connect a source database table or field to the table/field item of the SQL WHERE/ORDER. For an example, open the mapping DB_PhoneList.mfd from the folder <Documents>\Altova\MapForce2019\MapForceExamples\. 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 "B".

mf_sql-where1

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.

mf_sql-where2

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 DB_PhoneList.mfd mapping (these settings are further explained below). For more examples, see Creating WHERE and ORDER BY Clauses.

 

Supplying parameters to a SQL WHERE/ORDER

The SQL WHERE/ORDER component used in the mapping DB_PhoneList.mfd defines a WHERE clause as follows:

 

Last LIKE :Name

 

"Last" refers to the name of a database field in the connected table. "LIKE" is an SQL operator. ":Name" creates a parameter called "Name" 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 "B%"

 

This would retrieve all persons whose last name begins with letter "B". In order to make this query even more flexible, we added a parameter instead of "B%". This makes it possible to supply any other letter from the mapping (for example, "C", and thus retrieve people whose last name begins with "C" simply by changing a constant, or a mapping input parameter).

 

Appearance of SQL WHERE/ORDER components

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:

 

sql-ord3

A WHERE clause has been defined.

sql-ord1

A WHERE clause with a parameter has been defined. The parameter "Name" is visible under the "table/field" item.

sql-ord2

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.


© 2019 Altova GmbH