HearAid Version 5.0

SQL

SQL

Previous topic Next topic  

SQL

Previous topic Next topic  

QueryWorkArea

 

The SQL work area consists of five main components which build and display SQL (Structured Query Language) queries:

1.The Table List displays the names of all the available tables in the database. As you select a table you will see the Field List changes. To add a table to the query you can
Drag-and-drop the table name from the list onto the editor, or
Double-click the table name, or
Select one or more table names and click the Insert Table into SQL button

If you add more than one table to a query you must JOIN the tables on a common field e.g. all the client-related tables have a field called CLIENT_PK which stores the unique client ID number for each client. For examples of JOIN syntax, have a look at the SQL files supplied with HearAid.

 

Tip: click the Load SQL button on the toolbar to examine some of the supplied SQL files

 

2.The Field List displays the available fields (or columns) for the selected table. If you want to include all available columns in the query, select the asterisk * , but bear in mind that the more fields you include, the longer it takes to fetch the data, so it is good practice to limit the fields just to those you really need. To add one or more fields to a query you can
Drag-and-drop the field name from the list onto the editor, or
Double-click the filed name, or
Select one or more table names and click the Insert Fields into SQL button

 

3.Code Completion is activated by pressing Ctrl+SpaceBar in the editor. A drop-down list appears showing all the available field and function options which match what you have typed so far. This feature is really useful for adding fields to JOIN, WHERE, GROUP BY, ORDER BY and HAVING clauses in the query

 

4.The SQL script is the actual query which will be executed when you click on the Execute SQL button. If there are syntax errors in your script the database engine will generate a detailed error message telling you where and why your SQL has failed

 

5.If the script is a SELECT query the Query Result is displayed by either clicking the Execute SQL button, or by clicking on the Data tab. This table can be printed by clicking on the Print Table button. The data can also be manipulated by sorting, filtering and grouping in exactly the same way as for any other Browse view