Creating WHERE and ORDER BY Clauses

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) >

Creating WHERE and ORDER BY Clauses

After an SQL 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 "SQL WHERE/ORDER Properties" dialog box of MapForce, as shown in the 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 parameters that you can pass from the mapping, enter a semi-colon character ( : ) followed by the parameter 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 "SQL WHERE/ORDER Properties" dialog box. 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 Person table of the altova.mdb database component. It retrieves those records where First and Last are greater than the letter "C". In other words, it retrieves all names from "Callaby" onwards.

First > "C" AND Last > "C"

Note how the connections are placed:

 

The connection to table/field originates in the table that you want to query, "Person" in this case.
The result output is connected to a "parent" item of the fields that are queried/filtered, in this case the Person item.

whereeg-1

 

Example 2

The following WHERE condition creates a parameter Name which then appears in the SQL WHERE/ORDER component on the mapping.

Last LIKE :Name

sql-where4a

The constant component %S supplies the value of the Name parameter. The wildcard % denotes any number of characters. This causes the mapping to search for a pattern in the column "Last" (all last names ending in "S").

 

Example 3

The following WHERE condition creates two parameters, PhoneUpper and PhoneLower, to which the current values of PhoneExt are compared. The upper and lower values are supplied by two constant components shown in the diagram below.

PhoneExt < :PhoneUpper and PhoneExt > :PhoneLower

whereeg-3

The WHERE condition in this example could also be written using the BETWEEN operator:

PhoneExt BETWEEN :PhoneUpper and :PhoneLower


© 2019 Altova GmbH