General SQL syntax

A simple SQL query:

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

The Konica Minolta component requires that the first column be the value for the list item and the second column be 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 symbols:

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

In this example, all customers where Customer Name ends with the text “red” will be returned.  For example, customers where Customer Name is “Fred”, “Cred”, “1red” or “$red” will be returned.

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 “_” is used to specify a wildcard for one character.  The wildcard “%” is used to specify a wildcard for one or more character.

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 AutoStore form.

RRT pattern takes the following format: ~KMO::%AutostoreFormFieldName%~.

It is important to note that the “%” symbol used with RRT patterns does not function like the “%” symbol used for wildcard based searches.  With RRT patterns, the entire RRT pattern will be replaced with the field value that was entered by the user from the AutoStore form.  The AutoStore form field should be configured to generate a change event to provide a value for replacement.

Example 1:

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

In this example, the AutoStore form has the RRT pattern “patternFld”.  Once a user enters a value for “patternFld”, ~KMO::%patternFld%~ will get replaced with the value of patternFld.

If the user entered “Johnson” at the MFP for patternFld, 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 such as wildcard characters.  The following would be a valid SQL statement:

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

Using self-referencing RRT Patterns

If the database lookup field has a SQL statement that contains a self-referencing RRT pattern, then a text field with the Search option will be used instead of a list field.

select “Customer Name”
   from “Customers”
   where “Customer Name” like ‘%~KMO::%thisDbLookupFieldName%~%’
         

RRT will take the value the user has typed before pressing the Search button.