Three categorical sets of operators, clauses, and other miscellaneous features either cannot be used in the definition of a recursive view, or are restricted in their use in the definition.
- Restrictions defined by the ANSI SQL:2011 SQL standard.
- Additional restrictions defined by Teradata.
- Miscellaneous restrictions.
The first set of feature restrictions is defined by the ANSI SQL:2011 standard. The restrictions defined by ANSI are fundamental to preventing recursive views from violating monotonicity (see The Concept of Recursion).
- The following conditional negation operators are valid within a seed statement.
- NOT IN
- NOT EXISTS
They are not valid when used within a recursive statement.
Negation is forbidden within recursive statements to avoid violations of monotonicity, which is a fundamental requirement of recursive views in SQL.
- Aggregation is valid within a seed statement.
It is not valid when used within a recursive statement.
A LEFT OUTER JOIN is valid when used within a seed statement.
Left outer joins are valid when used 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 when used within a seed statement.
Right outer joins are valid when used 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 when used within a seed statement.
Full outer joins are valid when used 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 1 of its component relations.
In other words, 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.
- Non-linear recursion is not supported (see All Recursive View Definitions Must Be Linear).
- 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 non-recursive 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 when used within any component of a recursive view definition.
- EXCEPT
- EXCEPT ALL
- INTERSECT
- INTERSECT ALL
- UNION
- Subqueries are valid within a seed statement.
They are not valid when used within a recursive statement of a recursive view definition.
- Ordered analytical and window functions are valid within a seed statement.
They are not valid when used within a recursive statement.
The following clauses are valid within a seed statement.- CUBE
- GROUP BY
- GROUPING SETS
- HAVING
- ROLLUP
The HAVING clause of any condition specified within a recursive view definition cannot specify a SAMPLE clause within a predicate subquery.
They are not valid when used within a recursive request.
The DISTINCT operator is valid when used within a seed statement.
It is not valid when used within a recursive request.
- The TOP n and TOP m PERCENT clauses are not valid within a recursive view definition (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 and the CREATE VIEW/REPLACE VIEW statement in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for more information about the TOP n and TOP m PERCENT clauses).
- 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. Teradata Database aborts the request and returns an error message to the requestor.
- To create a recursive view, the CREATE RECURSIVE VIEW statement must refer to its view name within the definition.
If the view definition does not refer to its own name, then the system creates a valid view, but it is not recursive (see Specifying RECURSIVE For a Non-Recursive View 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for more information about the WITH RECURSIVE clause).
- 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.
This is true at all levels of a view hierarchy, so you must be careful not to create a recursive view that references another recursive view at some deeper level of the view hierarchy.