Select List Clause Syntax | SQL SELECT Statements | Teradata Vantage - Select List Syntax - Advanced SQL Engine - Teradata Database

{ { DISTINCT | ALL | normalize } [ { table_name.] * | column_name }.ALL ] |
  TOP_clause
}
{ * | expr_spec [,...] }

Syntax Elements

DISTINCT

See DISTINCT.

ALL

See ALL.

normalize

See NORMALIZE Clause.

table_name

Name of a table for which all the attributes of all its structured UDT columns are to be returned.

*

All columns of all tables referenced in the FROM clause be returned.

When qualified by table_name, specifies that all columns of table_name only are to be returned.

View columns are explicitly enumerated when views are defined. If a table is changed after a view is defined, those changes will not appear if you perform a SELECT * query.

SELECT * …  is a column operation, projecting all the columns of a table. SELECT COUNT(*)… is a row operation, restricting and counting all the rows of a table, then reporting the cardinality of the table in question. The function of the asterisk differs in the two cases.

Since these derived columns are not actual columns of a table, you must explicitly specify PARTITION or PARTITION#L n, where n ranges from 1 to 62, inclusive, to project the PARTITION or PARTITION#L n columns for the table.

column_name

Name of a structured UDT column for which all its attributes are to be returned; a column in the named query definition.

You can specify a row-level table constraint column in the select list of a SELECT statement, as long as it is not part of an arithmetic expression. The value returned for the column is the coded value for the row-level security constraint from the row.

Columns with a UDT type are valid with some exceptions. See Specifying UDTs in an SQL Request.

You cannot specify LOB columns with NORMALIZE.

.ALL

For structured UDTs only, .ALL specifies that the individual attribute values for a specified table or column name are to be returned.

See .ALL Operator.

TOP_clause

See TOP Clause.

expr_spec

{ expression [ [AS] expression_alias_name ] |
  table_name.*
}

expression

SQL expression, including scalar subqueries and scalar UDFs.

If you specify a scalar UDF, it must return a value expression.

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. See Specifying UDTs in an SQL Request.

expression_alias_name

An alias for the column expression that is derived from expression. You must specify a derived_column_name for self-join operations.

table_name

Name of a table, queue 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.