SQL Query Building
 |
CASE SUMMARY
This chapter shows the process of building SQL queries in the SQL editor of PANTHEON.
-
Selecting tables (SQL Objects)
-
Joining tables
-
Selecting columns
-
Defining additional settings
-
Displaying queries
-
Running queries and displaying results
-
Confirming and returning 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 instead of writing SQL queries manually. The tool can be accessed by clicking the 
SQL Query Builder button in SQL Editor.
1. Selecting tables (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 wants to have in an individual query.
When searching for SQL Objects, the person responsible can use the search field, located above the objects list. For an advanced search, the person responsible uses the 
(magnifier button), located on the right side of the search field. To check the properties of SQL Objects, the person responsible clicks the 
(plus) 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 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. Selecting columns
If the person responsible wants to limit the column selection in the query results (because the person responsible does not want to have all data displayed), the person responsible simply select the columns by checking the checkbox in front of the selected columns.
4. Defining additional settings
Under the selected table display panel, there is a panel with additional settings, such as custom column naming (see Alias in the image), the type and sorting order (Sort Type and Sort Order), calculation operations (Aggregate), grouping (Grouping), etc.
5. Displaying queries
The query is being built along automatically, and it is displayed in the panel at the very bottom.
6. Running queries and displaying results
The person responsible starts the query by pressing the F5 function key on the keyboard, or by clicking the button in the image below.
The result is displayed in the panel at the bottom of the window.
7. Confirming and returning 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, the person responsible clicks the OK (checkmark) button, as seen in the image below, to return to the original form, which can be Ad-hoc analyses, Dashboard Components, etc. The query is then transferred to the original form.
If the person responsible wants to further analyze the results of the built query, the person responsible clicks the Ad-hoc analyses button, as seen in the image below. The query will be transferred, and opened on the Ad-Hoc Analyses form, where the person responsible can create an Ad-Hoc Analysis.
9. Classic SQL Editor display
When the person responsible is using the tool for building a query and wants to see the query in the classic SQL Editor without using the tools for query building and without running the query, the person responsible uses the button, as seen in the image below.
A query in the classic SQL Editor will open.