Using Interactive Query Tasks

 

1 What is Interactive Query Task?

SQLMessenger provides Interactive Query feature. In an Interactive Query Task, you can input query parameters in the query interface, and the system will execute the SQL statements of the task, displaying the query results on the interface. The query results can include text, tables, charts, Excel files, and PDF documents.

An Interactive Query task's interface

2 Create Interactive Query Task

The configuration method for the Interactive Query Task is similar to that for email tasks. During the configuration, you simply need to set the "Task Type" to "Interactive Query".

Task Config

Set "Task Type" to "Interactive Query"

The following features are not supported in the Interactive Query Task: Task Schedules, Data Extraction Code, Information Distribute.

2 Configuring Query Parameters for Tasks

In an Interactive Query Task, you can configure some query parameters for the task. Before the query starts, you can enter the values of these parameters in the query interface as the query conditions for the task.

Query parameters of an Interactive Query Task

2.1 Create New Query Parameter

Click the "Create New" button on the toolbar of the "Query Parameters" tab in the task editor to open the "New Query Parameter" dialog box.

Click the "Create New" button to create a new query parameter

New Query Parameter dialog box

In this dialog box:

Parameter Name	Enter the name for the query parameter here. You can use this name as a query parameter in SQL statements. The parameter name can only contain letters, numbers, or underscores, and it must start with a letter or an underscore. Once the query parameter is created, the parameter name cannot be modified. Use the parameter name as a query parameter in SQL statement
Parameter Label	Enter the label for the query parameter here (optional).The label will appear before the input box of the query parameter on the query interface. If no label is set, the system will use the "Parameter Name" as the label for the query parameter. Parameter label will appear before the input box of the query parameter
Parameter Type	Select the type of the query parameter. Different types of query parameters have different input methods. About Parameter Type
Parameter Description	Enter the description for the query parameter(optional). The description will be displayed on the query interface. Parameter Description will be displayed on the query interface
Required	Select whether this parameter is required to be entered. If "yes", a value for this parameter must be entered to execute the query.

2.2 About Parameter Type

SQLMessenger supports multiple parameter types. Different types of query parameters have different input methods.

Single-Line Text	Only a single line of text is permitted, without any newline characters. It is displayed as a single-line text box on the query interface. Single-Line text box
Multi-Line Text	Input is allowed for multiple lines of text. It is displayed as a multi-line text box on the query interface. Multi-Line text box
Number	Allow input of numbers (including decimals), which can be preceded by a + or - sign, and does not support scientific notation or exponential format. Number box
Date Time	Allow input of a complete date and time string that includes year, month, day, and hour, minute, and second, such as '08/08/2021 12:31:02'. It will be displayed as a datetime box on the query interface. Date Time box Note: When using this parameter in SQL statements, the system passes a datetime string, for example, "30/04/2024 13:10:00". You may need to convert the parameter value to a datetime type in the SQL statement. For example, in Oracle database, you would use the to_date function to convert the parameter value: SELECT * FROM   sales_list WHERE  sales_time = to_date('30/04/2024 13:10:00', 'dd/mm/yyyy hh24:mi:ss');
Date	Allow input of a date string containing year, month, and day, such as '01/31/2023'. It will be displayed as a date box on the query interface. Date box Note: When using this parameter in SQL statements, the system passes a date string, for example, "30/04/2024". You may need to convert the parameter value to a date type in the SQL statement. For example, in Oracle database, you would use the to_date function to convert the parameter value: SELECT * FROM   sales_list WHERE  sales_time = to_date('30/04/2024', 'dd/mm/yyyy');
Time	Allow input of a time string containing hour, minute, and second, such as '12:31:02'. It will be displayed as a time box on the query interface. Time box Note: When using this parameter in SQL statements, the system passes a time string, for example, "20:57:49". You may need to convert the parameter value to a time type in the SQL statement.
Dropdown List	Display a dropdown list on the query interface, you can select query parameter values from the list. Dropdown List box Using Query Parameters in Tasks

2.3 Dropdown List Parameter Config

You can use Dropdown List type query parameters in the Interactive Query Task. These parameters are displayed as an option menu on the query interface, where you can select the value for the query parameter. For example, you can choose the State to query from a menu similar to the one below:

