You can specify the columns that are included in the query
results.
About this task
You use the Columns window to specify
what columns are included in the query results in the Prompted
Query or Query Builder editor.
By default, all the columns from a table that is included in the query
are included in the query results. If you have multiple tables included
in the query, all the columns from each table are included. To
specify the columns that are included in the query results:
Procedure
- Open the Columns window
by clicking the Add Column button .
- Each table that was added to the query is listed in the Table field.
If
there are two or more tables, each table is prefixed by a letter,
such as Q.STAFF(A) and Q.INTERVIEW(B).
- All the table columns are listed in the Column field.
- The type of data that is contained in the column is listed in
the Type field.
- The label that is associated with the column is listed in the Label field.
Labels
for columns are system column headers or column text. They are used
when the query is displayed or when the results are printed.
- Any comments that are associated with a column are listed in the Comments field.
- You can include a column in the query results in one of
the following ways:
- Select a column from the list of available columns by
clicking the name of the column as it appears in the Column field.
You can filter the listed columns by typing
a necessary column name or label, which is associated with the column,
in the text field in the Value area.
- Select all the columns of a table by selecting the name
of the table in the Table field.
- Select all the columns of a table by selecting the value
<All
columns> in the Column field.
- Enter an expression in the Or, enter an expression
here field.
The expression that you enter
is used to evaluate a column and determine whether it is included
in the query results.
Tip: Click the
ellipsis (...) to open an expression builder. The expression builder
offers a palette of common elements that are used to create SQL expressions,
such as column names, constants, functions, and operators. When you
click the expression builder buttons, templates for expression elements
are inserted into the expression fields.
- From the Summary function list,
select how you want to summarize the column in the query results.
You can specify the following summary options:
- (None) - No summarization is applied.
- Average - The average of all values in
the column is included in summary field.
- Count - A count of all the rows is included
in the summary field.
- Maximum - The maximum value that was in
the column is included in the summary field.
- Minimum - The minimum value that was in
the column is included in the summary field.
- Sum - The sum of all the values in the
column is included in the summary field.
- To specify a new name for the column in the query results,
type the new name in the New column name field.
Unless you specify a new name, the name of the column in
the column header of the query results is displayed exactly as it
appears in the Column field.
- Click Add.
The column
is included in the query results. Repeat these steps for each column
that you want to include in the query results.
- If you work in the Prompted Query editor,
click the Change button if you want to change
to a column that was added to the query results.
The
change to the column is made, and the Columns window
closes.
- Click Close when you finish adding
all the columns that you want to be included in the query results.
The Columns window closes.