SQL Query Workbench
A new reporting tool has been added to the system after release 1.00.17.052
It allows users to write SQL queries and return results from the database. These results can then be exported to MS excel for further analysis or manipulation.
How it Works
The SQL workbench can be found in the [Admin] section of the system.
Upon first selecting the workbench, for security, users will be prompted for the 'ADM' user password (unless they are already logged in as the ADM User). Having entered the password correctly the user will be allowed to continue to use this workbench whilst they remain in the option. Navigating away and then returning to the workbench will require the password to be re-entered.
Having opened the workbench users can choose a Table using the drop-down and then choose columns to display using the Column: drop-down.
If known, users can write an SQL query directly into the SQL: box or use the drop-down boxes to choose the relevant item(s) and then press the [SQL] button to insert the chosen item into the SQL query.
Once run the results of the query will be displayed on-screen in the chosen format (Grid/JSON) and can be exported to MS Excel using the Excel icon in the toolbar. Columns relating to Unique_IDs for records, such as Candidates, Vacancies, Sites or Companies will be indicated with the Navigator (>) and clicking on the field will drill-in and open the relevant record in the database.
Table: This drop-down allows users to select the data table they wish to report from. Having selected a table pressing the [SQL] button to the right will insert the chosen field into the SQL query box, using the syntax Select * FROM xxxxxxxx (where xxxxxxxx is the name of the chosen table.)
Column: This drop-down allows users to select fields from the selected table. Then can be inserted into the SQL query box by pressing the [SQL] button to the right. Repeated pressing of the button will insert sequential fields into the SQL query one-after-another.
[Sql] The small SQL button next to each of the above drop-downs inserts the chosen item (Table or Column) into the SQL Query box.
[Paste] This small button allows users to copy the chosen item (Table/Column) into the windows pasting buffer which can then be pasted put into 3rd-party applications.
Name: This is used to specify the name for your query. Once a name has been specified it is possible to save the query for future use with the SAVE icon at the top of the screen.
SQL: This is where you should write your SQL query. The query can contain all of the usual SQL syntax such as WHERE, SELECT, ORDER BY, LIKE.
NB: It is not possible to use the SQL query tool to create, add, modify or amend records within the database - it is a "Read only" tool.
[Run] This button (bottom left) will run the query. It is also possible to run the query using the Play (>) button in the toolbar.
[ ]Max This tick box will display all records on screen without applying the normal (1000 record) display limit.
( ) Grid This will display the results of the SQL Query in a grid (i.e. on-screen) which can then be exported to MS Excel.
( ) JSON This will display the results of the SQL query in JSON (a standard data format used by many web based systems )
The icons in the toolbar are mainly used for query management and will display their function with a hover-tip when the mouse pointer is left over the icon. In order (from left to right) the icons are:-
ADD Add/Start a New Query
OPEN Open a saved query (A list of queries will be displayed and can be chosen)
SAVE Save the Current query using the name entered in the NAME: field on-screen.
(Note > If you change the name of the query and press SAVE it will be re-saved with the new name, and does NOT create a new query)
DELETE This will delete the current query from the saved query list.
PLAY This will RUN the currently open query.
EXCEL This will export the current on-screen query information into MS Excel (Users will be prompted for the Excel export password)
HELP The usual Influence help, with access to the support pages and Mikogo Screen sharing tool.
Example showing the OPEN box with a choice of saved queries.
Creating Groups from Query
From release version 1.00.18.186 or later, it is possible to create a GROUP based upon the results of an SQL Query. (See below)
NB: In order for this to work the output from the Query must contain the unique ID of the main object as the first column of the query results. For example, if you have written a query to extract a list of candidates based upon certain criteria then the output, then the first column of the output must be Candidate Unique ID in order to allow you to create a GROUP of candidates.
You can find out more about GROUPS from HERE.
Below are some examples of SQL queries to give users an idea of what is possible. When reporting from two (or more) tables, the format is;
Select Field1, Field2, Field3, Field4, from Table1,Table2,
where table1.field_X=table2.field_Y and field_z
This will display a list of companies and the "Terms" Value, sorted into order of Terms Value
SELECT COMP_UNIQ_ID, COMP_AC_NAME, COMP_TERMS_TYPE, COMP_TERMS_VAL, COMP_AC_NAME
WHERE COMP_AC_NAME LIKE'BIG%' ORDER BY COMP_TERMS_VAL
This will display a list of Match record ID's and links to the Candidate, then shows their name.
SELECT MATSTAB0001.MATS_UNIQ_ID, MATSTAB0001.MATS_SITE_UNIQ, MATSTAB0001.MATS_CAND_UNIQ, CANDTAB0001.CAND_UNIQ_ID, CAND_FORENAME, CAND_SURNAME
FROM MATSTAB0001, CANDTAB0001
This will display a list of Candidates and their name/address where their 'Geographic restriction' is set to 'London'
SELECT CAND_FORENAME, CAND_SURNAME, CAND_UNIQ_ID, CAND_EMAIL, CAND_GEOG_REST, CAND_ADDR_HSE_NO, CAND_ADDR_LINE_1, CAND_ADDR_LINE_2, CAND_ADDR_LINE_3, CAND_ADDR_LINE_4, CAND_ADDR_LINE_5, CAND_POSTCODE FROM CANDTAB0001
WHERE CAND_GEOG_REST ='London'
This will display a list of Compliance codes which are currently in use. It shows the use of the DISTINCT qualifier and also joining two tables. The REQINFO0001 table is linked to the REQDEF0001 table using the 'Requirement code' field from each table
SELECT DISTINCT RQIN_REQ_CODE, RQDF_DESC
FROM REQINFO0001, REQDEF0001
where RQDF_REQ_CODE = RQIN_REQ_CODE
This will display a count of the number of candidates for each Enquiry source. Notice the use of the as 'Qty' to label this column in the output.
SELECT CAND_ENQ_SRC , COUNT(CAND_UNIQ_ID) as'Qty'
GROUP BY CAND_ENQ_SRC
This will display count of the number of candidates for each Enquiry source, and lookup the Description of the Enq. Source from the CODX table.
SELECT CAND_ENQ_SRC, CODX_DESC , COUNT(CAND_UNIQ_ID)as'Qty' FROM CODXTAB0001, ENIDTAB0001
Where CODX_TAB_NO='913' and CODXTAB0001.CODX_CODE=ENIDTAB0001.CAND_ENQ_SRC
GROUP BY CAND_ENQ_SRC
If things don't work as expected or you have problems, we have listed some common issue below:
a) In incorrectly written Query using SUM can cause the system to crash. (After version 1.00.20.160 the system will display an error message as below)
This query will not work as you need to use GROUP BY when using SUM:
SELECT SUM(TIME_INV_AMT) FROM TIMESHT0001 <<<< This will not work
What you want is a query more like this:
SELECT TIME_TS_NO, SUM(TIME_INV_AMT) as'InvAmount' FROM TIMESHT0001
GROUP BY TIME_TS_NO