Gui Query Builder - Columns Tab

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

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

specify which table columns are in the query
specify additional options about the query columns
build more complex query-columns, such as constants, sub-selects, etc
specify the order in which the columns appear in the query
specify the sort-order for the query (Order-By)
specify whether the query is a standard query, or a summary query (Group By)

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.

You can include all columns in the query by clicking on All.

You can exclude all columns by clicking on None.

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.

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.

Query Type

Specifies whether you are running a Normal Query or 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.