(Utilities – Database Queries)
You can use this utility to write and retrieve SQL queries to the database tables and output the results to a CSV or XLS file.
NOTE: If you use this utility to write your own queries, a basic familiarity with simple programming expressions is required.
A series of sample queries are provided that you can use as actual queries or as samples to follow when writing your own queries.
SQL Builder Screen
Tables
In this window are listed all the database tables. You can select a table and click the Add Table to SQL button to add the table to the SQL panel at right.
Fields
In this window are listed the fields within the table currently highlighted in the Tables window. You can select each field to be included in your query and click on the Add Field to SQL button to add the field to the SQL panel at right.
SQL
Your SQL statement (query) is entered in this panel.
Writing a Query
Adding Tables
Add tables to the SQL statement by selecting from the Tables window and clicking the Add Table to SQL button.
Adding Fields
Add fields to the SQL statement by selecting from the Fields window and clicking the Add Field to SQL button.
Keywords
Add keywords by clicking SQL in the top left corner and selecting the Add Keywords option. Values include: *, Sum, Count, From, Where
Operators
Add operators by clicking SQL in the top left corner and selecting the Add Operators option. Values include: >, <, =. <>, +, -, /
Parameter
Add a parameter by clicking SQL in the top left corner and selecting the Add Parameter option. This will add your parameter name to the query (preceded with a colon). When the query is run you will be asked to enter a value, which the query will take into account. For example you may wish to have a query of ‘select * from Jobs where Jobno = :para’. When running the query Parameter ‘para’ will appear for you to enter your job number.
Saving Your Query
If you wish to save your query for future use, click the Save Query button. You are presented with a list of saved queries. Click the Add button and give your query a name. When finished, click the OK button.
Retrieving a Saved Query
To retrieve a saved query, click the Query List button and select a saved query from the list. Once its contents display in the SQL panel, click OK to run the query. The SQL Builder screen closes and the query data are displayed in the grid within the Database Queries screen. Click the Export to CSV or Export to XLS button to output the grid contents to an export file.
