SQL validation tips

The following information can be helpful when writing an SQL statement for an SQL-validated validation:

The SQL statement must query at least two columns.
- The first column is a hidden value that is never displayed, and is typically stored in the database or passed to internal functions.
- The second column is the value that is displayed in the field.
- All other columns are for information purposes and are only displayed in the auto-complete window. Extra columns are not displayed for drop-down lists.
When something is typed into an auto-complete field, the values displayed in the auto-complete window are constrained by what was first typed in the field. Typically, the constraint is case-insensitive. To constrain the list, write the SQL statement to query only values that match text that was typed.

Before the auto-complete list is displayed, all question marks in the SQL statement are replaced by the text that the user typed. Typically, if the following conditions are added to the WHERE clause in an SQL statement, the values in the auto-complete window are constrained by what the user typed.
```
where UPPER(<displayed_column>) like UPPER('?%')
and (<displayed_column> like upper(substr('?',1,1)) || '%'
or <displayed_column> like lower(substr('?',1,1)) || '%')
```
Any column aliases included directly in the SQL statement are not used. The names of the columns, as displayed in auto-completes, are determined from the section Column Headers. Drop-down lists do not have column headers.

Auto-Complete Matching Tips

Auto-complete field behavior can be divided into the following areas:

Field behavior. A user types a character in the field and type the Tab key. If an exact match is not available, the Select page opens.
Select page behavior. For lists that are configured appropriately, when a user types a character or characters into the field at the top of the page, the results are automatically limited to display only matching entries.

Consider the following tips as you configure the "starts with" or "contains" functionality for auto-complete fields and the Select page:

Auto-completes should be configured such that the field matching behavior works the same way as the Select page matching behavior. Specifically, if the auto-complete field uses the STARTING WITH clauses in the SQL, then the selection window should use the "Starts With" Selection Mode.
Consider using the "Contains" selection mode for fields with multi-word values. For example, possible values for the request type auto-complete field are:
```
Development Bug
Development Enhancement
Development Issue
Development Change Request
IS Bug
IS Enhancement
IS Issue
IS Change Request
Support Issue
```
The user must log a bug against one of the IS-supported financial applications. The user types "bug" into the auto-complete field and types the Tab key. The following items are returned:
```
Development Bug
IS Bug
```
The user selects "IS Bug." Without the "contains" feature enabled, typing "bug" would have returned the entire list. Typing "Financial," to find potential separate request type used for each type of supported application would have returned the entire list. And, the user would be forced to try another "starts with" phrase or read a long list.

The values in an auto-complete Validate By field, are as follows:

List. Used to provide specific values.
SQL. Uses an SQL statement to build the contents of the list.
SQL - User. Identical to SQL configuration, but includes a few additional preconfigured filter fields.
Command With Delimited Output. Uses a system command to produce a character-delimited text string and uses the results to define the list.
Command With Fixed Width Output. Uses a system command to produce a text file and parses the result on the basis of the width of columns, as well as the headers.