A RECURSIVE named query that can refer to itself in the query definition. The named query list consists of at least one nonrecursive, or seed, statement and at least one recursive statement.
- RECURSIVE query_name
- Recursive query name.
- column_name
- Name of a column in the named query definition.
- UNION ALL
- Operator that adds results of iterative operations to the named query.
Seed Statement
The seed statement is a nonrecursive SELECT statement that retrieves row data from other tables to store in the named query.
- DISTINCT
- Only one row is returned from any set of duplicates that result from a given expression list.
- ALL
- Return all rows, including duplicates, in the results of the expression list. This is the default value.
- *
- Return all columns of all tables referenced in the FROM clause of the seed statement.
- expression
- Any valid SQL expression, including scalar UDFs.
- AS
- Optional introduction to expression_alias_name.
- expression_alias_name
- Alias for the expression.
- table_name
- Name of a table, derived table, or view.
- FROM
- Introduction to the names of one or more tables, views, or derived tables from which expression is to be derived.
Single Table
This option enables the FROM clause of the seed statement to specify single tables.
A FROM clause can include a sequence of single table references, which creates an implicit inner join.
A FROM clause in a seed statement cannot specify the name of the WITH RECURSIVE query.
- table_name
- Name of a single table, derived table, table UDF, or view referred to in the FROM clause.
- AS
- Optional introduction to correlation_name.
- correlation_name
- Alias for the table referenced in the FROM clause.
Joined Tables
Options for joined tables enable the FROM clause of the seed statement to specify that multiple tables be joined in explicit ways, as described below.
- joined_table
- Name of a joined table.
- INNER
- Join in which qualifying rows from one table are combined with qualifying rows from another table according to a join condition.
- LEFT OUTER
- Outer join with the table that was listed first in the FROM clause.
- RIGHT OUTER
- Outer join with the table that was listed second in the FROM clause.
- FULL OUTER
- Rows are returned from both tables.
- JOIN
- Introduction to the name of the second table to participate in the join.
- ON search_condition
- One or more conditional expressions that must be satisfied by the result rows.
- CROSS JOIN
- Unconstrained or Cartesian join, returns all rows from all tables specified in the FROM clause.
Derived Tables
The derived table option enables the FROM clause of a seed statement to specify a spool made up of selected data from an underlying table set. The derived table acts like a viewed table.
- (subquery)
- Nested SELECT statements.
- AS
- Optional introduction to derived_table_name.
- derived_table_name
- Name of the derived table.
- column_name
- Column name. This field is for the column name only. Do not use forms such as Tablename.Columnname or Databasename.Tablename.Columnname.
- WHERE
- Introduction to the search condition in the seed statement.
- search_condition
- Conditional search expression that must be satisfied by the row or rows returned by the seed statement.
- GROUP BY
- Introduction to the specification of how to group result rows.
- ordinary_group_set
- Column expression by which the rows returned by the seed statement are grouped.
- empty_grouping_set
- Contiguous LEFT PARENTHESIS, RIGHT PARENTHESIS pair with no argument. You use this syntax to request a grand total of the computed group totals.
- rollup_list
- ROLLUP expression that reports result rows in a single dimension with one or more levels of detail.
- cube_list
- CUBE expression that reports result rows in multiple dimensions with one or more levels of detail.
- grouping_sets_specification
- GROUPING SETS expression that reports result rows in one of
two ways:
- As a single dimension, but without a full ROLLUP.
- As multiple dimensions, but without a full CUBE.
- HAVING
- Introduction to the conditional clause in the SELECT statement.
- search_condition
- One or more conditional expressions that must be satisfied by the result rows.
- QUALIFY
- Introduction to a conditional clause that filters rows from a WHERE clause. The difference between QUALIFY and HAVING is that QUALIFY filtering is based on the result of performing various ordered analytical functions on the data.
- search_condition
- One or more conditional expressions that must be satisfied by the result rows.
- ORDER BY
- Order in which result rows are to be sorted.
- expression
- Expression in the SELECT expression list of the seed statement, either by name or by means of a constant that specifies the numeric position of the expression in the expression list.
- column_name
- Names of columns used in the ORDER BY clause in the SELECT statement. These can be ascending or descending.
- column_name_alias
- Column name alias specified in the select expression list of the query for the column on which the result rows are to be sorted.
- column_position
- Numeric position of the columns specified by the ORDER BY clause. These can be ascending or descending.
- ASC
- Results are to be ordered in ascending sort order.
- DESC
- Results are to be ordered in descending sort order.
Recursive Statement
The recursive statement is a SELECT statement that retrieves row data from a join of the named query and other tables.
You cannot include NORMALIZE in a recursive statement of a recursive query.
- *
- All columns of all tables referenced in the FROM clause of the recursive statement be returned.
- expression
- Any valid SQL expression, with these exceptions:
- Aggregate functions
- Ordered analytical functions
- AS
- Optional introduction to expression_alias_name.
- expression_alias_name
- Alias for the expression.
- table_name
- Name of the named query or the name of a table or view.
- FROM
- Introduction to the named query and one or more tables or views from which expression is to be derived.
Implicit Join
This option enables the FROM clause of the recursive statement to specify the name of the RECURSIVE query and one or more single table references, creating an implicit inner join.
- query_name
- Named query referred to in the FROM clause.
- AS
- Optional introduction to correlation_name.
- correlation_name
- Alias for the query name referenced in the FROM clause.
- table_name
- Name of a single table or view referred to in the FROM clause.
- AS
- Optional introduction to correlation_name.
- correlation_name
- Alias for the table name referenced in the FROM clause.
Explicit Join
Options for joined tables enable the FROM clause of the seed statement to specify that multiple tables be joined in explicit ways, described as follows.
- query_name
- Named query referred to in the FROM clause.
- join_table_name
- Name of a joined table.
- INNER JOIN
- Join in which qualifying rows from one table are combined with qualifying rows from another table according to a join condition.
- LEFT OUTER
- Outer join with the table that was listed first in the FROM clause.
- RIGHT OUTER
- Outer join with the table that was listed second in the FROM clause.
- JOIN
- Introduction to the name of the second table to participate in the join.
- joined_table
- Name of the joined table.
- ON search_condition
- One or more conditional expressions that must be satisfied by the result rows.
WHERE Clause
- WHERE
- Keyword that introduces the search condition in the recursive statement.
- search_condition
- A conditional search expression that must be satisfied by the row or rows returned by the recursive statement.