Query Builder
- Gints Vaskums
- Inese Auzina
- Mercedes Rothwell
Overview
To access the Query Builder, click the Platform tab (1), then click on Query Builder (2).
The Query Builder is an unstructured window that allows a user to run preloaded SQL (Structured Query Language) statements against the production database. This feature is typically used to produce ad hoc reports at very short notice (faster than having us build a report for you). To learn more about how to use SQL, you can visit sites such as W3 Schools for an overview and examples of SQL.
This window is not editable in the production database. To add queries, you must create them in the UAT environment and then ask us to promote them when you are ready to move them to production. This feature has the following advantages and limitations:
Query Builder is very fast. Running directly on the database, it has less overhead than on the app tier.
Query Builder allows data table joins not supported in the user interface. Our monitor windows can handle most data queries, but not all. Query Builder is safe, secure, and very simple to use. However, from time to time, users need columns that are not available on our monitor windows. We can add these columns quickly using SQL in this monitor.
There are no page size limitations on this window. Other windows are typically limited to 200 rows per page.
There is no security on this window: all data is accessible. This window is only appropriate for super users or admins in the back office.
Screen Elements
Query (1) - dropdown containing a list of existing queries or groups sorted by their names
Description (2) - brief description of what the query does
Group Name (3) - dropdown containing a list of groups sorted by their names (?)
Query Text (4) - the SQL code for the selected query.
FILTER (5) - action button for displaying results
RESULTS (6) - a grid displaying all records matching the selected query
Page size (7) - a number of items in each page (default value 100)
Total Count (8) - total number of found items
ADD (9) - action button for adding a new query
DELETE (10) - action button for deleting selected query
SAVE (11) - action button for saving recent changes
Working With the Queries
To access the Employee Manager, click the PLATFORM tab (1), then click on Query Builder (2).
Basic Actions
Opening a query
The Query dropdown contains a list of existing queries or groups sorted by their names. When you open the Query Builder, the system displays a list of existing queries by default. To open a query, select it from the list. The system displays the selected query description, group name, and SQL code.
Running a Query
You must have the authority to run a query. If you don't have the authority to do it, you need to ask somebody in your home office to grant you authority or to run it for you and give you the results. The authority to run queries is granted or revoked by the Security Manager.
To run a query, select it and click FILTER. The system displays results in a grid.
Note: Depending on the particular query, the system may ask to specify various query parameters. Enter the parameter and click OK.
Query Groups
All queries are organized into groups. The Group Name is a dropdown containing a list of groups sorted by their names.
How to Search for a Query by Group:
Double-click the Query dropdown
Select a group
Select a query.
How to Create a New Group:
Click the “+” button on the right of Query Group dropdown
Click ADD in the Query Type window
Enter the new query type name
Click OK
Click SAVE in the Query Type window.
How to Delete a Group:
Click the “+” button
Select a group from the list
Click DELETE in the Query Type window.
How to Reassign a Query:
Open the query in the Query Builder window
Select another group from the Group Name dropdown
Click SAVE.
How to Create a Query
Important: Adding queries is available only in the UAT environment.
Attention: Users cannot execute any query that updates data such as insert, update, or delete.
Additional Features
Unlike the other components, the Query Builder does not support paging (predefined page size and navigation between the pages using the Next and Back buttons). Specify the Page size (number of visible rows to preview) instead and click FILTER. There's no limit to the page size. To see how many records are populated, click the Total Count button.
To export or print the results, right-click within the results table.
Select Export to export the highlighted record
Select Export All to export all results
Select Print to send the grid to the printer.