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
Prerequisite: Before
you specify
a WITH statement, create the
statement in the SQL Query Builder.
About this task
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.