SQL Syntax

A simple SQL query

select <ValueField>, <DisplayField>
   from <Table>
   where <Some Condition>

The first column must be the value for the list item and the second column the display for the list item.

A select statement can also return a single column

select <ValueField>
   from <Table>
   where <Some Condition>

In this example, the column will be used for both the value and display for the list item.

A conditional select statement

select “Customer Id”, “Customer Name”
   from “Customers”
   where “Customer Name” = ‘Fred’

In this example, all customers where Customer Name is “Fred” will be returned.

A conditional select statement using wildcard characters

select “Customer Id”, “Customer Name”
   from “Customers”
   where “Customer Name” like ‘%Johnson’

In this example, all customers where Customer Name ends with the text “Johnson” will be returned.  For example, customers where Customer Name is “FJohnson”, “FredJohnson”, “Johnson” or “3Johnson” will be returned.

SQL wildcard syntax is used with the like operator.  The % wildcard character is used to specify one or more characters.

Using a RRT pattern in a conditional select statement:

It is possible to use a RRT pattern in a conditional select statement if field values are entered by the user from the Workflow form.

RRT pattern takes the following format: ~CAN::%FieldName%~.

It is important to note that the % wildcard character used with RRT patterns does not function like the % delimiter character used for field searches.  With RRT patterns, the entire pattern between the two % delimiter characters will be replaced with the field value that was entered by the user from the workflow form.

Example

select “Customer Id”, “Customer Name”
   from “Customers”
   where “Customer Name” like ‘~CAN::%Customer%~’

In this example, the workflow form has the RRT pattern “Customer”.  Once a user enters a value for “Customer”, ~CAN::%Customer%~ will get replaced with the value entered in the Customer field.

If the user entered “Johnson” at the MFP for Customer, the resulting query would be:

select “Customer Id”, “Customer Name”
   from “Customers”
   where “Customer Name” like ‘Johnson’

Note that RRT patterns within SQL select statements can be used with other SQL syntax, including wildcard characters.  The following would be a valid SQL statement:

select “Customer Id”, “Customer Name”
   from “Customers”
   where “Customer Name” like ‘%~CAN::%Customer%~%’