SQL Query Tool

Created by Martin Parkinson, Modified on Sat, 25 May at 12:37 PM by Graeme Orchard

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.


 

Contents (Click an Item to Jump to that section)


TABLE OF CONTENTS



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.

 

TableThis 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.)
ColumnThis 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 smallSQLbutton next to each of the above drop-downs inserts the chosen item (Table or Column) into theSQL 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.
NameThis is used to specify thenamefor your query. Once anamehas been specified it is possible to save the query forfutureuse with theSAVEicon at the top of the screen.
SQLThis 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 thequery. It is also possible to run the query using the Play button in the toolbar.
[  ]MaxThis tick box will display all records on screen without applying the normal (1000 record) display limit.
( ) GridThis 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 )

 

 Back To Top






Toolbar Icons

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


.Back To Top




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.


Back To Top



EXAMPLES

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


Example1: List Terms (Days) for Clients

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 
FROM COMPTAB0001 
WHERE COMP_AC_NAME LIKE'BIG%' ORDER BY COMP_TERMS_VAL


Back To Top



Example2: List Candidates Linked to Matches

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 
WHERE MATSTAB0001.MATS_CAND_UNIQ=CANDTAB0001.CAND_UNIQ_ID

 

Back To Top




Example3: List Candidates when Area Restrict is London

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'


Back To Top



Example4: List Compliance Items 

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.  DISTINCT ensures that duplicate rows are eliminated from the result set.


SELECT DISTINCT RQIN_REQ_CODE, RQDF_DESC 
FROM REQINFO0001, REQDEF0001 
where RQDF_REQ_CODE = RQIN_REQ_CODE


Back To Top



Example5: Count Candidatess by Enquiry Source

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' 
FROM CANDTAB0001
 GROUP BY CAND_ENQ_SRC



Back To Top


Example6: Count Candidates by Enq. Source and lookup Description

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


Back To Top



Troubleshooting/Errors

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)

Set_and_non-set_Mixture

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



b) There are No Tables listed in the SQL Query Tool


It is caused because the Catalogue file is empty. To fix theissue just go into Maintenance >Setup > Table Maintenance - Once done, log OUT/IN and try again - the catalogue should have been recreated and the tables should be available.



Back To Top

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article