Query Dialog

This dialog allows you to create or edit complex queries. The queries can contain Filter, Joins and Aggregation.

To create a new query, proceed as follows:

Add Table

Choose Add Tables and then select from the existing external and internal tables those whose data you want to include in the query. Results from other queries can also be used.

  • To select a table or query, double-click the corresponding row. When all tables or queries are selected, click Close.

Add Column

After you have inserted tables, you can include columns from these tables in the query result. Even if you want to filter or group by a column, you must add that column.

  • To do this, double-click on the individual fields or on Add column. The selected columns now appear in the lower part of the dialog.

Edit columns

All inserted columns are displayed in the lower part of the dialog. You can define the following properties for the columns:

  • Table: The table from which this column originates
  • Column: The column from this table that is to be included in the result.
  • Caption: You can assign a new name to the result column. If you do not specify anything here, EasyMap automatically creates a generic name.
  • Function: This property is only displayed if you have previously activated Data Aggregation.
  • Display: Here you can specify whether the column should be included in the result table. If the column is only used for filtering or grouping, it may be useful to remove the check mark.
  • Criteria: Here you can specify Criteria for filtering data.

Note: Filtering takes place before aggregation (a WHERE section in SQL, as opposed to the HAVING section, which is not supported in EasyMap queries). When entering the filter criterion, a special syntax must be used. Several lines are available for criteria. These are evaluated as alternative filter criteria (OR link), i.e. the data record is contained if one of the criteria applies.

Add Calculated Column

In the query wizard you have the possibility to calculate columns. The functionality of the calculation mode is explained here.

Create Joins

If you use several tables, you must define how these tables are to be linked together in the result. If you do not specify anything here, you get the Cartesian product of both tables (full join), which is usually not the intention.

  • Create a join by clicking with the mouse on the field of one table and then dragging it to a field of the other table with the mouse button held down. These two columns are joined, that is, only those data records are combined in which the contents of both fields are the same.
  • The created join is displayed as a line between the two tables.

Editing Joins

  • Double-click the line that displays the join or click the line and then click the Edit join button.
  • The Connection Settings dialog opens where you can specify the settings in detail.

Data aggregation

If you want to summarize data records, you must activate data aggregation.

  • After clicking on the sum character Σ, you can apply aggregation functions to the columns. The group function is used by default.
  • As soon as an aggregation function is selected for at least one column, an aggregation function must also be selected for every other data column.

Aggregation Types

Grouped: All data records with the same values are combined in one result data record. If several columns are grouped together, each combination of these values occurs only once in the result.

Not grouped: In order to calculate a result from the many data, one of the available aggregation functions is used.

Filtering data

Use the criteria line to specify a data filter. This must be specified in a special syntax.

  • With criteria you can use selection criteria for each column. The query result then only contains the data records for which all affected data columns meet the selection criteria.
  • You can also combine several filter expressions. Criteria that are entered in the same line are linked with and. Criteria that are entered in different rows are linked with or.
  • If several criteria are used in a cell, it is necessary to specify the column name in the filter expression.

Save preview and query

  • After a click on Preview EasyMap shows a preview of the query result. If this does not meet your expectations, you can change the query again.
  • After a click on OK you will be prompted to give the query a name. The query is saved under this name. At the same time, a table view of the query result is opened.