Ability to use common table expressions


A query is a component of certain SQL statements; it specifies a (temporary) result table.

A table expression creates a temporary result table from a simple query. Clauses further refine the result table. For example, you can use a table expression as a query to select all of the managers from several departments, specify that they must have over 15 years of working experience, and be located at the New York branch office.

A common table expression is like a temporary view within a complex query. It can be referenced in other places within the query, and can be used in place of a view. Each use of a specific common table expression within a complex query shares the same temporary view.

Recursive use of a common table expression within a query can be used to support applications such as airline reservation systems, bill of materials (BOM) generators, and network planning.

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. You can create WITH statements only if you are using IBM DB2 database.

You can use a common table expression in the following ways:

The syntax used to construct a common table expression is:

WITH [table-name]<([column-name], ...])>



Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz