Configure SQL validations

You can use an SQL statement to generate the values in a validation. SQL can be used as a validation method for drop-down lists, auto-complete fields and check box components.

A validation may already exist that meets your process requirements. If it does, consider using that validation in your process. Also consider copying and modifying validations that are similar to the validation you want. For a complete list of validations that are delivered with the product, see View system validations.

SQL Validated Drop Down List/Check Box

To define a dynamic list for a drop down list:

  1. Open a new or existing validation, see Configure validations.

  2. In the Component Type field, select Drop Down List or Check Box.

    Note: The Check Box option is available only when the Enable Checkbox List Validation feature toggle is turned on. For details, see Use feature toggles to turn on/off features.

    The fields in the Validation window change dynamically, depending on your selection.

  3. In the Validated By field, select SQL.

  4. In the SQL field, provide the Select statement that queries the necessary database information. An ending semicolon is not necessary.

  5. To add token to your SQL statement, click Tokens, select the token text that you want, copy the text, close the Tokens window, and paste the text into the SQL statement where it is needed.

SQL Validated Auto-Complete Lists

Custom auto-completes or validations (Validated by: SQL-Custom) can be configured for short or long list formats.

User auto-completes or validations (Validated by: SQL-User) have the following default filter fields:

  • Primary field - this field takes the name of the auto-complete field

  • First name

  • Last name

The user auto-complete always appears in the long list format, which uses the paging interface to display the items. Additionally, user auto-completes display a different icon.

  1. Open a new or existing validation, see Configure validations.

  2. In the Component Type field, select Auto Complete List.

    The fields in the Validation window change dynamically, depending on your selection.

  3. In the Validated By field, select SQL - Custom or SQL - User.

  4. For SQL - Custom, Selection mode, select one of the following:

    Selection

    Description

    Starts with

    Type characters and if the list is not automatically filtered, then type the Tab key. The selection window opens and lists entries that begin with the specified characters.

    Contains

    Type characters, and if the list is not automatically filtered, then type the Tab key. The selection window opens and lists entries that contain the specified character string. This is the same behavior as a wild card search, which uses the % character at the beginning of the search text.

    This setting only controls the matching on the Select page. Matching in the auto-complete field is controlled by including specific clauses in the auto-complete's SQL.

  5. For SQL - Custom, in the Expected list length field, select one of the following:

    Selection

    Description

    Short

    Auto-completes configured as short lists load all values when the window is opened. This can adversely affect performance.

    Long

    Auto-completes configured as long lists load a limited set of values when the window is opened. By default, 50 results are shown per page. End users can page through the results or further limit the results by specifying text in one of the available filter fields at the top of the page.

  6. If you selected a long list, in the Number of results per page, indicate the number of results you want displayed on each page.

  7. On the Configuration tab, in the SQL field, provide the Select statement that queries the necessary database information. An ending semicolon is not necessary.

    For example, XYZ Corporation creates an auto-complete field that lists all users in the Engineering department and is validated by SQL. 

    SELECT U.USER_ID, U.USERNAME, U.FIRST_NAME, U.LAST_NAME
FROM KNTA_USERS U, KNTA_SECURITY_GROUPS SG, KNTA_USER_
SECURITY US
WHERE SG.SECURITY_GROUP_ID = US.SECURITY_GROUP_ID AND
US.USER_ID = U.USER_ID
AND SG.SECURITY_GROUP_NAME = 'Engineering'
and UPPER(u.username) like UPPER('?%')
and (u.username like upper(substr('?',1,1)) || '%'
or u.username like lower(substr('?',1,1)) || '%')
order by 2

    After a new user account is created and added to the Engineering security group, that user is automatically included in the auto-complete. If you are using an auto-complete component, you can define headers for the selected columns. These column headers are used in the window that opens if a value from an auto-complete is selected.

    Note: If you use the same token in both the WHERE clause of the SQL statement and the SQL statement of the validation's filter field, PPM may return no value for the ACL field when you change the value of the field associated with the token in PPM Web pages.

    This is because the value of the token used in the WHERE clause of the ACL validation's SQL statement is retrieved from the Web pages, while the value of the token used in the SQL statement of the validation's filter field is retrieved from PPM database.

    To avoid this issue, we recommend that you not use the same token in the WHERE clause of an ACL validation's SQL statement and the SQL statement of the validation's filter field.

  8. To configure "starts with" matching from the auto-complete window to the selection window, add the following to the SQL WHERE clause:

    UPPER(value) like UPPER('?%') and (value like
upper(substr('?',1,1)) || '%' or value like
lower(substr('?',1,1)) || '%')

  9. To configure "contains" matching from the auto-complete window to the selection window, add the following to the SQL WHERE clause:

    UPPER(value) like UPPER('%?%') and (value like '%' ||
upper(substr('?',1,1)) || '%' or value like '%' ||
lower(substr('?',1,1)) || '%')

  10. Under the Column Headers table, click New.

  11. Specify values for the following:

    Field Name

    Description

    Column Header

    Column name to display in the auto-complete window.

    Display

    Determines whether or not the column is visible. The first column is never visible and the second column is always visible.

  12. For short lists, you are done, click Save.

  13. For long lists and for SQL - User, you can continue with Add search fields to auto-complete validationss.