Previous Topic

Next Topic

Book Contents

Book Index

GUI Query Builder - Columns Tab

Access this window by clicking on the Columns tab of the Query Builder.

The window shows the all the query columns. Use it to:

Query Columns or Table Columns

There is an important distinction between table columns and query columns. They can be quite different:

Column type

Description

Notes

Examples

table column

a column in one of the tables used in your query

can appear in more than one query column

 

query column

can be a combination of table-columns and functions on table-columns

 

substr(col1,1,20)

col1*100/col2

 

can be unrelated to any table-column

We sometimes refer to these as “non column-based” query columns

it could be a literal, a system-variable or a sub-select

NULL

sysdate()

‘Name’

23

AQT allows all these types of query-columns to be built.

Table / Columns

The left pane of the Columns Tab contains the list of tables and columns. You use this to specify the columns to be in the query.

Query-Column Grid

The right pane is a grid showing the columns in the query. It displays:

Grid column

Use

Notes

Table Id

Identifies the table the query-column comes from

This cell will be blank for a non-column-based query column.

Column

Identifies the column the query-column comes from

This cell will be blank for a non-column-based query column.

Function

Enter a column function (if any).

To add a column function, click in this cell to display a drop-down list of the common functions for that column type.

These functions will shown as, for instance, substr(:,1,20) – the column name will be substituted where the colon appears.

Select a function from the list, or type one in.

If you need more space to enter a complex column function, click on the Mod button to enter the Query Column window.

Display Name

You can specify the name for the Query Column if you do not wish to use the database-generated name

When you run a query, your database will give a name to all your query columns. You see this name in the Query Results display.

By default, the Query Column name is the same as the table-column name. In some circumstances, your database will give the columns a system-generated name.

If you wish to specify a name, enter it in this field.

Order By

Specifies whether the database is to sort the query by this column

Click on this cell to sort by the column – it will display, for example, 1, in the cell. This means the column is the first in the sort order.

To decrease the sort-order, right-click the cell.

To remove this cell from the sort order, continue right-clicking until the number disappears, or [Careful:] use the Keyboard Delete key NOT the screen Delete button, which will delete that column from the query.

If you delete a column by accident, replace it by using the New button.

Asc/Desc

Specifies whether the sort order is Ascending (default) or Descending.

Click the cell to toggle between Asc and Desc.

This will be blank until an Order By has been set for the column.

Group By

The grid will include a Group By column if you have specified a Summary or Group-by Query.

Click on this cell to specify the column as being a Summary or Group By column.

Change column order by dragging them up and down in the grid.

New

Use this button to add a new query column. You might use this option to add a non-column-based query column. Another circumstance when you might need to use this is if one of your columns is to appear twice in your query (for instance “Select name, left(name,1,5) from table”) (this is the only way you can add a column twice). This option opens the Query Column window.

Mod

Use this button to modify a query column. This takes you to the Query Column window, which displays full details of the query column. You can modify these as necessary.

Del

Use this button to delete a query column. If you have selected a range of columns, all of them will be deleted.

Summary Query

When this is checked, this specifies that you are running a Summary Query. A Summary or Group-by Query is used to summarise the contents of the table. When you specify a Summary Query another column (Group By) will appear in the column grid. You use this to specify which columns you are summarising by.

Distinct

Use this option to display only distinct rows in your query. Duplicate rows will be removed. This option is generally not of much use if you are displaying all columns in the table. It is often used to display the different values a column can take. In this case you will specify only one or two columns in the query.

Types

When this is checked, the column types will be displayed in the grid.

Desc

When this is checked the column description will be displayed in the grid..