17.00 - selection - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

You can delimit the SELECT clause with parentheses. If you do, you must specify both opening and closing parentheses or the system aborts the request.

You can invoke an SQL UDF at any point of the SELECT clause of a view definition. The rules are the same as those for a SELECT request.

See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for complete documentation of the SELECT clause.

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 each corresponding value in the other.
You cannot specify a DISTINCT operator if the view definition also includes a TOP n or TOP m PERCENT specification.
ALL
All rows, including duplicates, are to be returned in the results of the expression list.
This is the default value.
TOP
Returns the top-ranked rows to from the SELECT operation.
You cannot specify a TOP n operator if the view definition also specifies any of the following:
  • DISTINCT operator
  • SAMPLE clause
  • QUALIFY clause
  • Subquery, that is, a subquery within a view definition cannot contain a TOP operator.
Views that specify a TOP operator are not updatable.
You can also specify an ORDER BY clause with the TOP operator. Otherwise, ORDER BY clauses are not valid within view definitions.
The sort expression for the ORDER BY clause cannot contain BLOBs, CLOBs, UDTs, columns with Period or Geospatial types, or ordered analytic functions.
TOP performs as well or better as the ordered analytic functions QUALIFY RANK() or QUALIFY ROW_NUMBER() that can be used in a similar fashion.
n
A DECIMAL or an INTEGER value for the number of rows to return from the SELECT operation.
Note that this can be more limiting than the TOP n clause you can specify with DML requests, depending on whether you specify an ORDER BY clause or not. If you do not specify an ORDER BY clause, then a TOP n clause only specifies that any n base table rows be returned, not the TOP n.
m PERCENT
Returns only m percent of rows from the SELECT operation, where m can be either a DECIMAL or an INTEGER value.
If ( m *COUNT(*)/100.0) does not evaluate to an integer value, then the system rounds it up to the next highest integer value.
For example, if ( m *COUNT(*)/100.0) evaluates to 0.1, then it is rounded up to 1.
If ( m *COUNT(*)/100.0) evaluates to 9.2, then it is rounded up to 10.
Vantage only applies this rounding up to the first 15 digits following the decimal point. For example, if ( m *COUNT(*)/100.0) evaluates to 0.0000000000000001, then the system does not round it up to 1.
WITH TIES
All qualified rows having the same value for the ORDER BY fields are to be included in the results set.
This option only works if you also specify an ORDER BY clause; otherwise, the system ignores it.
If you do not specify the WITH TIES option, then the system returns only the first n or m PERCENT qualified rows.
For example, suppose you specify TOP 2 WITH TIES and ORDER BY OrderValue. If the top customer for this field has an OrderValue of 300,000 USD, but there are two additional customers with the same next highest OrderValue of 295,000 USD, then all 3 are returned in the result set.
If you did not specify WITH TIES, then only the first of the two customers encountered in the result list having an OrderValue of 295,000 USD would be returned.
*
All columns from all tables referenced in the FROM clause are to be returned.
When qualified by table_name, * specifies that all columns of only the user base table or view specified by table_name are to be returned.
View columns are explicitly enumerated when views are defined; therefore, if a table is changed after a view is defined, those changes will not appear if the SELECT * construct is used.

sub_selection

A valid SQL expression.

You can specify both aggregate and arithmetic operators in a view definition expression.

You cannot specify a SAMPLE clause if the view definition also includes a TOP n or TOP m PERCENT specification.

The presence of aggregates in a view definition renders that view non-updatable.

expression_alias
A temporary name for the expression.
Alias names are used to name expressions and must be always be specified for a self-join operation on the table.