Select a state to query from the dropdown list

Click the "Dropdown List Config" button to open the Dropdown List Config dialog box

Dropdown List Config dialog box

In this dialog box:

Dropdown List Type	Set the list type for the Dropdown List. Single selection: Only one option can be selected at once. The parameter passed to the task has only one value, for example, "Washington". Multiple selection: Multiple options can be selected at once. The query parameter passed to the task may have multiple values, separated by commas (,), for example, 'Alabama','Alaska','Arizona','California','Arkansas'.
List Source	Set the source of the option list. Static list: Set the selectable options for the list when configuring the task. Config a Static List for a Dropdown List Read from database: When running the task, use an SQL statement to dynamically retrieve the contents of the option list from the database. Config a Static List for a Dropdown List

Advanced Options

At least xx items must be selected	If the "Dropdown List Type" is "Multiple selection," you can set a minimum number of options that must be selected for each query here.
A maximum of xx items can be selected	If the "Dropdown List Type" is "Multiple selection," you can set a maximum number of options that can be selected for each query here.

2.4 Config a Static List for a Dropdown List

Click "Add Item" to open the "Option Item Config" dialog box.

Option Item Config dialog box

Item Value	The value of the option. This value will be passed as the query parameter value to the task's SQL statement.
A maximum of xx items can be selected	The text of the option. This value will be displayed as the option's text on the query interface.

2.5 Dynamically Retrieving Option Lists from a Database

You can store the option list in a database and use an SQL statement to retrieve the option list from the database when the task runs.

Set "List Source" to "Read from database", then click the "SQL Query Config" button

Select a data source and enter SQL query

After entering the SQL query and clicking the "OK" button, the system will execute the SQL statement you entered and retrieve the list of fields returned by the SQL query.

Then you set the SQL fields corresponding to the Item Value Field and Item Text Field in the list.

Item Value Field: The value of the SQL field corresponding to the "Item Value Field" will be passed as the value of the query parameter to the task's SQL statement.

Item Text Field: The value of the SQL field corresponding to the "Item Text Field" will be displayed as the text of the option on the interface. If a field for "Item Text Field" is not set, the system will use the value of the SQL field corresponding to "Item Value Field" as the option text.

Item Comment Field: Optional. You can set a SQL field corresponding to an "Item Comment Field" for the options. The value of this field will be displayed as comment information on the query interface.

Setting SQL Fields for options

Note: When the "List Source" is "Read from database", the task's execution mode must be "Multi-thread mode". Task Running Mode

2.6 Configuring Cascading Dropdown Menus

When the "List Source" of a Dropdown List parameter is set to "Read from database", you can reference the values of other query parameters in the SQL statement of the query parameter to achieve a cascaded option menu.

For example, consider the following list of query parameters:

Query parameters of a task

You can reference the value of the query parameter "State" in the SQL query for the query parameter "City" to display only the list of cities for the selected State in the City option list:

SELECT DISTINCT city
FROM   sales_stat
WHERE  state = #[state]# --Use the value of the query parameter "State" as the query condition.
ORDER  BY city 

Use the value of "State" as a query condition for the "City" option list

Display only the cities of the selected State in City List

Note: The order of the referenced query parameter must be before the parameter that references it. For example, in the above case, the order of State must be before City.

2.7 Using Query Parameters with the IN Keyword

When the SQL statement of the task contains the IN keyword and uses the value of a query parameter as the condition for the IN keyword, be sure not to use JDBC Variables in the IN keyword. Instead, use Regular Variable types.

SELECT *
FROM   t_stat
WHERE  area_name IN (#[CityNames]#) 

This SQL statement might produce incorrect results. The solution is to modify the syntax of the variable after the IN keyword:

SELECT *
FROM   t_stat
WHERE  area_name IN (#CityNames#) 

Considerations When Using Variables in IN Clause

2.8 Using Query Parameters in Tasks

The method of using query parameters is the same as using task variables. For example, the following statement is used to query sales reports within a specified time range for a selected city in a task:

SELECT *
FROM   sales_stat
WHERE  state = #[state]#
       AND city = #[city]#
       AND stat_month BETWEEN #[start_month]# AND #[end_month]#
ORDER  BY stat_month 

Using Variables in Tasks, Using Variables in SQL Statements