WITH Modifiers - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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;