WITH Modifier Syntax | SQL SELECT Statements | Teradata Vantage - WITH Modifier Syntax - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™
WITH modifier [,...]

Syntax Elements

modifier
{ query_name [ column_list ] AS ( select_expression ) |
  RECURSIVE recursive_query_name [ column_list ] AS ( seed seed_spec [...] )
}
query_name
Name of the query.
column_list
( column_name [,...] )
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 the transform group has a fromsql routine. The system automatically converts the expression from its UDT value to the external type using the fromsql routine before returning the result to a client application.
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_query_name
Recursive query name.
seed
{ SELECT | SEL } [ DISTINCT | ALL ] { * | expr_spec [,...] }
  FROM { single_table_spec | joined_table_spec | derived_table_spec }
  WHERE search_condition
  [ GROUP BY group_by_spec [,...] ]
  [ { HAVING | QUALIFY } search_condition ]
  [ ORDER BY order_by_spec [,...] ]
The seed statement is a nonrecursive SELECT statement that retrieves row data from other tables to store in the named query.
seed_spec
UNION ALL { seed | recursive_statement }
UNION ALL adds results of iterative operations to the named query.
column_name
Name of a column in the named query definition.
expr_spec
{ expression [ [AS] expression_alias_name ] |
  table_name.*
}
single_table_spec
table_name [ [AS] correlation_name ]
joined_table_spec
joined_table {

  [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ]
    JOIN joined_table ON search_condition |

  CROSS JOIN
}
derived_table_spec
(subquery) [AS] derived_table_name
  [ ( derived_table_column_name [,...] ) ]
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 encoded form.
group_by_spec
{ ordinary_grouping_set |
  empty_grouping_set |
  rollup_list |
  cube_list |
  grouping_sets_specification
}
order_by_spec
{ expression | column_name | column_name_alias | column_position }
  [ ASC | DESC ]
recursive_statement
{ SELECT | SEL } { * | expr_spec [,...] }
  FROM { implicit_join [,...] | explicit_join }
  WHERE search_condition
SELECT statement that retrieves row data from a join of the named query and other tables. It cannot include NORMALIZE, aggregate functions, or ordered analytical functions.
expr_spec
{ expression [ [AS] expression_alias_name ] |
  table_name.*
}
implicit_join
{ query_name | table_name } [ [AS] correlation_name ]
explicit_join
{ { query_name | join_table_name } LEFT [OUTER] JOIN joined_table |
  join_table_name RIGHT [OUTER] JOIN { query_name | joined_table } |
  query_name INNER JOIN joined_table |
  join_table_name INNER JOIN query_name
} ON search_condition
DISTINCT
Only one row is returned from any set of duplicates that 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
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.
When qualified by table_name, return all columns of table_name only.
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 encoded form.
The difference between QUALIFY and HAVING is that QUALIFY filtering is based on the result of performing various ordered analytical functions on the data.
expression
Any valid SQL expression, including scalar UDFs.
expression_alias_name
Alias for the expression.
table_name
Name of a single table, derived table, table UDF, or view.
correlation_name
Alias for table_name.
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.
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 are 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 are returned in the outer join result and extended with nulls.
joined_table
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.
A value you specify for a row-level security constraint in a search condition must be in encoded form.
CROSS JOIN
Unconstrained or Cartesian join, returns all rows from all tables specified in the FROM clause.
In a FULL OUTER JOIN, rows from both tables that have not been returned in the result of the inner join are returned in the outer join result and extended with nulls.
subquery
Nested SELECT statements.
SELECT AND CONSUME statements cannot be used in a subquery.
You can specify NORMALIZE in a subquery.
derived_table_name
Name of the derived table.
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 exceptions.
ordinary_grouping_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
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.
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.
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.
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. 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.
You can specify the sort column by column_position value within the select list for the query.
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.