Running SQL & Queries

Query Builder Overview

Table of Content

Table of Content

Table of Content

The Query Builder allows you to build a query in graphical mode. For information on the features of the Query Builder, see Features of the Query Builder.

Starting the Query Builder

The Query Builder window can be started in a number 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

  • in the Data Display window, click on File > Edit SQL in Query Builder

Tabs on the Query Builder window

The Query Builder window has a number of tabs for building the query:

  • the Gui tab allows you to build and visualize the query in a graphical mode

  • the remaining tabs allow you to specify other parts of the query.

These are described in the following topics in this chapter.

Features of the Query Builder

The Query Builder allows you to build queries in a graphical paradigm.

  • it is very easy to build multi-table queries - tables can be joined by dragging columns between tables

  • AQT can automatically join tables based on their foreign-key relationships

  • columns can easily be added / excluded from the query

  • you can change the order of the columns in the query plus specify more advanced aspects of the query columns. This includes column functions, column alias names, column sort order.

  • you can specify Where clauses (search conditions) for the query. The Query Builder has a very full-featured ability to build Where clauses.

  • you can build Summary queries

  • AQT generates nicely-formatted SQL. There are many options for how the SQL is formatted.

Some of the more advanced features:

  • if a join is not defined to the database a as foreign-key relationship, you can save it as user-defined relationship. AQT can then automatically rejoin the tables the next time you are building query.

  • your queries can include table expressions. These are also known as inline views or sub-queries.

  • you can build queries that contain multiple Unioned select statements

  • your queries can use a With statement

  • you can build SubSelects, included Correlated SubSelects.

AQT can reverse-engineer queries

This is a major function of the Query Builder, and one which is available in very few other products.

AQT can take an existing SQL query and display as a graphical query in the Query Builder. This allows you to open a build and save a query, then reopen it in the Query Builder at a later date.

  • the query can either be one built by AQT, or some other product. It doesn't matter, as long as it clear SQL text.

  • AQT can reverse-engineer most SQL queries - including those using table expressions, With statements, Unioned statements etc.

  • this provides a very useful way of "visualizing" existing queries, even if you do not intend to amend them

Running your Query

Once you have built your query in the Query Builder, you can run it by clicking on Run (or F5).

Exporting

You can export your data by clicking on Query > Export Data (F7).

Check Syntax

You can check the syntax of your query by clicking on Query > Check Syntax (F6).

This will do a "prepare" of your query, but will not actually run it. Some databases may not support this mode of operation.

Specifying where Query Results are Displayed

You can display the query results in either:

  • a separate Data Display window.

    This is useful if you wish to do further manipulation of the data (grouping, totalling etc)

  • in a tab at the bottom of the Query Builder window

    This is useful if you quickly want to see the results of your query

You can also specify this with View > Display Query Results on this window

Removing display tabs

  • You can remove a tab by clicking on the cross at the right of the tab

  • You can remove all tabs with View > Clear all grids

Using the data display tabs

This are a number of useful things you can do with the display grids. These are similar to the data display tabs on the Run SQL window.

Running your query against another database

You can run your query against another database with Run > Run Against, or use the Run against item in the toolbar.

The query will only work if your run-against-database has the same tables/columns as the database you are building the query against.

This does not change the database you are building the query against.