Restrictions Defined by the ANSI SQL:2011 SQL Standard
These restrictions preven recursive views from violating monotonicity (see The Concept of Recursion).
- The following conditional negation operators are valid within a seed statement, but not within a recursive statement:
- NOT IN
- NOT EXISTS
- Aggregation is valid within a seed statement, but not within a recursive statement.
- A LEFT OUTER JOIN is valid within a seed statement.
A LEFT OUTER JOIN is valid within a recursive statement only if the recursive view is the outer, or left, table specified in the outer join.
- A RIGHT OUTER JOIN is valid within a seed statement.
A RIGHT OUTER JOIN is valid within a recursive query only if the recursive view is the outer, or right, table specified in the outer join.
- A FULL OUTER JOIN is valid within a seed statement.
A FULL OUTER JOIN is valid within a recursive statement only if neither the inner nor the outer table in the outer join definition is the recursive view being defined or one of its component relations. That is, a full outer join used within a recursive query cannot cross recursion.
- The ON clause of any join condition specified within a recursive view definition cannot specify a SAMPLE clause within a predicate subquery.
- The following set operators are not valid when used within any component of a recursive view definition:
- EXCEPT ALL
- INTERSECT ALL
- MINUS ALL
- The only valid set operator within a recursive view definition is UNION ALL.
Restrictions Defined by Teradata
- Non-linear recursion is not supported (see General Usage Guidelines for Recursive Views).
- Mutual recursion, the condition in which one recursive view references another recursive view, is not supported.
- The following rules and restrictions apply to the coding of derived tables within a recursive view definition:
- Derived tables are valid within the seed statement of a recursive view definition.
- Derived tables are not valid within the recursive statement of a recursive view definition.
- You cannot code a WITH RECURSIVE or nonrecursive WITH clause within the definition of a derived table.
- You can reference a recursive view within the definition of a derived table.
- The following set operators are not valid within any component of a recursive view definition.
- EXCEPT
- EXCEPT ALL
- INTERSECT
- INTERSECT ALL
- UNION
- Subqueries are valid within a seed statement.
Subqueries are not valid within a recursive statement of a recursive view definition.
- Ordered analytical and window functions are valid within a seed statement.
Ordered analytical and window functions are not valid within a recursive statement.
- The following clauses are valid within a seed statement:
- CUBE
- GROUP BY
- GROUPING SETS
- HAVING
- ROLLUP
The preceding clauses are not valid within a recursive request.
- The HAVING clause of any condition specified within a recursive view definition cannot specify a SAMPLE clause within a predicate subquery.
- The DISTINCT operator is valid within a seed statement.
The DISTINCT operator is not valid within a recursive request.
- The TOP n and TOP m PERCENT clauses are not valid within a recursive view definition (see General Usage Guidelines for CREATE VIEW and REPLACE VIEW and CREATE VIEW and REPLACE VIEW for more information).
- If a recursive view references multiple row-level security-protected tables, but the row-level security definitions are not the same for all of those tables. The system aborts the request and returns an error message to the requestor.
Miscellaneous Restrictions
- To create a recursive view, the CREATE RECURSIVE VIEW statement must refer to its view name within the definition.
Otherwise, the view is valid, but not recursive (see General Usage Guidelines for Recursive Views for details).
- Recursive views are not updatable.
- Because recursive views are not updatable, the WITH CHECK OPTION clause is not valid for recursive view definitions.
- The WITH RECURSIVE clause is not valid when used within any component of a recursive view definition (see General Usage Guidelines for CREATE VIEW and REPLACE VIEW for more information).
- You cannot reference a recursive query or recursive view definition more than once within a FROM clause.
- You cannot reference a non-recursive WITH clause within a CREATE RECURSIVE VIEW definition.
- References to another recursive view are not valid within a recursive view definition, even if the target recursive view is at a deeper level of the view hierarchy.
- Definitions of nonrecursive views cannot refer to recursive views.