Query Builder
- 4 minutes to read
The Query Builder is a visual query editor. You can use it to add data tables and views to the data source, and select which columns to include.
This topic consists of the following sections.
Add Tables
Drag a specific table or view to the Query Builder design surface pane to include it in a query:
Then select the columns to include in the query:
The Query Builder provides a toolbar with the following commands:
| Icon | Description |
|---|---|
 |
Removes the selected table or view from the query. |
 |
Reverses the most recent action. |
 |
Restores the previously undone action. |
Join Tables
The Query Builder allows you to join tables and/or views. Use drag and drop to connect corresponding columns (key fields). A relationship line is drawn between two connected tables / views. Note the connected columns should have identical data types. The Query Builder automatically joins a table or view that has a relationship at the database level with any recently added tables.
The Query Builder allows you to change the join type (if necessary). Click a relationship line to display the Relation Properties section. Properties in this section define the join type (Inner or Left Outer) and applied logical operator.
A left outer join returns all values from an inner join along with all values in the “left” table that do not match the “right” table. The result also includes rows with NULL (empty) values in the key field. If you select the left outer join, the relationship line displays an arrow which points to the “right” table of the join clause. The executed query returns a “flat” table that joins different tables within a single query.
Select the relationship line and click Delete (the icon) to delete an unnecessary relationship.
Edit Column Settings
Select a table or view, and click a data column to display the data column options.
The Column Properties section contains the following options:
| Option | Description |
|---|---|
| Name | Indicates the column name that the Query Builder obtains from the database. |
| Type | Indicates the column’s data type. The Query Builder provides information about the maximum string length for string columns. |
| Alias | Specifies a custom column name (alias). Include a column into a query to enable this option. Aggregated columns should always have an alias. |
| Output | Specifies whether to include a column in a query result. |
| Sort Type | Specifies whether to maintain the initial sort order for a column, or sort data records in ascending or descending order. |
| Sort Order | Defines the sorting priority for multiple columns (the less this number is, the higher the priority). |
| Group By | Specifies whether to group a query result by this column. |
| Aggregate | Specifies whether to aggregate the column’s data records. You can use the following aggregate functions: Count, Max, Min, Avg, Sum, CountDistinct, AvgDistinct, SumDistinct. The Query Builder omits individual data records from the query result and only retains the aggregate function result when you apply any of these functions. |
Use Query Parameters
Use the Parameters section to add, remove and edit query parameters.
Each query parameter provides the following properties:
| Option | Description |
|---|---|
| Name | Specifies the query parameter’s name. |
| Type | Specifies the parameter value’s data type. Set this property to Expression to generate parameter values dynamically. |
| Result Type | Specifies the data type of the expression’s result value. This property is enabled if the query parameter type is Expression. |
| Value | Determines the query parameter’s actual value. You can specify a static value according to the selected value’s data type. Alternatively, construct an expression to generate parameter values dynamically. Click this property’s ellipsis button to invoke the Expression Editor and create an expression. This ellipsis button is enabled if you set the query parameter type to Expression. |
Filter Data
The Query Builder can be used to filter a query. To do this, deselect tables and click the ellipsis button in the invoked Filter field in the Properties pane. This invokes the Filter Editor dialog, which provides a visual interface for constructing a filter string.
See Filter Queries for details.
Preview Data
Click the Preview Results button to test a query on the actual data’s limited subset.
The invoked Data Preview dialog displays the first 100 data records of the query:
