Using Table Expressions

The Query Builder allows you to use a Table Expression in your query.

You will use a Table Expression when your data is to come from an SQL statement, rather than a table. Table Expressions are also known as Inline Views or sub-queries.

An example of a query which uses a Table Expression is

SELECT * FROM

Customers a,

(SELECT CustomerID, count(*) as num FROM Orders GROUP BY CustomerID) b

WHERE a.CustomerID=b.CustomerID

There are two “tables” in this query:

the first table is Customers
the second table is (SELECT CustomerID, count(*) AS num FROM Orders GROUP BY CustomerID)

This second of these is a Table Expression. Most databases support the use of Table Expressions, which provide a mechanism for developing powerful queries.

query_builder_table_expression

New Expression / Edit Expression

To specify a new Table Expression, click on New Expression. Enter the SQL text for the table expression.

The table will now appear in the query with the name (expression). If you create more than one Table Expression, the table-ids will differentiate them.

You can amend the table expression by clicking on the Edit Expression button.

New Expression (in QB)

You can create a Table Expression in another Query Builder window. If you click on New Expression (in QB), AQT will open a new Query Builder window in which you can create a query to be used as a Table Expression.

this Query Builder window will be labelled as Build Expression in the AQT window-list bar
the first line of the SQL text will be a comment that this is an expression for another Query Builder window
you can run this query, and generally operate this Query Builder window as per normal
as you amend the query, the query in the parent Query Builder window will be changed accordingly

Edit Expression in QB

Clicking on Edit Expression in QB will allow you to edit a Table Expression in a Query Builder window

if you are already editing that expression in a Query Builder window AQT will take you to that Query Builder window
if you do not have a Query Builder window open for editing that expression, AQT will open a new Query Builder window

Identifying Table Expressions

A Table Expression must generally have a Table-Id. AQT will normally generate one unless you have set the option not to use Table-Id (Options > Table ID > no table id), or if it is the only table in the query.

Any generated or summary columns in your Table Expression should be given names with the AS clause.

Invalid	no proper name for second column, difficult to reference	`SELECT CustomerID, count(*) FROM Orders GROUP BY CustomerID`
Valid	`AS` clause gives name to column	`SELECT CustomerID, count(*) AS CustCount FROM Orders GROUP BY CustomerID`

In order to determine the columns in the Table Expression, AQT does a “prepare” on the SQL for the Table Expression. Some databases do not support a standalone prepare, and may run the SQL; this might cause a delay when you enter or change a Table Expression.