Creating a WITH statement

A WITH statement is composed of one or more common table expressions and a SELECT statement. A common table expression defines a named result table that can be specified as a table in the FROM clause of a subsequent SELECT statement. This option is available only if you are using IBM DB2 Universal Database.

You only can create a WITH statement using the SQL Builder; you cannot create it with the Create A New SQL Statement wizard.

Common table expressions

A common table expression can be used in the following ways:

• In place of a view to avoid creating the view (when general use of the view is not required and positioned updates or deletes are not used)

• To enable grouping by a column that is derived from a scalar subselect or function that is not deterministic or has external actions

• When the result table is based on host variables

• When the same result table needs to be shared in a FULLSELECT

• When the result needs to be derived using recursion

To create a WITH statement:

1. Switch to the Data Definition view in the Data perspective.

2. Expand the project or folder that contains your database, and then expand the database nodes until you see the Statements folder.

3. Right-click the Statements folder, and then click New > With Statement on the pop-up menu.

4. Type a name for the statement, and then click OK. The SQL Builder opens.

5. Review the structure of the WITH statement in the Outline view. A SELECT statement is automatically added to the WITH statement. This is referred to as the WITH statement SELECT. You must first define common table expressions, then complete the WITH statement SELECT using the tables defined in the common table expressions.

6. To add a common table expression, right-click the WITH statement in the Outline view, and then click Add Common Table Expression on the pop-up menu. To complete the common table expression, you define a temporary table and its columns that is based on a SELECT statement's result set. Because the table you are defining is only temporary, it does not appear in the Tables folder in the Data Definition view. After the temporary table is defined in the common table expression, you can add it to the WITH statement SELECT.

7. In the Outline view, click the common table expression node underneath the WITH statement.

8. Expand the common table expression node and then click the SELECT statement that is contained within.

9. Complete the SELECT statement for the common table expression.

10. In the Outline view, click the common table expression and add columns to it by assigning column names and then clicking Add>> to complete the column list. The columns named should correspond to the columns defined in the SELECT clause of the common table expression SELECT statement.

11. Add more common table expressions in the same way, if needed.

12. Select the WITH statement SELECT in the Outline view. It is named statementnameSELECT, where statementname is the name of your WITH statement. This is the SELECT statement that applies to the WITH statement as a whole and uses the common table expressions defined in the WITH statement.

13. Complete the WITH statement SELECT.

14. After you have finished, select SQL > Execute to execute the SQL statement.

The output is shown in the DB Output view.

 

Parent topic

SELECT, UPDATE, INSERT and DELETE statements. The SQL Builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement that you create is stored in a file with the extension .sqx.">Creating an SQL statement

 

Related tasks

Editing an SQL statement
Executing an SQL statement