Syntax Elements - Teradata Database

Nonrecursive Form

The WITH statement modifier is similar to a derived table. It defines a named query list from which the SELECT statement can select data.

query_name: Name of the nonrecursive query.
column_name: Name of a column in the named query definition.
select_expression: Nonrecursive SELECT statement that retrieves the row data to store in the named query.; You can specify an expression that returns a UDT in a column list only if its transform group has a fromsql routine. The system automatically converts the expression from its UDT value to the external type via the fromsql routine before returning it to a client application. For details, see Specifying UDTs in an SQL Request.; You can specify a row-level security constraint column in the select list of a SELECT request. However, the column cannot be specified as part of an arithmetic expression in the select list. The value returned for the column is the coded value for the row-level security constraint from the row.

Recursive Form

The WITH RECURSIVE statement modifier defines a named query that can refer to itself in the query definition and in the SELECT statement that it precedes. The named query list consists of at least one nonrecursive, or seed , statement and at least one recursive statement.

You can invoke a scalar UDF within the WITH RECURSIVE definition of a recursive query. The rules are the same as those for invoking a scalar UDF within a SELECT request.

query_name: Name of the recursive query.
column_name: Name of a column in the named query definition.
UNION ALL: Operator that adds results of iterative operations to the named query.; Because UNION ALL permits duplicate rows, it is not a true relational operator.

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 to be returned from any set of duplicates that might result from a given expression list.; Two rows are considered duplicates only if each value in one is equal to the corresponding value in the other.
ALL: All rows, including duplicates, are to be returned in the results of the expression list. This is the default value.
*: All columns of all tables referenced in the FROM clause of the seed statement be returned.; When qualified by table_name, specifies that all columns of table_name only are to be returned.
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.; table_name.* in the select list can define the table from which rows are to be returned when two or more tables are referenced in the FROM clause.
FROM: Introduction to the names of one or more tables, views, or derived tables from which expression is to be derived.; The FROM clause in a seed statement cannot specify the TABLE option.

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 some join condition.; Inner join is the default join type.
LEFT OUTER: Outer join with the table that was listed first in the FROM clause.; In a LEFT OUTER JOIN, the rows from the left table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls.
RIGHT OUTER: Outer join with the table that was listed second in the FROM clause.; In a RIGHT OUTER JOIN, the rows from the right table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls.
FULL OUTER: rows be returned from both tables.; In a FULL OUTER JOIN, rows from both tables that have not been returned in the result of the inner join will be returned in the outer join result and extended with nulls.
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.; An ON condition clause is required if the FROM clause specifies an outer join.; If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form.
CROSS JOIN: Unconstrained or Cartesian join; it 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.

SELECT AND CONSUME statements cannot be used in a subquery.

You can specify NORMALIZE in a subquery.

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.

Columns with a UDT type are valid with some exceptions. For the specific exceptions, see Specifying UDTs in an SQL Request.

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.

If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.

GROUP BY

Iintroduction 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.

The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type.

ordinary_grouping_set falls into three general categories:

column_name
column_position
column_expression

See Ordinary Grouping Set Expressions for definitions.

empty_grouping_set

Contiguous LEFT PARENTHESIS, RIGHT PARENTHESIS pair with no argument. This syntax is used 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.

The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type.

cube_list

CUBE expression that reports result rows in multiple dimensions with one or more levels of detail.

The expression cannot group result rows that have a LOB, ARRAY, or VARRAY type.

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.

If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.

QUALIFY

Introduction to a conditional clause that, similar to HAVING, further filters rows from a WHERE clause. The major 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.

If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.

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. See SELECT.

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.

If you specify a column_name_alias to sort by, then that alias cannot match the name of any column that is defined in the table definition for any table referenced in the FROM clause of the query whether that column is specified in the select list or not. This does not work because the system always references the underlying physical column having the name rather than the column that you attempt to reference using that same name as its alias.

If you attempt to specify such a column_name_alias, the system aborts the request and returns an error.

The workaround for this is to specify the sort column by its column_position value within the select list for the query. See Rules and Restrictions for the ORDER BY Clause.

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.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

Results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

table_name

Name of the named query or the name of a table or view.

table_name.* in the select list can define the table from which rows are to be returned when two or more tables are referenced in the FROM clause.

FROM

Introduction to the named query and one or more tables or views from which expression is to be derived.

The FROM clause in a recursive statement cannot specify the TABLE option.

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.

When qualified by table_name, specifies that all columns of table_name only are to 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.

table_name.* in the select list can define the table from which rows are to be returned when two or more tables are referenced in the FROM clause.

FROM

Introduction to the named query and one or more tables or views from which expression is to be derived.

The FROM clause in a recursive statement cannot specify the TABLE option.

Implicit Join

This option enables the FROM clause of the recursive statement to specify the name of the WITH 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 in which qualifying rows from one table are combined with qualifying rows from another table according to some join condition.; Inner join is the default join type.
LEFT OUTER: Outer join with the table that was listed first in the FROM clause.; In a LEFT OUTER JOIN, the rows from the left table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls.
RIGHT OUTER: Outer join with the table that was listed second in the FROM clause.; In a RIGHT OUTER JOIN, the rows from the right table that are not returned in the result of the inner join of the two tables are returned in the outer join result and extended with nulls.
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.; If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form.; An ON condition clause is required if the FROM clause specifies an outer join.

WHERE Clause

WHERE: An introduction to 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.; If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form.