Use the SQL builder and the Outline view to create a WITH statement.
A WITH statement is composed of one or more common table expressions
and a SELECT statement.
- SQL Source pane
- Use this pane to review the WITH statement as
you build the statement in the Tables and Design panes and in the Outline
view. When the top node is selected in the Outline view, you can also
type changes directly in this pane. If you change the statement in this pane,
you can parse the statement by moving the focus out of the pane or by saving
the statement. If the parser considers the statement syntax valid, the Tables
and Design pane are synchronized to reflect the updated statement.
- Tables pane
- Use this pane to add tables to the SELECT statement
that is in the WITH statement. You can also select columns and specify join
conditions in this pane. After you add a table, to add columns to the
SELECT statement, select their check boxes in the table graphic. To specify
a join condition, right-click a table graphic and then click Create
Join on the pop-up menu, or drag the pointer from a column in
one table to a column in another table.
- Design pane
- Use the fields and pages in this pane to define
elements of the SELECT statement and SELECT clauses that are in the WITH statement.
- Statement
- The name of the WITH statement.
- DISTINCT
- If this check box is selected, the query results
will not contain duplicate rows of data.
- Columns
- Use this page to specify the columns to use in
the SELECT statement. This page shows the columns that you have selected
in the Tables pane.
- Conditions
- Use this page to specify which rows of the database
table to include in the result set.
- Groups
- Use this page, together with the Group Conditions
page, to specify a GROUP BY clause. A GROUP BY clause contains a grouping
expression that is used to define the grouping of the result set of the previous
clause of the SELECT statement.
- Group Conditions
- Use this page to specify the conditions for the
grouping expressions that you have defined on the Groups page.