The prompted view of a query
You can build simple to complex queries using the prompted
query interface. Select Create --> Prompted Query to
start building a query using the prompted query interface. The Prompted
query interface has five sections where you specify the elements
of your query. The five sections are:
- Tables where you specify the tables that will be used
in the query.
- Join Conditions where you specify the join conditions
for tables, if you are using multiple tables in the query.
- Columns where you specify what columns will be included
in the query results
- Sort Conditions where you specify how the rows in the
query results will be sorted.
- Row Conditions where you can specify conditions that
will limit the rows that are returned in the query results.
In each section you can add, edit, delete and move through the
entries using the icons located for each in the upper right hand
corner.
Tables
You use the Tables dialog to specify the tables that you
want to include in the query. You can specify one or more tables.
If you specify more than one table, the Join Conditions dialog opens
automatically.
Click the icon in Tables List box to add one or more tables to
the query.
Table 97. Tables dialog
Field |
Description |
Table Owner |
The owner identifier of the table that you want
to include in the query. You can use patterns to select objects
from a list of matching objects.
- Use the percent character (%) to match a string of any length
containing any characters. For example, to list all tables with
a name beginning with the letter A, enter A%.
- Use the underscore character (_) to match a single character.
For example, to list all tables with an owner that has the letter
A in the second position, enter _A%.
|
Table Name |
The name of the table that you want to include
in the query. You can use patterns to select objects from a list of
matching objects. See Table Owner for information on matching objects. |
Add from List |
Lists the tables that are on the database server. |
Join Conditions
You
can add more than one table to a query. When you add an additional
table to a query using the Tables dialog, the Join Conditions
dialogs open automatically. There are two Join Conditions dialog
windows:
- The Join Tables dialog, where you specify what type of join
will link the tables, such as an inner join or left, right or full
outer join.
- The Join Columns dialog, where you specify the columns from
each table that will be joined
The Join Tables dialog box contains the following fields:
Table 98. Join Tables Dialog
Field |
Description |
Select a table to join into the query |
Lists the tables selected for the query. This
table will be joined to another table based on the join condition. |
Select the type of join to perform |
Select the type of join condition that will be
used to connect the table listed to the previously selected table.
You have four choices:
- Inner Join
- Right Outer Join
- Left Outer Join
- Full Outer Join
|
Inner join |
Only rows with matching values in both tables
will be included in the results set. An INNER join is implicit if you
do not specify any other join operator.
Note:
An
inner join compares every row of the left table with every row of
the right table keeping only the rows where the join-condition is
true. The resulting table may be missing rows from either or both
of the joined tables. |
Left Outer join |
All rows in the left table, with matching rows
from the right table, will be included.
Outer
joins include the rows produced by the inner join as well as the
missing rows, depending on the type of outer joins. A Left outer join
includes the rows from the left table that were missing from the
inner join. |
Right Outer join |
All rows in the right table, with matching rows
from the left table, will be included.
Outer
joins include the rows produced by the inner join as well as the
missing rows, depending on the type of outer joins. A Right outer join
includes the rows from the right table that were missing from the
inner join. |
Full Outer join |
All rows from both tables will be included
For a FULL OUTER (or FULL) join, the join condition is a search
condition in which the predicates can only be combined with AND.
In addition, each predicate must have the form'expression
= expression', where one expression references only columns
of one of the operand tables of the associated join operator, and
the other expression references only columns of the other operand
table. The values of the expressions must be comparable.
Each full join expression in a FULL OUTER join must include a
column name or a cast function that references a column. The COALESCE
and VALUE functions are allowed.
Outer joins include the rows produced by the inner join as well
as the missing rows, depending on the type of outer joins. A Full outer join
includes the rows from both tables that were missing from the inner
join. |
Continue |
Click Continue to join
the tables. The Join Columns dialog box opens. |
Joining Columns
Use the Join Columns dialog box to specify the columns
that will used to link the tables. The columns from the current
table that you are adding to the query and the columns from each
table that is already included in the query are listed. Select a
column from each list box with the same data type (NUMERIC, DATE, TIME, or CHARACTER). Rows that have equal values in those columns will be joined.
The Join Columns dialog box contains the following fields:
Table 99. Join Columns Dialog
Field |
Description |
Columns of |
Lists all the columns that are included in the
table that you are adding to the query. Select one column from
this list.
Note:
Select a column with matching data types from each list. |
Available columns to join |
Lists all the columns from the table or tables
that are already included in the query. Select one column from this
list.
Note:
Select a column with matching data types from each list. |
Columns
You use the Columns dialog to specify what columns will
be included in the query results. By default, all the columns from
a table that is included in the query are included in the query
results. In the case where you have multiple tables included in
the query, all the columns from each table will be included.
Select Query --> Add --> Columns or
click the icon in Columns list box to specify the columns that will
be included.
The following fields are available:
Table 100. Columns Dialog
Field |
Description |
Table |
Lists the tables that are included in the query.
If there are two or more tables, each table is prefixed by a letter,
such as Q.STAFF(A) and Q.INTERVIEW(B). |
Column |
Lists the columns for each table in the query |
Type |
Lists the data type (character, numeric or time)
for each column, such as:
SMALLINT NOT NULL |
Label |
Lists any labels associated with the column.
Labels on columns are implemented as system column headings or
column text. Column headings are used when displaying or printing
query results. |
Comments |
Lists any comments associated with the table,
such as:
Employee identification number |
Or, enter an expression here |
Use this field to enter a conditional expression
that will determine whether a column is included in the query results.
You can use the Expression Builder to help you build the expression.
The Expression Builder offers a palette of common elements 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.
You can invoke the Expression Builder by:
- pressing Alt + .
- clicking the browse icon to the right of
the input field
|
Summary function |
Apply the summary function to the column.
A number of summary functions, including AVERAGE, COUNT, MAXIMUM, MINIMUM, and SUM. The summary function must be compatible with the data type
of the column. |
New column name |
A new column name for a column that will be used
in the query results. Usually, this is simpler name than the column
name defined in the database.
Select a column from the Column list box,
then type a new column name in the New column name field. The
column will be renamed in the query results, such as SHIFTEND from A.ENDTIME. |
Sort Conditions
Sort condition control how to sort the rows that will
be included in the query results. Rows can be sorted in ascending
(A-Z) or descending (Z-A) order.
If you sort your rows by more than one column, the first column
is ordered first, then the second column is ordered within the sort
order defined for the first column.
There are several methods by which to specify a sort condition:
- From the Columns in the results set list,
select a column included in the query
- From the Other available columns list,
select a column that was not selected from the tables in the query
- From the Or, enter a sort condition field,
type a sort condition
Click the icon in Sort Conditions list box to open the Sort Conditions
dialog box and specify the sort conditions.
Table 101. Sort Conditions Dialog
Field |
Description |
Columns in result set |
Lists the columns that will be included in the
query results.
- You can select one or more columns if you are adding a sort
condition
- You can select only one column if you are changing a sort condition
|
Other available columns |
List
columns that will not be included in the query results but are available
in the table that was queried. You can select any of these columns
for the sort. |
Or, enter a sort condition |
Type a condition by which columns should
sort. |
Expression Builder |
The Expression Builder offers a palette of
common elements 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.
You can invoke the Expression Builder by:
- pressing Alt + .
- clicking the browse icon to the right of
the Enter a sort condition field
|
Sort direction |
Apply an ascending (lowest-to-highest)
or descending (highest-to-lowest) sort order to the columns selected.
The first column is used for the primary sort; subsequent columns
will sort within the first. |
Row Conditions
Use can specify row conditions to limit the rows that
are returned in the query results. Without row conditions, all qualifying
rows will be returned from the query.
Click the icon in the Row Conditions list box to open the Row
Conditions dialog box and specify the row conditions.
Table 102. Row Conditions Dialog
Part of the row condition |
Function |
Connector |
An "And" or "Or" statement that links one row condition
with another. These radio buttons are available only when a row
condition has been added to the query. |
Left side |
Select the column from the list box to examine
for inclusion in the query results |
Operator |
Select the Is or Is Not radio
button to determine the relationship between the left- and right
sides of the row. Also, select the operation to be applied to the row
condition from the Operator drop-down menu. The available operators
are:
- Equal To
- Less Than
- Less Than or Equal to
- Greater than
- Greater than or Equal to
- Between
- Starting with
- Ending with
- Containing
- Null
- Equal in area to
|
|
- Larger than
- Larger than or equal to
|
Right side |
Type the condition for which to check the rows.
Only rows that meet this condition will be selected for the query |
Expression Builder |
Use
the Expression Builder for building SQL expressions for the prompted
query.
The Expression Builder offers a palette of common elements 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 associated SQL query
window or prompted query window.
You can invoke the Expression Builder by:
- pressing Alt + .
- clicking the browse icon to the right of
the Enter a sort condition field
|
