Query of Views

From the “Query” tab, you can query the view.

The page is divided into two sections:

To display all the data of the view, select some output columns and click on the Execute button. You can also add conditions to filter the data obtained from the view.

Query Definition

It is the graphical way of building a query. It is divided in two parts:

Query definition

Query definition

  • The left side (red) contains the fields to work with:

    • Fields: contains the list of all the fields of the view (they can be sorted alphabetically and/or filtered). You can multi-select some fields and drag-and-drop them to the right side. In addition to the fields of the view, a special field called NUMBER_OF_RECORDS is available as a shortcut for adding a count(*) field to the output.

      Query definition - Fields

      Query definition - Fields

    • Relationship Fields: contains the list of fields that are used in the condition mapping of an association with cardinality 1. In this case, you can select which fields of the associated view you want to add to the result.

      For example, if you are querying the view “city” and you want to see the data of the view “country” and there is an association between city and “country” with cardinality 1 in the end point of the “country” view, you can select the fields of the “country” view you want to add to the result. You can also order the results by these fields.

      Query definition - Relationship fields

      Query definition - Relationship fields

  • The right side (green) is the working area, where you add the fields from the left side and edit them to configure the final query.

    • Output columns: shows the list of fields that will conform the output schema of the query and in which order they appear. You change the order by drag-and-drop a field to the position you want. After adding a field to this section, you can edit in (contextual menu), to change its name or to create an expression.

      Click Add new field to add a new field to the output. The tool will automatically detect whether it is a derived or an aggregation field.

      • If it is an aggregation field, a GROUP BY expression is automatically added to the query.

      Note

      Because the syntax for the expanded fields is <role_name>/<field>, the / cannot be used as an operator. The function DIV has to be used instead.

      Query definition - Output columns

      Query definition - Output columns

    • Filters: complex conditions to filter the data of the view. If the view contains mandatory fields, the wizard will automatically create a filter for each one. If they are deleted, another filter or expression must be created to give value to that field. The user would only have to complete their expressions.

      You can use date, time and timestamp literals in your queries. If the selected field is of date type, an example value with the appropriate format (taking into account the subtype of the field) is shown.

      Query definition - Add a filter on a timestamp field

      Query definition - Add a filter on a timestamp field

    • Order by: order the results by one or more fields. You can drag-and-drop fields from the list of “Fields” or from the “Outputs”. Once a field is added to this section, you can change its order mode (ascendant/descendant) by clicking on the arrow.

      Query Definition - Order by

      Query definition - Order by

Note

You can use expanded fields (of associations with cardinality 1) in the following places of the query:

  • Output columns

  • Filters

  • Order by

  • GROUP BY clause (automatically generated)

The expanded fields are used in the same way as the fields of the view being queried and will be shown with the syntax <role_name>/<field>.

After building the query, click on the Execute button to execute it. At any time, you can stop the query with the Cancel button.

Use the **Cancel** button to stop the query

Use the Cancel button to stop the query

In these wizards, the expression editor provides the autocomplete functionality and highlights the different elements that make up the expression with different colors (function names, fields, literals, operators, etc.). Besides, the fragments not identified as any of the possible element types, will be highlighted in red.

Use the Save button to save the query executed to obtain these results. It is associated to the current user and server and will be shown in the My Queries section. This is useful if the executed query is complex and you are going to execute it often or if you want to deploy it to the Virtual DataPort server.

You can obtain the VQL that will be generated for the current query by clicking the VQL button.

Finally, you can export the results to a file by clicking the Export button. You can select the following:

  • The output format: CSV, HTML, Excel or Tableau. The page Enabling Exports to Tableau explains how to enable exports to Tableau. The administrator can disable these options (see Export Format Configuration).

  • The number of exported results:

    • All results.

    • Only visible results. I.e. the part of the results you are currently seeing.

    • Only the first n results.

  • Choose if you want the output file to include a header with column names. The files for Tableau (.tde) and Tableau Hyper (.hyper) always include this header.

    The administrator, in the Personalization page, can change the default option.

Export the results of a query

Export the results of a query

Note

If the view contains compound fields or expanded associations “to Many” (see section Expanding Associations for more information), those fields are going to be exported as a JSON string.

Note

When exporting to Excel the maximum number of results that will be exported is 1.048.576

Query Results

Regarding the results, there are two special cases:

  • Compound fields: click the icon to see the value of its subfields on a pop-up.

  • Binary fields are shown as “[BINARY DATA]” and cannot be downloaded.

If the queried view has associations with other views. These associations can be of 2 types:

  • “To One”: The user can select the fields of the associated view will be shown as fields of the results query.

  • “To Many”: When one tuple of the consulted view is related to more than one tuple of the associated view. In this case you can go through that association and see all the tuples associated to this tuple of the main view. You can browse them by clicking link. A new pop-up will be opened with the data at the other side of the associations. It is also possible to expand associations (see section Expanding Associations for more information).

Query results with navigable "to-many" associations

Query results with navigable “to-many” associations

In each cell of the table, if the content is too long, it will not increase the height of the row, but a tool-tip will be used to show the rest of the content and the height will be kept uniform for all the rows.

The first column of each row contains a full-row icon that shows in a dialog the data from that row in a vertical format (it can be helpful when a table has too many columns, because it is difficult to see the results of a complete row).

Detailed view of a row in the query results

Detailed view of a row in the query results

Expanding Associations

Expanding an association between two entities means that related tuples/entities are included inline in the results. For instance, suppose a scenario with entities Order and Customer, as shown in Expanding an association (“to One”). Each tuple in Order is associated with a tuple in Customer (thanks to PK-FK relationship). This way, when querying an order, you can eagerly retrieve the data from the customer that ordered it. The same way, when querying a customer, you can retrieve all his/her orders.

Expanding an association (“to One”)

Expanding an association (“to One”)

Following with this example, you can have two scenarios:

  1. Associations “to One”: like in the former example, where an order belongs to a customer (and only to that customer). This way you can select the fields from the associated view (Customer) you want to eagerly retrieve when retrieving the data from the order. To do that, you have to select the fields you want to expand and they will appear in the result.

    Expanding fields from a “to One” association

    Expanding fields from a “to One” association

    Association “to One” expanded in the query results

    Association “to One” expanded in the query results

  1. Associations “to Many”: a tuple in a view is related to one or more tuples in other view. It is the case of a customer that may have done several orders. This way, you can traverse the association and see all orders done by a particular customer. To do that, you have to select the fields you want to expand and an icon to expand the association will be added to the table. You can click on the icon link and a new pop-up will be displayed with the orders for the desired customer.

    Expanding fields from a “to Many” association

    Expanding fields from a “to Many” association

    Association “to Many” expanded in the query results

    Association “to Many” expanded in the query results