Who is this article for?

SQL & Dataset Administrator to execute select queries

Security Warning! Query Builder allows access to all application data regardless of reporting authority, role, and record access control restrictions.

The Query Builder offers a convenient method for users to interact with the back-end database by utilizing Structured Query Language (SQL) queries. It is important to note that the functionality of the Query Builder is specifically limited to executing SELECT queries. This means that users can retrieve data from the database, but they do not have the ability to modify or alter any data through this tool. Such a design ensures that the integrity of the database is maintained while still allowing users to access the information they need.

This useful shortcut is exclusively available to individuals who hold the SQL & Dataset Administrator role. By restricting access to this feature, it ensures that only those with the appropriate permissions can perform queries, thereby safeguarding the database from unintended changes and maintaining a controlled environment for data retrieval.

SQL region

The SQL region allows a SQL statement to be entered. The SQL region has the following buttons:

• Execute: Runs the entered SQL statement and displays results on screen.
• Execute into Excel: Runs the entered SQL statement and saves results to an Excel file.
• Create Dataset: Used to create a Dataset, this option should only be used when instructed to do so by a Technical Account Manager.
• Refeed Search: Runs the entered SQL statement and refreshes all matching objects in the platform's in-memory search engine. The SQL statement must contain the column “ObjectID” (Case sensitive) in the results.
• Refeed Dirty Objects: Runs the entered SQL statement and feeds returned objects into the dirty objects (DO) background processing queue. The DO queue saves each object in the background. The SQL statement must contain the column “ObjectID” (Case sensitive) in the results. 

View region

System auto-generated views mimic the physical tables of the platform; for example, vPrefix_Team is a view of the platform team's table. Module views mimic all the fields at the header and child levels of a module. 

System View of Platform Information
Module views do not contain an underscore (_). Views with an underscore (_) in the view name are system views showing platform table information. 

For example, vPrefix_Attachments would show all attachments in the system across all objects.

Once a view is selected, the available fields (columns) will be listed in the column's region.

Select a data source from the Source dropdown list. The “Data Depot” source mirrors the production database and should be used when available so that the performance of the system is not degraded by searches.

SQL queries can be saved for future re-use using the Save-As feature