Querying data providers with the Query activity

The Query activity reads and writes information through an MS SQL connection, ODBC connection, Excel sheet, or LDAP provider. Administrators can use the Query activity to retrieve information and store it in custom variables for use within workflows. Advanced queries return multiple records of information that can be stored in multiple variables.

For example, an administrator uses a Query activity in an Inbound Email workflow to retrieve customer account balances from a SQL database. The administrator populates an Email activity template with a variable that will contain this information, retrieved from the Query activity. The workflow responds with balance information to customers inquiring via email.

The Query activity is available to all Multimedia Contact Center workflows.

Note:
  • By default, the query result is stored within the <<LastQueryResult>> system variable. Each subsequent Query action overwrites this variable. It is not recommended to use the LastQueryResult variable when expecting protected information to be contained within this variable.
  • For information on configuring data providers, see "Configuring data providers".

Connecting Query activities to data providers

Connecting Query activities to data providers enables the Query activity to access data and use this information to route interactions. See "Configuring data providers" for more information.

  1. Select the Query activity and click Properties.
  2. After Data Provider, click the Browse button.
  3. Select a data provider and click OK.
    Note:

    The Query activity cannot be saved without a query or write statement configured.

Running simple queries in workflows

Running simple queries enables administrators to retrieve information from a database and store it in a variable.

To run a simple query in a workflow
  1. Select the Query activity and click Properties.
  2. After Query statement, click the Browse button and, under Query Type, select Simple Query.
  3. After Database Table Name, from the drop-down list, select the table to query.

    'Database table names' refer to the tables in the data source from which the Query activity retrieves information.

  4. After Column Return Name, from the drop-down list, select the column of data to query.

    'Column return names' refer to the columns in the selected table, from which the Query activity retrieves information.

  5. After Variable Return Name, from the drop-down list, select the variable to populate with the return value.

    'Variable return names' refer to the variables populated as a result of the query.

  6. After Column Where Name, from the drop-down list, select the name of the column of data to compare against.

    'Column where names' refer to the columns in the data source against which the Query activity compares information.

  7. After Variable Where Name, from the drop-down list, select the variable to use for comparison.

    'Variable where names' refer to the variables against which the Query activity compares.

  8. To test the query, after Test with a value equal to, enter an existing value from the data provider and click Test Query.

    The Raw SQL window displays the SQL statement based on the selections from the drop-down lists.

  9. To clear all fields and return to the Canvas, click Clear. To accept the query, click OK.
  10. Click Save.

Running advanced queries in workflows

Advanced queries enable administrators with knowledge of SQL to write SQL statements retrieving information from a database provider. Advanced queries also support LDAP syntax for LDAP providers, and advanced queries for MS SQL Server and ODBC data providers support stored procedures.

Administrators can use an advanced query to retrieve single or multiple records from a database provider.

To run an advanced query in a workflow
  1. Select the Query activity and click Properties.
  2. After Query statement, click the Browse button, and under Query Type, select Advanced Query.
  3. Under Query, type the SQL statements to be run directly against the data provider and click Execute.

    The Execute window opens displaying a list of detected input and output parameters.

  4. Under Value, type the numbers corresponding to the values contained in the data provider.
  5. Click Run.
  6. To assign variables to store information returned by the SQL statements, click the Variable Assignment tab.

    Return Column will be populated with the column names in the data provider.

  7. Under Variable Name, select the variables to store information returned by the SQL statements.
  8. To assign values to the parameters filled by the SQL statements, click the Parameter Assignment tab.

    Parameter Name will be populated with the SQL parameters used to query the data provider.

  9. After Variable Name, select a variable from the drop-down list to store the information retrieved by the SQL parameter.
  10. To view the values returned from the query, click the Test Results tab.
  11. To clear the variables and their parameters, click Clear. To accept the query, click OK.
  12. Click Save.

Defining write statements

Write statements define a delete, insert, or update statement against a specified data provider. Write statements enable administrators to delete, insert, or update column values in a data provider from within a workflow.

The following procedures explain how to configure simple and advanced write statements

Note:

Advanced write statements enable users with knowledge of SQL to write insert, update, or delete SQL statements.

To define a simple write statement
  1. Select the Query activity, click Properties and, after Write statement, click the Browse button.
  2. Select Simple Write and select a Write Type from the drop-down list. Options vary by data provider.
  3. After Table Name, select the table in the data provider against which the write statement is defined.
  4. Under Column Name, select a column in the data provider against which the write statement is defined.
  5. Under Value, select the data to be inserted into the data provider.
  6. Under Where, select a column name.
    Note:
    • ‘Where’ statements display only if Delete or Update is selected
    • The column names displaying in the list derive from the Table Name selected.
  7. Select an operator from the drop-down list
    • =
    • <>
    • >
    • <
    • >=
    • <=
  8. From the third drop-down list, select or type the variables used to evaluate the information selected from the first column
  9. To test the write statement, click Test Syntax.
  10. To empty column values, click Clear. To accept the write statement, click OK.
  11. Click Save.
To define an advanced write statement
  1. Select the Query activity, click Properties.
  2. After Write statement, click the Browse button and select the Advanced Write button.
  3. Under Query, type the insert, update, or delete SQL statements to be run against the data provider and click Execute.
  4. The Parameter name column will be populated with the column names in the data provider.
  5. Under Variable Name, from the drop-down list, select the variables used to evaluate the information you selected from the first column.
  6. To test the write statement, click Test Syntax.
  7. To empty column values, click Clear. To accept the write statement, click OK.
  8. Click Save.

Returning multiple results with queries

The Query activity supports returning multiple results from a query. When a Query activity returns multiple results from a query, the Success branch is followed for each result. When there are no more results, the message exits the Success branch of the Query activity and continues along the workflow.

Multiple result returning Query activities enable contact centers to build more complex workflow functions, such as providing callers access to an employee directory.

Parent topic: Activity list
↑