SQL Query Building

CASE SUMMARY This chapter shows the process of SQL query building in the SQL editor of PANTHEON. Table selection (of the SQL Objects) Table joining Column selection Additional settings Query display Query run and results display Confirmation and return to the original form* Further query analysis in Ad-hoc analyses Classic SQL Editor display

SQL query building

A tool for SQL Query building can be used in place of writing SQL queries manually. The tool is recalled by clicking the button in SQL Editor.

1. Table selection (of the SQL Objects)

The person responsible positions the cursor on SQL Objects in the menu on the right side of the window, selects the desired table (or view) and transfers it to the upper central panel, using the drag-and-drop method. The process is repeated for all tables that the person responsible wishes to have in an individual query.

When looking for SQL Objects, the person responsible can use the search field, located above the objects list. For an advanced search, the person responsible uses (povečevalno steklo), located on the right side of the search field. To review the properties of SQL Objects, the person responsible clicks the button in front of the selected object.

* – a more detailed description of SQL Objects can be found at https://developer.datalab.eu, under Database info / DB Info P55.

2. Joining the tables

If the person responsible would like to have a query that uses fields from several different tables (or views), the tables need to be joined together. Two tables are joined through a column, normally the key, which is located in both tables. The tables are joined by transferring the column from one table to the corresponding column in the other table, using the drag-and-drop method. In some cases, the tables are joined automatically, and there is no need for manual joining.

3. Column selection

If the person responsible wants to limit the column selection in the query results (they are not interested in everything), they simply select the columns by checking the checkbox in front of the selected columns.

4. Additional settings

Under the selected table display panel, there is a panel with additional settings, such as custom column naming (see Alias in the picture), the type and sorting order (Sort Type and Sort Order), calculation operations (Aggregate), grouping (Grouping), etc.

5. Query display

The query is being built along automatically, and it is displayed in the panel at the very bottom.

6. Query run and results display

The person responsible starts the query by clicking the F5 button on the keyboard, or the button in the picture below.

The result is displayed in the panel at the bottom of the window.

7. Confirmation and return to the original form*

*– only when the editor has been recalled from Ad-hoc analyses, Dashboard Components or another form . Once the person responsible is satisfied with the built query, they click the OK button (see picture below) to return to the original form, which can be Ad-hoc analyses, Dashboard Components, etc.). The query is transferred to the original form.

8. Further query analysis in Ad-hoc analyses

If the person responsible wants to further analyse the results of the built query, they click the Ad-hoc analyses button (see picture below). The query will be transferred to, and opened in the Ad-hoc analyses form, where the person responsible continues with the analysis.

9. Classic SQL Editor display

When the person responsible is using the tool for building a query and they want to see the query in the classic SQL Editor (without the tools for query building) without running it, they use the button, as seen in the picture below.

A query in the classic SQL Editor will open.