Using the Gui query builder

The Gui query builder can be started in three different ways:

• from the Database Explorer, select a table then use the Build Query menu item
• from the Run SQL window, click on the Query icon or Build Query menu item
• in the Database Explorer, select a view, right-click then select View as Query

The Gui query builder has these major features:

• allows you to build a query using the Gui paradigm – a graphical display of tables. This is particularly useful for building joins between tables and visualise a query easily
• most aspects of a query can be specified – column order, column functions, column aliases, constants, summary queries (group by), row order, where clauses.
• AQT can also reverse-engineer a query. It can take an existing SQL statement and turn it into a graphical query. This can be SQL statements generated by AQT, or some existing SQL you have. You can amend this code or reformat the SQL using the many options AQT provides for the format of the generated SQL.
• AQT can also build and reverse-engineer View definitions.
• There are limitations to the SQL that AQT can build or reverse-engineer.

Joining Tables

• Joins between tables can be quickly defined by dragging the mouse between the related columns in the Gui.
• When you have a "known" relationship between your tables (eg. it is defined to your database as a foreign key relationship) AQT can automatically build a join between these tables with a single click.
• There are many cases when you have a relationship between your tables, but this is not defined to your database. In this case AQT allows you to save the definition of the relationship as a user-defined relationship. The details of the relationship are permanently saved (to disk); you can then build a join between these tables with a single click.

Starting the Gui

The Gui window has the following tabs:

Gui – to show a graphical display of your query tables and joins.

Tables / Joins – to specify further information about the query tables and joins.

Columns – to specify further information about the columns in the query, and for building complex query-columns.

Where – to build the WHERE statement for the query

Options – to specify the many options you have for the way the SQL is generated.

In addition, at the bottom of the window is a text box that displays the query SQL. This text will change as the query is built or amended.

Toolbar

The toolbar gives access to frequently-used functions. You can customize the toolbar to change its appearance or add / remove buttons. These functions are also available from the Menu bar and shortcut keys.

Some functions that need to be described are as follows:

• Refresh. Resets the query to the way it was when you entered this window. All changes you may have made will be lost.
• Refresh Table Info. For performance reasons, AQT caches in memory the information about the tables in your query. This includes the columns, primary keys and foreign keys of the tables in your query. This reduces the number of times AQT has to query the system tables, which can sometimes be a slow process. However there is a disadvantage to this – if you change a table (eg. add some columns, or add a foreign key) then the Query Builder will not show this change. It will continue to use the old definition. If this happens, click on the Refresh Table Info icon. The information on the tables will be refreshed from the system tables.
• Assistant. This switches the Query Assistant on or off.