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.
Prerequisite: Before you specify a WITH statement, create the statement in the SQL builder.
To specify a WITH statement in the SQL builder:
- With the WITH statement open in the SQL 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 builder, build the SELECT
clause for the common table expression.
- In the Outline view, click the WITH WithTable node.
In the SQL 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 builder.
- Run the SQL statement. The output is shown in the Data Output view.