Query Designer Dialog Box

Relevant for: API testing only

This dialog box helps you design an SQL statement for:

  • the Query string property of the Database > Select Data activity.

  • creating a new database data source in the Data Pane.

To access

In the Input/Checkpoints tab, for a Select Data step:

  1. Select the Input property—Query string.

  2. Click the Build an SQL query string button in the right side of the Value column to open the Query Builder Dialog Box.

  3. Click the Query Designer button .

In the Data Pane, when defining a new Database type data source using the Add New Database Data Source Wizard:

  1. In the Add New Database Data Source wizard, proceed to the second screen, Set SQL Statement.

  2. Click the Query Designer button to the right of the SQL statement box.
Relevant tasks

Add a database data source

User interface elements are described below (unlabeled elements are shown in angle brackets):

UI Elements

Description
Table/View

A drop-down list of the tables and views in the database. The tables and views are distinguishable by different icons.

The number of items shown in the list is limited to 1000. You can also enter text in the field to display a list of relevant data sources.

Reset Query. Resets the query to its original state and discards all changes.
Group by

Adds a GroupBy column to the grid, allowing you to select a grouping method such as: Sum, Avg, Min, Max, Count. It adds GROUP BY to the SQL statement. If you select a GroupBy criteria, it also adds it to the SQL expression.

Distinct

Adds a DISTINCT term to the SQL statement, instructing the query to retrieve only distinct record sets.
<table/view columns grid>

A list of the columns in the selected table or view.

The grid contains the following columns:

  • Column. The name of the column in the table or view.

  • Alias. An alias name for the column, if applicable.

  • Output. When enabled, it includes the column in the SELECT statement.

  • Sort. Enables you to sort the results in ascending or descending order.

  • Sort Order. When working with multiple sorted columns, this indicates the sorting order beginning with 1.

  • Filter. Enables you to filter the results of the query, by adding a WHERE <column_name> = <filter text> to the SQL statement.
<SQL statement preview pane> (bottom left)

A preview of the SQL statement generated by your selections.
<returned rows pane> (bottom right)

The rows returned by the query.

Tip: If you enable Auto Execute, these rows display the current query results.
Auto Execute

Automatically runs the SQL statement in the Preview pane whenever you make a change in the upper panes of the dialog box.

Tip: If you notice that the query takes a long time to run, you can turn off this option and click the Execute query button to manually run it.

Execute query. Runs the query shown in the Preview pane. Use this when Auto Execute is turned off.

Tip: If the Returned rows pane is grayed out, this indicates that the results are not current.