Common Table Expression Enhancements - Teradata Database - Teradata Vantage NewSQL Engine

You can use a WITH clause to define one or more common table expressions (CTEs) in derived table and view definitions. CTEs are also referred to as named queries.

Previously, when a WITH clause defined multiple CTEs, a CTE could only reference a subsequent CTE in the WITH clause. Now, a CTE can reference a preceding or subsequent CTE in the WITH clause.

Benefits

CREATE VIEW statements and derived table definitions can include a WITH clause with backward and forward CTE references.

Considerations

A CTE cannot indirectly reference itself, that is, circular references are not allowed.

A recursive query cannot reference another recursive query, either directly or indirectly.

If you mix nonrecursive queries with recursive queries in a WITH statement modifier, you must specify all forward CTE references or all backward CTE references. You cannot mix forward CTE references and backward CTE references.

Additional Information