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;