Follow-On Topics

Related Topics

# About the Query Tab

Click the image to view full size.

The Query tab is used to configure all settings for data collection from the OLEDB data source. Specific columns can be selected for inclusion in the recordset and various features used to filter the results.

The dialog box consists of the following:

About the Toolbar

The Query Tab toolbar displays a number of icons which provide shortcuts to various functions as detailed in the table below:

Icon | Description
--- | ---
| Displays Tables & Joins in the Design Tab.
| Displays Columns in the Design Tab.
| Displays Grouping in the Design Tab.
| Displays Criteria in the Design Tab.
| Displays Having in the Design Tab.
| Displays Sorting in the Design Tab.
| Reverts to SQL Free Type Mode if you wish to enter the script directly. Once selected, this mode must be used to edit the query.
| Runs the query and displays the results in the Results Tab. This function is also available when the General and Results tabs are selected and is a great feature for testing the query.
| Cancels the execution of the query.
| Displays the Predicates dialog box to filter the query results.

OLEDB Schema

The left side of the screen contains a pane displaying available data tables, columns and views in a tree format. The data exposed depends on the database being used. NOTE: The UDT (User define data) type available through SQL Server 2008 is NOT currently supported. Columns may be selected and then dragged and dropped into various panes on the right of the dialog box.
|  | For example, a column called `NAME` contained in the `SALES_ORDER` table is dragged into the Columns pane.
|  | The Tables and Joins icon in the Toolbar is then selected to display the Tables & Joins pane which has been automatically populated with the `SALES_ORDER` table.
|  | If a column from another table was added to the Columns pane, then its table would also be automatically added into the Tables & Joins pane.
In addition to the tables and columns described above, the tree also contains an item called Formulas which are used to create new "columns" based on those available.
|  | Right-clicking on the (Formulas) item provides access to the Formula Editor dialog box through which a new Formula may be created.

|  | An example of using a Formula could be where it is required to display the value of an item in a different currency. A formula is created to multiply the Unit Price column by an applicable fixed number to convert the value.

|  | The formula is then dragged and dropped into the Columns pane to be included in the query recordset.

About the Design Tab

The Design tab is displayed by default on the right side of the dialog box when the Query tab is first selected. It contains a number of panes listed below which are selectable from the Toolbar:
|  | Tables & Joins
The Tables & Joins pane displays the tables within the query and the relationships (joins) between them.
|  | Columns
The Columns pane is used to specify the columns that are returned in the query. The Columns available are displayed on the left of the dialog in the OLEDB Schema. Formula Columns can also be created on the fly to provide manipulation of data whether for formatting or calculations.
|  | Formula Columns
| Click the image to view full size.

Formula Columns enable the user to return information in the Query that is not available in the database. The feature can be used to transform Columns that exist in the database by performing data type conversions, formatting or calculations of almost limitless complexity.

|  | Grouping

The Grouping pane is used to create a query that returns consolidated data and is commonly used in conjunction with aggregation functions such as `SUM` or `COUNT`.

By selecting certain columns within the Grouping pane the data is consolidated so that no duplicates exist across the fields returned. In turn, the aggregate functions enable information such as totals or averages to be calculated across the records that are consolidated. The pane corresponds directly with the `GROUP BY` clause in the SQL syntax.

|  | Criteria

The Criteria pane is used to restrict the rows that are returned by the query. The Criteria defined in this pane are applied before Grouping, if used. Grouping is the graphic equivalent of enclosing condition in parenthesis to impose the order in which they are applied.

Multiple Criteria can be added within the Criteria pane and the criteria can be nested using drag & drop to ensure precedence when using a combination of AND and OR operators.

|  | Having

The Having pane has exactly the same features as the Criteria pane. Columns are added in the same way and criteria selected for filtering is identical.

The difference between them is the order in which they are processed when the query is run. The Having filter is always applied after the Grouping filter.

A simple example would be where a company has offices in different locations.

1. Columns are added in the Columns area to provide order and invoice information for all offices.
2. A Criteria filter is applied to only include orders received for the current month.
3. A Grouping filter is then applied to group all rows by office location.
4. Finally the Having filter is applied to only include orders for which an invoice has been raised.

|  | Sorting

Columns may be added to the Sorting pane to set the order in which the query results are displayed.

When a column is added it contains an arrow on the left side which indicates whether the sorting order is ascending (pointing upwards) or descending (pointing downwards). Double-clicking on the arrow toggles it between up and down. The default sorting order when the column is first added is ascending.

As further columns are added they are displayed to the right of the last column entered and this is the order in which they are implemented. Columns may be re-ordered by dragging and dropping them to the left or right of another column.

About the SQL Tab

The SQL Tab may be selected at any time to display the SQL script that is being created in the background for the Step.

About the Request Tab

The Request tab is only displayed if a non-SQL OLEDB provider is selected.

The tool automatically detects whether it supports a different query language or no query language at all and then either allows the user to write a free type query if a query language is supported or to enter a table name if no query language is supported.

Copyright © 2018 Quadrant Software B.V. All rights reserved.