SQL Query Utilities>SQL Query

Top  Previous  Next

Overview

 

 Advanced Users Only 

 

SQL Query applet allows you to write, review and run SQL Queries against InFocus (default), or other external data source.

 

In summary, queries are written in the editor using additional tools available from the menu and toolbar. Results are reviewed below the query editor.

 

SQL Queries can be written ad hoc or for use in other applets such as Dashboard Queries Manager, Custom Reports, etc.

 

Query Builder makes building SQL Queries accessible to users who are unfamiliar with writing queries in SQL syntax.

 

Data & View Rights

 

SQL Query applet does not inherit view rights to sensitive information (e.g. Can view Pay Rate set in HR>Employees>Account & Rates tab), nor does it inherit Project viewing rights (e.g. Project filtering based on Employee Job Types or Project Roles). Results displayed reflect the raw output of the query.

 

Please note: SQL Queries can directly affect the InFocus database. Use caution.

 


 

Tutorial

 

Below is a brief tutorial for working with SQL Query applet.

 

1.Browse to UT>SQL Query
2.Select a data source (defaults to- and is typically- InFocus)
3.Using the editor, write the query
oOptionally load the query from a .SQL File by clicking Open File from the toolbar.
4.Once the query is complete, click Run Query. Please note: SQL Queries directly affect the InFocus database. Use caution.
5.Review the Results

 

Once satisfied with the Results, they can by exported to excel, word, .pdf, etc. The query itself can be copied for use in other InFocus applets such as:

 

Dashboard Queries Manager - Used for building Classic Dashboard widgets and Analytic Models
Custom Reports - Used for building customized reports

 


 

Field Descriptions

 

Menu

 

In addition to standard toolbar options, SQL Query makes the following available:

 

File

 

Open File - Launches browse dialogue for opening a file in the SQL Query editor
Open Stored Procedure - Launches a dialogue where a stored procedure can be loaded by name
Open New SQL Query Instance - Opens a new instance of the SQL Query applet. This can be helpful, for instance, when working with queries against different data sources.

 

Tools

 

Generate Create Table Statement - Generates a create (#temptable) statement from the last run query
Generate Insert Table Statement - Generates an insert (#temptable) statement from the last run query
Doc / Undock Window - Docs or Undocks the SQL Query applet from the primary InFocus window

 

More on SQL Query Developer Tools

 

Toolbar Buttons

 

Open File - Launches browse dialogue for opening a file in the SQL Query editor
Run Query - Executes the query entered in the editor
Clear Query - Clears the query entered in the editor

 

Editor and Results

 

Query Datasource - Sets the data source the query is to be run against. Defaults to InFocus but can include external data sources defined in Global Settings.
Process Query as Action Result
Replace Variables before Execution - Check to indicate that the script uses InFocus Variables which should be inserted at run-time.
Editor - Used for entering and editing SQL queries
Results tab - Displays the results of the query
Dataset Info tab - Generates an alphabetical fields list based on the result columns. More on the Dataset Info tab.
oCommon Statements - Used to generate template SQL commands against a temp table (e.g. #TableName) for the columns included in the results set
oCopy to Clipboard - Copies the text of the generated common statement to the clipboard
Export Results - Click to export the results of the query to, for instance, excel (e.g. .xlsx, etc.)