Previous Topic

Next Topic

Book Contents

Book Index

Build Query Column

Use this Window to build or modify an individual Query Column. You open this window by clicking on NEW or MOD in the GUI Columns tab.

The layout of the window reflects the normal format of a Query Column, which is:

function(column_name) As “Display Name”

Examples:

cust_type

substr(cust_name,1,2) As “Cust Prefix”

Table/Column

Specifies the column the Query Column is based on. If you are modifying a Query Column these boxes will be disabled.

Column Function

Specifies an (optional) column function. You specify this in the format function(:) – the column name will be substituted wherever the colon appears. In the listbox you will be shown a list of common functions for the type of your column. You can select one of these or type one in. If you have a long column function, click on the >>> button to get an expanded window for entering a long value.

As you build the query column, its full text will be displayed in the Column Expression text box.

Column-based

The above discussion relates to Query Columns which involves a column from one of your query tables. We call these “Column-based Query Columns”. The majority of your Query Columns will be like this. However it is possible to have Query Columns that are not based on a Table Column.

Examples of this are:

Literal – eg. ‘Test’ , 0

System Constant – eg. Sysdate(), Current_Date

Sub-select – eg . (Select count(*) from Tab1)

You can specify clauses such as this by setting Clause is Column Based to No. The Table/Column window will be greyed out – you type the query column directly into the Column Expression text box. In addition, a list of common System Constants will also be displayed in the Expressions text box – you can select one of these.

Sub-selects

Sub-selects can be used in a Query Column. AQT allows you to enter a sub-select (by typing one into the Column Value box), however it gives you no assistance in building it. This will be done in a later release of AQT.

Other query options

These specify a number of other options for the Query Column.

Display Name – gives the name for the Query Column.

Group-By Seq – for a summary query, this specifies that you are summarising by this column (and the order in the group-by list).

Order-By Seq – specifies to Sort the query results by this column. Use Asc/Desc to specify whether to Sort Ascending or Sort Descending.

Include Col – for some databases it is possible to Sort by a column, yet not have it in the Result-set. This option allows you to do that: if you are sorting by this column the Include Col checkbox will be enabled. If you de-select the checkbox, the column will not be included in the result-set.