The requested page is not available for the requested platform. You are viewing the content for Default platform.

Query Builder

  • 4 min 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:

wdd-query-builder-add-table

Then select the columns to include in the query:

wdd-query-builder-select-column

The Query Builder provides a toolbar with the following commands:

Icon Description
wdd-query-builder-delete-icon Removes the selected table or view from the query.
wdd-query-builder-undo Reverses the most recent action.
wdd-query-builder-redo 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.

wdd-query-builder-join-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.

wdd-query-builder-relationship-properties

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 wdd-query-builder-delete-icon 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.

wdd-query-builder-properties-pane

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.

wdd-filter-editor-filter-query

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:

wdd-preview-result