Specifying WITH statements in the SQL builder

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:

  1. With the WITH statement open in the SQL builder, in the Outline view expand the root-node SELECT statement.
  2. Expand the WITH WithTable node, and then click its child SELECT clause.
  3. In the SQL builder, build the SELECT clause for the common table expression.
  4. 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.
  5. 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.
  6. 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.
  7. Build the WITH statement SELECT in the SQL builder.
  8. Run the SQL statement. The output is shown in the Data Output view.
Related reference
SQL builder for WITH statements

Feedback