Overview
The Query Designer is used to modify query-based dataviews. The query designer presents a series of notebook tabs; each tab represents a different part of the query. The last notebook tab in the Query Designer shows the generated SQL and allows the name of the dataview and data pipeline to be changed. The Query Designer is pictured below.



Query Designer: Adding Search Criteria
You can use the Query Designer to add or remove search criteria from your query. Perform these steps in order to add search criteria:

1 Click on the Search icon of the dataview to launch the Query Designer.


2 From the list of fields at the top of the search page, double-click on the field for which criteria needs to be entered.


3 Click on thefield that hasbeenadded to the list of criteria at the bottom and select the operator.


4 Click in the edit box and enter the search criteria value. This criteria will find all company names that begin with the letter 'S'.


5 Click on the SQL tab to make sure the criteria value is valid.


6 Close the Query Designer and click on the preview icon.


7 Preview the data and make sure that the intended records are selected.


Create a Group Sum
The SQL 'GROUP BY' clause allows you to eliminate rows in your query where the field values repeat. For example, let's assume we have a database table that contains order records. Each order record has the customer number and the amount paid. If we viewed the data in this table, we would see that the value in the customer number field repeats where there are multiple orders for a customer.

We can use SQL to select data from the orders table and calculate the total amount paid for each customer. We can do this by specifying a group on the customer number field. By specifying the group, we are saying to the SQL engine: create one row in the result set for each customer number found. When the SQL engine runs the query, it will find multiple records for some customers; these records will be eliminated from the result set. SQL allows us to perform calculations on these repeated records and store the result in a new field of the result set.

These types of calculations can be created on the Calc tab of the Query Designer. Perform these steps in order to sum the amount paid for all customers in the orders table:

1 Click the 'Calc' icon to launch the Query Designer.


2 Double-click the 'Amount Paid' field from the selection list at the top of the page. Amount Paid will be added to the list of calculations.


3 Select 'Sum' as the function type for the calculation.


4 Enter the Field Alias you would like to use for this calculated field.


5 Click the SQL tab to make sure the generated SQL is valid.


6 Close the Query Designer and click the Preview icon to preview the data.


7 Check the data to make sure the sum is calculated as expected.


Concatenate Fields
You can enter SQL expressions from the Calc tab of the Query Designer. The following query selects data from a table of employees. The table has a first name and last name field. Perform these steps in order to concatenate these two fields together using the Query Designer:

1 Click the 'Calcs' icon to launch the Query Designer.


2 Double-click the 'First Name' field from the selection list at the top of the page. 'First Name' will be added to the list of calculations.


3 Select 'Expression' as the function type for the calculation.


4 Modify the widths of the 'Field SQL Alias' and 'Table SQL Alias' and 'Expression' columns in the calculations list at the bottom of the page so that there is enough space to enter the expression. The figure below illustrates a sample expression.


5 Enter the Field Alias you would like to use for this calculated field.


6 Click the SQL tab to make sure the generated SQL is valid.


7 Close the Query Designer and click the Preview icon to preview the data.


8 Check the data to make sure the field is calculated as expected.


Edit SQL
There may be times when you need to utilize advanced features of SQL that cannot be accessed via the visual interface of the Query Designer. In these cases, you can edit the SQL manually in the Query Designer. Once you have edited the SQL manually, you must always use the SQL tab of the Query Designer to make future modifications.

Perform these steps in order to edit the SQL generated by the Query Designer:

1 Click on the SQL icon to launch the Query Designer.


2 Right-click over the SQL text to display the popup-menu.


3 Select the menu item. Click Yes to the message dialog. You can now edit the SQL.