Skip to main content

Aggregate Query

Creating Aggregate Queries​

Design mode is the only mode available for aggregation queries. The design mode presents a visual abstraction of tables and joins. When you work with a query in design mode, the right side of the query editor lists the DPL queries defined in the current package. The query editor's other panes reflect your work as you build the query and will be blank when you first begin to create your aggregate query.

The content pane (top left side of the editor) displays the elements of your query as you build it.

The native language pane (center-right) displays the query in pseudo-SQL. This is used only for reference and is never submitted to any connector.

The preview pane (lower-right) displays a selection of the data being returned by your query.

To create an aggregate query​

  1. Before creating an aggregate query, you must already have created at least two other queries (standard, aggregate, or transform) within your DPL Studio package

Right-click the Queries folder in the Data pane (bottom left pane) and select New Aggregate Query from the shortcut menu. -OR-

Right-click a blank space in the background of the document area and select New Aggregate Query.

  1. Select the tables and views that contain the desired data by clicking and dragging the desired table or view to the content pane. If you do not see the tables or columns you want, the metadata may have gone out of date. DPL Studio works with cached table and column metadata to enhance performance by reducing extraneous queries to the database.

To check the age of the metadata, hover the mouse over a table in the Tables pane.

To refresh table metadata, use the Refresh Tables tool on the toolbar

To refresh column metadata, right-click on a table in the Tables pane and select Refresh Columns. If the table is already being used, it will not update automatically to prevent loss of the current query. If you wish to use the new column metadata, close the table and drag it over again.

Note: If a table contains Phantom in the title and has a gold title bar, the table no longer exists due to a change in the connection or the underlying database. If you are certain the query is correct, phantom tables can still be used and joined across. If you delete a phantom table, however, it cannot be retrieved again.

  1. If you are using multiple tables, joins can be created by dragging a column from one table onto a column of another table, automatically creating an inner join where the two columns are equal.

To change the join type or condition, right-click on the join line and select the type and condition from the shortcut menu.

To change the join order, the relationship between joins can be set from the same shortcut menu. Use the query in the native language pane to determine what the join order is.

  1. In case that the standard queries used in an aggregate query contains parameter(s). The parameter will be passed on automatically. However, this will have implication on additional queries using this aggregate query that the parameter must be specified with an absolute query and column name, for example, DataFormatted. parameter.

  2. Select the desired fields/columns from the table, using the small window that appears when the table is positioned in the content pane.

To include all values from a particular field in your query, click the check-box to the left of the field name.

To include all values of all fields, click the * in the lower left corner.

  1. When the selections are complete, save the query: from the File menu, select Save [query name] where [query name] is the name of the current query.