DB2 graphic QMF Version 8

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:

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 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:

  1. pressing Alt + .
  2. 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:

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:

  1. pressing Alt + .
  2. 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:

  1. pressing Alt + .
  2. clicking the browse icon to the right of the Enter a sort condition field


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004