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.
| 2. | Select a data source (defaults to- and is typically- InFocus) |
| 3. | Using the editor, write the query |
| o | Optionally 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. |
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:
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 |
| o | Common Statements - Used to generate template SQL commands against a temp table (e.g. #TableName) for the columns included in the results set |
| o | Copy 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.) |
|