Table of Contents
The Database Query (ODBC) tool is dragged onto the task planner. The step can be given a recognisable name. A connection to the business systems database must be selected from the list. Setting up these connections is covered in TaskCentre Administration – Global Configuration – ODBC / OLE DB connections.
On the second tab of this tool the query that will return the data is defined. A query can be defined using the drag and drop query editor. A knowledge of the structured query language, SQL, is not essential but may be needed for more advanced queries. You can read up on SQL on the w3schools website.
On the left are the tables and views available for the connection. Each table or view can be expanded to see the data columns within it.
Dragging the data columns from this list into the columns pane to the right adds them to the data returned by the query and makes them available for subsequent steps. Adding a column from a table to the columns pane automatically adds it to the Tables & Joins pane.
If data from multiple tables is required then for the query to work joins between the tables must be defined. If the joins are defined in the database then they will automatically appear. If not, dragging a column from one table to a column in another table creates a join. By default a simple join (inner join) is created but this can be changed by right clicking on the arrow between the tables and selecting ‘Join Type’. You can then select alternative types of joins.
The criteria pane allows the user to create multiple criteria that limit the records returned to only those matching the criteria. To create a criterion a column can be dragged from the list on the left into the Criteria pane.
A comparison method is selected and a value or values entered (depending on the comparison method) or a value selected from a list of those available in the database. Multiple criteria can be dragged into the criteria pane if required.
By default an ‘And’ is added between the criteria, this can be changed to an ‘Or’ by double clicking the ‘And’ or ‘Or’. Dragging a column onto a criterion creates a nested criterion group.You can move a group by dragging and dropping it into another position in the criteria.
If it is required to sort the results, the sorting pane can be added by clicking the sort pane button.
Multiple columns can be dragged from the left hand pane or the columns pane into the sorting pane. The hierarchy of the sort can be changed by dragging the columns into the desired order. Double clicking a column in the sorting pane reverses the order of the sort on that column (ascending or descending).
Testing the Query
The query can be tested by pressing the Test button .
A sample set of records are displayed. This recordset (with the complete data set) is the output from this step.
Manual SQL edits
The above actions generate a SQL statement that is executed at runtime. This SQL statement can be viewed on the Query tab on the SQL sub tab. Optionally, this SQL statement can be manually edited (or rewritten completely).
In order to edit it the Free Type Mode button must be pressed.
To read about tasks which make use of this tool see