If a fullselect of the view contains a reference to the view itself in a FROM clause, the view is a recursive view. Views using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.
The restrictions that apply to a recursive view are similar to those for a recursive common table expression:
If a column name of the view is referred to in the iterative fullselect, the attributes of the result columns are determined using the rules for result columns. For more information see Rules for result data types.
Recursive views are not allowed if the query specifies:
If SQL names were specified, the view will be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the view will be created in the schema that is specified by the qualifier. If not qualified, the view name will be created in the same schema as the first table specified on the first FROM clause (including FROM clauses in any common table expressions or nested table expression).
If a view name is not a valid system name, DB2 UDB for iSeries SQL will generate a system name. For information on the rules for generating the name, see Rules for Table Name Generation.
A list of column names (and system column names) must be specified if the result table of the subselect has duplicate column names, duplicate system column names, or an unnamed column. For more information about unnamed columns, see Names of result columns.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.
fullselect must not reference variables.
The maximum number of columns allowed in a view is 8000. The column name lengths and the length of the WHERE clause also reduce this number. The maximum number of base tables allowed in the view is 256.
For an explanation of fullselect, see fullselect.
common-table-expression defines a common table expression for use with the fullselect that follows. For more information see common-table-expression.
CHECK OPTION must not be specified if:
If CHECK OPTION is specified for an updatable view that does not allow inserts, then the check option applies to updates only.
If CHECK OPTION is omitted, the definition of the view is not used in the checking of any insert or update operations that use the view. Some checking might still occur during insert or update operations if the view is directly or indirectly dependent on another view that includes a CHECK OPTION. Because the definition of the view is not used, rows that do not conform to the definition of the view might be inserted or updated through the view.
CREATE VIEW V1 AS SELECT COL1 FROM T1 WHERE COL1 > 10 CREATE VIEW V2 AS SELECT COL1 FROM V1 WITH CHECK OPTION CREATE VIEW V3 AS SELECT COL1 FROM V2 WHERE COL1 < 100
SQL statement | Description of result |
---|---|
INSERT INTO V1 VALUES(5) | Succeeds because V1 does not have a CHECK OPTION clause and it is not dependent on any other view that has a CHECK OPTION clause. |
INSERT INTO V2 VALUES(5) | Results in an error because the inserted row does not conform to the search condition of V1 which is implicitly part of the definition of V2. |
INSERT INTO V3 VALUES(5) | Results in an error because V3 is dependent on V2 which has a CHECK OPTION clause and the inserted row does not conform to the definition of V2. |
INSERT INTO V3 VALUES(200) | Succeeds even though it does not conform to the definition of V3 (V3 does not have the view CHECK OPTION clause specified); it does conform to the definition of V2 (which does have the view CHECK OPTION clause specified). |
WITH LOCAL CHECK OPTION specifies that the search conditions of the following underlying views are checked when a row is inserted or updated:
In contrast, WITH CASCADED CHECK OPTION specifies that the search conditions of all underlying views are checked when a row is inserted or updated.
The difference between CASCADED and LOCAL is best shown by example. Consider the following updatable views where x and y represent either LOCAL or CASCADED:
V1 defined on table T0 V2 defined on V1 WITH x CHECK OPTION V3 defined on V2 V4 defined on V3 WITH y CHECK OPTION V5 defined on V4
The following table describes which views search conditions are checked during an INSERT or UPDATE operation:
View used in INSERT or UPDATE | x = LOCAL
y = LOCAL |
x = CASCADED
y = CASCADED |
x = LOCAL
y = CASCADED |
x = CASCADED
y = LOCAL |
---|---|---|---|---|
V1 | none | none | none | none |
V2 | V2 | V2 V1 | V2 | V2 V1 |
V3 | V2 | V2 V1 | V2 | V2 V1 |
V4 | V4 V2 | V4 V3 V2 V1 | V4 V3 V2 V1 | V4 V2 V1 |
V5 | V4 V2 | V4 V3 V2 V1 | V4 V3 V2 V1 | V4 V2 V1 |
If a record format name is not specified, the format-name is the same as the system-object-name of the view.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.