A WITH statement is composed of one or more common table
expressions and a SELECT statement. A common table expression defines
a named results table that can be specified as a table in the FROM
clause of a subsequent SELECT statement. The WITH statement is available
only if you are using IBM® DB2® database.
Before you begin
Before you specify a WITH statement, create the statement in the SQL Query
Builder.
Procedure
To specify a WITH statement in the SQL Query Builder:
- With the WITH statement open in the SQL Query Builder,
in the Outline view expand the root-node SELECT statement.
- Expand the WITH WithTable node,
and then click its child SELECT clause.
- In the SQL Query Builder, build
the SELECT clause for the common table expression.
- In the Outline view, click the WITH WithTable node.
In the SQL Query Builder, complete the column list. Add columns to
the common table expression by assigning column names and then clicking Add>>. The columns must correspond to the columns that are defined
in the SELECT clause of the common table expression SELECT statement.
- Optional: To add more common table expressions, in the
Outline view, right-click the root-node SELECT statement, and then
click Add Common Table Expression (WITH) on
the pop-up menu.
- In the Outline view, click the WITH statement SELECT. The SELECT statement is the sibling of the WITH WithTable nodes.
This SELECT statement applies to the entire WITH statement and uses
the common table expressions that are defined in the WITH statement.
- Build the WITH statement SELECT in
the SQL Query Builder.
- Run the SQL statement. The output is shown in the SQL Results view.