Query Builder
- 09 Jul 2024
- 2 Minutes to read
- Print
- PDF
Query Builder
- Updated on 09 Jul 2024
- 2 Minutes to read
- Print
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
A visual query editor is called the Query Builder. It allows you to choose which columns to include and add data tables and views to the SQL data sources. You can write a federated query for federated data sources.
Figure: Query Builder
Add Table
To incorporate a particular table or view into a query, drag and drop it into the Query Builder design surface pane:
Figure: View
Select the individual columns to be included in the query or select all columns* to include all the columns in the query.
Figure: Select columns
Edit Column Settings
Select a table or view and click a data column to display the data column options.
Figure: Edit Column
The following table explains the column properties.
| Field | Description |
Name | Specifies the column name that the Query Builder picks from the database. |
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. |
| Type | Specifies the column's data type. The Query Builder provides information about the maximum string length for string columns. |
| Sort Type | Specifies whether to maintain the initial sort order for a column or sort data records in ascending or descending order. |
| Sort Order | Specifies 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. |
Figure: OutputParameters
Click Parameters to add a new parameter. Click the plus + icon to add the parameter.
Figure: Add parameter
- 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.
- Value - Specifies the actual value of the query parameter.
Preview Results
Click Preview Results to preview the query on the actual data. The preview window displays the first 100 data records of the query.
Figure: Preview Results
Join Tables
In Query Builder, you can join two tables and/or views. Drag and drop a connecting line between the columns.
The connected columns should have identical data types. The Query Builder automatically joins a table or view with a database-level relationship with any recently added tables.
Figure: Join tables and views
If required, you can modify the join type using the Query Builder. To view the Relation Properties section, click on the relationship line. This section's properties specify the applied logical operator and the join type (Inner or Left Outer).
Figure: Relation properties
A left outer join returns all values from an inner join and 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 that points to the "right" table of the join clause. The executed query returns a "flat" table that joins different tables within a single query.
Was this article helpful?