WITH Modifiers - Teradata Vantage

The rules and restrictions are:

The only set operator that can appear in a recursive named query within a WITH modifier is UNION ALL.
The following elements cannot appear within a WITH or WITH RECURSIVE modifier:
- WITH or WITH RECURSIVE modifier
- TOP n operator
- User-defined functions
The following elements cannot appear within a recursive statement in a WITH RECURSIVE modifier:
- NOT IN or NOT EXISTS logical predicate
- Aggregate functions
- Ordered analytical functions
- GROUP BY clause
- HAVING clause
- DISTINCT clause
- Subqueries
- Derived tables
You cannot specify a WITH modifier in the definitions of any of these database objects:
- Triggers
- Stored procedures

A recursive named query that does not have a recursive statement works like a nonrecursive named query.

This request produces the same results as the request that specifies a nonrecursive named query in the WITH modifier:

     WITH RECURSIVE orderable_items (product_id, quantity) AS (
     SELECT stocked.product_id, stocked.quantity
     FROM stocked, product
     WHERE stocked.product_id = product.product_id
     AND   product.on_hand > 5)
     SELECT product_id, quantity
     FROM orderable_items
     WHERE quantity < 10;