Creating WHERE and ORDER BY Clauses
After an SQL/NoSQL-WHERE/ORDER component is added to the mapping, it needs a WHERE condition (clause) through which you specify how exactly you want to filter the data connected to the component. The WHERE condition must be entered in the dialog box SQL/NoSQL-WHERE/ORDER Properties (see previous section).
Writing a WHERE condition from MapForce is similar to writing the same SQL clause outside MapForce. Use the syntax applicable to the SQL dialect of the corresponding database. For example, you can use operators, wildcards, as well as sub-selects or aggregate functions. To create a parameter that you can pass from the mapping, enter a colon character ( : ) followed by the parameter's name.
| Note: | When you finish writing the WHERE clause and click OK, MapForce validates the integrity of the final SQL statement. A dialog box prompts you if there are syntax errors. |
The table below lists some typical operators that can be used in the WHERE clause:
Operator | Description |
|---|---|
= | Equal |
<> | Not equal |
< | Less than |
> | Greater than |
>= | Greater than/equal |
<= | Less than/equal |
IN | Retrieves a known value of a column |
LIKE | Searches for a specific pattern |
BETWEEN | Searches between a range |
Use the % (percentage) wildcard to represent any number of characters in a pattern. For example, to retrieve all records ending in "r" from a field called lastname, use the following expression:
lastname = "%r" |
When querying databases that support storing and querying of XML database data (for example, IBM DB2, Oracle, SQL Server), you can use XML functions and keywords applicable to that particular database, for example:
xmlexists('$c/Client/Address[zip>"55116"]' passing USER.CLIENTS.CONTACTINFO AS "c") |
See also Example: Extracting Data from IBM DB2 XML Type Columns.
Optionally, if you want to sort the retrieved recordset by a particular field, add an ORDER BY clause in the corresponding text box of the dialog box SQL/NoSQL-WHERE/ORDER Properties. To sort by multiple fields, separate the field names by commas. To change the sort order, use the ASC and DESC keywords. For example, the following ORDER BY clause retrieves records ordered by lastname, and then by firstname, in descending order:
lastname, firstname DESC |
Example 1
The following WHERE condition is attached to the users table of the Nanonull.sqlite database component. It retrieves those records where last_name is greater than the letter M. In other words, it retrieves all names starting from user called Marzolla onwards.
last_name > "M" |
Note how the connections are placed:
•The connection to table/field originates in the table that you want to query (users in this case).
•The result output is connected to a parent item of the fields that are queried/filtered (in this case the row item).
Example 2
The following WHERE condition creates a parameter param which then appears in the SQL/NoSQL-WHERE/ORDER component in the mapping.
last_name LIKE :param |
The constant component %M supplies the value of param. The wildcard % denotes any number of characters. This causes the mapping to search for a pattern in the column last_name (all last names starting with the letter M).
Example 3
The following WHERE condition creates two parameters, min and max, to which the current values of quantity are compared. The min and max values are supplied by two constant components from the mapping.
quantity > :min and quantity < :max |
The WHERE condition in this example could also be written using the BETWEEN operator:
quantity BETWEEN :min and :max |