SELECT
Purpose
Returns specific row data in the form of a result table.
For information about syntax that is compatible with temporal tables, see:
Syntax
where:
Syntax Element … |
Specifies … |
request_modifier |
an option that modifies the SELECT statement using one or more of these request or statement modifiers: Request and statement modifiers apply to all statements in a request. |
DISTINCT |
that only one row is to be returned from any set of duplicates that might result from a given expression list. Rows are duplicates only if each value in one is equal to the corresponding value in the other. If you specify DISTINCT and an expand_alias in the select list, Teradata Database performs the DISTINCT operation after the EXPAND operation to ensure that duplicate rows are removed from the expanded result. If you specify DISTINCT but do not specify an expand_alias in the select list, Teradata Database ignores the EXPAND ON clause (even if you specify one), and does not expand rows. You cannot specify DISTINCT if any member of the select column list is a LOB column. |
ALL |
that all rows, including duplicates, are to be returned in the results of the expression list. This is the default value. |
NORMALIZE Clause |
|
NORMALIZE |
that the result of the select is normalized on the first period column in the select list. Period values that meet or overlap are coalesced, that is, combined to form a period that encompasses the individual period values. Any period columns that are specified after the first period column are treated as regular column values. Normalize is the last operation performed on the result of a SELECT statement. You can use a SELECT statement with the normalize clause on a normalized or non-normalized table. A SELECT statement cannot normalize a derived period column. |
ON MEETS OR OVERLAPS |
period values that meet or overlap are to be coalesced, that is, combined to form a period that encompasses the individual period values. |
ON OVERLAPS |
period values that overlap are to be coalesced, that is, combined to form a period that encompasses the individual period values. |
ON OVERLAPS OR MEETS |
period values that overlap or meet are to be coalesced, that is, combined to form a period that encompasses the individual period values. |
table_name |
the name of a table for which all the attributes of all its structured UDT columns are to be returned. |
column_name |
the name of 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” on page 36 for the specific exceptions. You cannot specify LOB columns with NORMALIZE. |
.ALL |
that all attributes for the specified UDT column or for all structured UDT columns in the specified table are to be returned in the results of the expression list. |
TOP n |
that the query return a specified number of rows or a percentage of available rows. For details, see “TOP n Operator” on page 91. You cannot specify NORMALIZE with TOP n. |
integer integer PERCENT |
an INTEGER literal indicating the number of rows to return. If the PERCENT option is specified, the system returns integer% of the total rows available, where 100 ≥ integer > 0. |
decimal decimal PERCENT |
a DECIMAL literal indicating the number of rows to return. If the PERCENT option is specified, the system return decimal% of the total rows available, where 100 ≥ decimal > 0. |
WITH TIES |
that the rows returned by the query include the specified number or percentage of rows in the ordered set produced by the ORDER BY clause, plus any additional rows where the value of the sort key is the same as the value of the sort key in the last row that satisfies the specified number or percentage of rows. The WITH TIES option is ignored if the SELECT statement does not also specify an ORDER BY clause. |
* |
that 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. Note that Since these derived columns are not actual columns of a table, you must explicitly specify PARTITION or PARTITION#Ln, where n ranges from 1 to 62, inclusive, to project the PARTITION or PARTITION#Ln columns for the table. |
expression |
any valid 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” on page 36 for details. |
AS |
an optional introduction to derived_column_name. |
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 |
the 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. |
FROM Clause |
|
FROM |
an optional introduction to the names of one or more tables, queue tables, views, or derived tables from which expression is to be derived. If the TABLE option is specified, the FROM clause invokes the specified table user-defined function that returns row results as a derived table. For details, see “FROM Clause” on page 96. |
Single Table This option allows the FROM clause to specify single tables. A FROM clause can include a sequence of single table references, which creates an implicit inner join; to be distinguished from explicit inner joins in which the keyword JOIN is part of the syntax. |
|
table_name |
the name of a single base table, queue table, derived table, or view referred to in the FROM clause. |
AS |
an optional introduction to correlation_name. |
correlation_name |
an alias for the table referenced in the FROM clause. Correlation names are sometimes referred to as range variables. |
Joined Tables These options enable the FROM clause to specify that multiple tables be joined in explicit ways, described as follows: |
|
joined_table |
the name of a joined table. |
INNER |
a 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 |
an 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 |
an 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 |
that 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 |
an introduction to the name of the second table to participate in the join. |
ON search_condition |
one or more conditional expressions, including scalar subqueries, that must be satisfied by the result rows. You can only specify a scalar UDF for search_condition if it is invoked within an expression and returns a value expression. 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. |
CROSS JOIN |
an unconstrained, or Cartesian join; it returns all rows from all tables specified in the FROM clause. |
single_table |
the name of a single base or derived table or view on a single table to be cross joined with joined_table. |
Derived Tables This option enables the FROM clause to specify a spool file made up of selected data from an underlying table set. The derived table acts like a viewed table. You can invoke a scalar UDF from any point in a derived table. |
|
(subquery) |
nested SELECT expressions. You cannot specify SELECT AND CONSUME statements in a subquery, nor can you specify an EXPAND ON clause in a subquery. You can specify NORMALIZE in a subquery. |
AS |
an optional introductory clause to the derived table name. |
derived_table_name |
the name of the derived table. |
column_name |
the column name. The column_name field is for the column name only; do not use forms such as tablename.columnname or databasename.tablename.columnname. |
Table Function Using this option with the FROM clause enables you to invoke a user-defined table function that returns row results as a derived table. The TABLE option can only be specified once in a FROM clause. The TABLE option cannot be specified in a FROM clause that also specifies the JOIN option. |
|
function_name |
the name of the user-defined table function. |
expression |
any valid SQL expression. If expression references a column from a table or subquery in the FROM clause, the table or subquery must appear before the TABLE option. |
RETURNS database_name.table_ |
the name of the table and its containing database, if different from the current database, into which the rows processed by the dynamic result row table function are to be inserted. |
RETURNS column_name- data_type |
the name and data type of one or more columns to be assigned to the row fields returned by a dynamic result row table function. You cannot use the BLOB AS LOCATOR or CLOB AS LOCATOR forms to specify columns that have a BLOB or CLOB data type. |
UDF Input Ordering Clause This clause enables you to specify the order in which rows are passed to table UDFs. You can specify either hashed ordering or value ordering. |
|
LOCAL ORDER BY column_name |
the column name set by which rows are to be value‑ordered locally on the AMPs before being passed to a table function. See “LOCAL ORDER BY Clause” on page 113 for details. |
HASH BY column_name |
the column name set by which rows are to be hash partitioned across the AMPs before being passed to a table function. See “HASH BY Clause” on page 109 for details. |
AS derived_table_name |
the name of a temporary derived table that other clauses in the SELECT statement can reference. AS is an introductory optional keyword for derived table. |
column_name |
an optional list of column names that other clauses in the SELECT statement can reference. If specified, the number of names in the list must match the number of columns in the RETURNS TABLE clause of the CREATE FUNCTION statement that installed the function_name table function on the Teradata platform. The alternate names list corresponds positionally to the corresponding column names in the RETURNS TABLE clause. If omitted, the names are the same as the column names in the RETURNS TABLE clause of the CREATE FUNCTION statement that installed the function_name table function on the Teradata platform. |
Table Operator Table operators read an input table, perform operations on the table such as partitioning or aggregation, then write output rows. Table operators can accept an arbitrary row format and based on the operation and input row type, they can produce an arbitrary output row format. For more information on the table operators that Teradata provides, see SQL Functions, Operators, Expressions, and Predicates. For more information on creating table operators, see SQL External Routine Programming. |
|
operator_name |
a name for the table operator. This name cannot be a Teradata reserved word. |
ON table_name ON view_name |
the table or view expression that is input to the table operator. |
ON (query_expression) |
the query expression that is input to the table operator. You can have up to 16 ON clauses. You can use multiple ON clauses with all supported languages for protected and non-protected mode table operators. If you use multiple ON clauses, the order of the ON clauses must match the order of the stream numbers in the table operator that you use. You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY. Cogroups are used for table operators with multiple ON clauses. For information about cogroups, see SQL Functions, Operators, Expressions, and Predicates. |
Hash or Partition By Clause |
|
HASH BY column_name |
an optional set of column names on which to hash order globally the columns input to a table function or table operator. When you use a multiple input table operator that has multiple hash by clauses, the following restrictions apply: If you specify a LOCAL ORDER BY clause along with a HASH BY clause, the following restrictions apply: |
PARTITION BY |
to partition for a table specified as input to a table operator. You cannot specify a row-level security constraint column as a partitioning column. You can specify a column by name or position, or use an expression that resolves to a column. Note: Partition keys are important to performance. If the partition keys hash to the same AMP, then some AMPs may be overloaded compared with others. Performance may also degrade if the partition key is a VARCHAR, as VARCHARs may take longer to compare than some other data types. |
PARTITION BY ANY |
Specifies a table with no partitioning or order by attributes. PARTITION BY ANY preserves the existing distribution of the rows on the AMPs. A PARTITION BY ANY clause followed by ORDER BY clause means that all the rows are ordered by the ORDER BY clause on that partition, and it is functionally equivalent to using LOCAL ORDER BY without using a HASH BY clause. The following restrictions apply: |
DIMENSION |
Specifies that a duplicate copy of the dimension table is created for every partition on which the function operates. You can specify zero or more DIMENSION clauses for each ON clause. DIMENSION input is useful when the input table is a small look up table for facts or is a trained model, such as that used for machine learning. For a look up table using DIMENSION, the rows are duplicated to all the AMPs. Each AMP holds one instance of the look up table in memory and uses it to process each row of another input. For machine learning, you can store a model in the database that predicts the outcome of a given set of data and then use the stored model as dimensional input to a function. The following restrictions apply: For information about the SCRIPT table operator, see SQL Functions, Operators, Expressions, and Predicates. |
LOCAL ORDER BY |
that qualified rows are ordered on each AMP in preparation to be input to a table function. See “LOCAL ORDER BY Clause” on page 113. You cannot use a LOCAL ORDER BY clause in addition to a DIMENSION clause in the same ON clause. If you use multiple ON clauses, you cannot specify a LOCAL ORDER BY clause in addition to a PARTITION BY ANY clause in the same ON clause. If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, then all other ON clauses can only specify a DIMENSION clause. If you specify multiple HASH BY clauses with LOCAL ORDER BY clauses, the following restrictions apply: |
ORDER BY |
how result sets are sorted. If you do not use this clause, result rows are returned unsorted. You cannot specify ORDER BY as the only option in an ON clause. You must combine it with a PARTITION BY, PARTITION BY ANY, HASH BY, or DIMENSION clause. If you specify an ORDER BY clause along with a PARTITION BY clause, the following restrictions apply: See “ORDER BY Clause” on page 221. When NORMALIZE and ORDER BY are specified in a query, the rows are normalized first and the result is ordered on the normalized result. |
USING name (value) |
one or more name-value pairs. |
AS |
optional keyword introducing correlation_name. ANSI SQL refers to aliases as correlation names. They are also referred to as range variables. If you have multiple ON clauses, each correlation name used in the query must be unique. |
correlation_name |
an alias for the column that is referenced by column_name. |
column_name |
one or more column names. |
WITH Clause |
|
WITH expression_1 |
an introduction to the condition to be fulfilled by the SELECT statement. Specifies a summary line, such as a total, for the values in a column of the select result. expression_1 can contain one or more aggregate expressions that are applied to column values. You cannot include LOB columns in the WITH expression_1 list. You cannot include a WITH clause with NORMALIZE. |
BY expression_2 |
one or more result expressions for which expression_1 is provided. BY is valid only when used with WITH. expression_2 can refer to an expression in the select expression list either by name or by means of a constant that specifies the numeric position of the expression in the expression list. It can also refer to a scalar subquery. You cannot include LOB columns in the BY expression_2 list. |
ASC |
that the 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. If the BY clause is not specified, at that level, there is no ordering of the result. |
DESC |
that the 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. |
WHERE Clause |
|
WHERE |
an introduction to the search condition in the SELECT statement. |
search_condition |
a conditional search expression that must be satisfied by the row or rows returned by the statement. Scalar subqueries are valid search conditions, but you cannot specify expressions that contain LOBs in a search condition unless you first cast them to another data type (for example, casting a BLOB to BYTE or VARBYTE or a CLOB to CHARACTER or VARCHAR) or pass them to a function whose result is not a LOB. You cannot specify an expression that returns an ARRAY or VARRAY data type. See SQL Functions, Operators, Expressions, and Predicates for more information about relational operators and expressions. You can only specify a scalar UDF for search_condition if it is invoked within an expression and returns a value expression. If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form. |
GROUP BY Clause |
|
GROUP BY ordinary_grouping_set |
a column expression by which the rows returned by the statement are grouped. You cannot specify LOB, ARRAY, or VARRAY columns in a GROUP BY clause. ordinary_grouping_set falls into three general categories: See “Ordinary Grouping Set Expressions” on page 146 for the definitions of these ordinary grouping set expressions. |
empty_grouping_set |
a 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 |
a ROLLUP expression that reports result rows in a single dimension with one or more levels of detail. See “ROLLUP Grouping Set Option” on page 160 for more information. |
cube_list |
a CUBE expression that reports result rows in multiple dimensions with one or more levels of detail. See “CUBE Grouping Set Option” on page 154 for more information. |
grouping_sets_ |
a GROUPING SETS expression that reports result rows in one of two ways: See “GROUPING SETS Option” on page 157 for more information. |
HAVING Clause |
|
HAVING |
an introduction to the conditional clause in the SELECT statement. |
conditional_expression |
one or more conditional expressions that must be satisfied by the result rows. HAVING condition filters rows from a single group defined in the select expression list that has only aggregate results, or it filters rows from the group or groups defined in a GROUP BY clause. A GROUP BY expression is a column expression by which the rows returned by the statement are grouped. You can specify aggregate operators and scalar subqueries as conditional expressions with HAVING. You cannot specify LOB columns in the HAVING conditional expression list. If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form. |
QUALIFY Clause |
|
QUALIFY |
an 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 with QUALIFY the 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. You can specify ordered analytical functions and scalar subqueries as search conditions with QUALIFY. You cannot specify LOB columns in the QUALIFY search condition list. If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form. |
SAMPLE Clause |
|
SAMPLE |
an introduction to a clause that permits sampling of rows in the SELECT statement. |
WITH REPLACEMENT |
whether sampling is done by returning each sampled row to the table for possible redundant sampling or by withholding sampled rows from resampling. If you specify WITH REPLACEMENT, then it is possible to request more samples than there are rows in the table. Sampling without replacement is the default. You select it implicitly by not specifying WITH REPLACEMENT. |
RANDOMIZED ALLOCATION |
whether rows are sampled randomly across AMPS (RANDOMIZED ALLOCATION) or proportionate to the number of qualified rows per AMP (proportional allocation). The proportional allocation option does not provide a simple random sample of the entire population. It provides a random sample stratified by AMPs, but it is much faster, especially for very large samples. Proportional is the default. You select it implicitly by not specifying RANDOMIZED ALLOCATION. |
fraction_description |
one (or many) floating point fractional constants in the closed interval (0,1). The sum of the fraction_description values specified cannot exceed 1. |
count_description |
a positive integer constant list of row counts. |
WHEN |
to test a set of conditions for truth. |
condition |
an evaluation predicate that defines each homogeneous subgroup in the sample set. You can only specify a scalar UDF for condition if it is invoked within an expression and returns a value expression. You cannot specify expressions that contain LOBs in a search condition unless you first cast them to another type or pass them to a function whose result is not a LOB. For example, cast a BLOB to BYTE or VARBYTE or cast a CLOB to CHARACTER or VARCHAR. If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form. |
THEN |
to apply the specified sampling fraction description or count description to the sample. |
ELSE |
to apply the specified sampling fraction description or count description to the sample if none of the WHEN condition predicates evaluates to TRUE. |
END |
the termination of the WHEN … THEN … ELSE clause. |
EXPAND ON Clause |
|
EXPAND ON expand_expression |
the time series expansion information in a query expression. The EXPAND ON clause generates a regular time series as a sequence of values at each of the granules or at each point of a predefined interval in the specified period from an input row. The expand_expression variable specifies a Period column name or Period expression on which the selected rows are to be expanded. If the expansion period is null, then the expanded row has a null expanded value. The specified column or expression must have a Period data type. |
expand_column_alias AS expand_column_alias |
the aliased name of the Period expression to be expanded. The aliased column or expression must have a Period data type. You can reference expand_column_alias in the select list, including inside an expression that does not reference any other columns and refers to the expanded value for an expanded row. You cannot specify expand_column_alias in other clauses within the same query block, except for an ORDER BY clause. You cannot specify expand_column_alias in any clauses in a subquery or correlated subquery, nor can you specify it in any aggregate or statistical function in the select list. |
BY expand_interval |
the interval literal (interval expression) or anchored literal by which expanded_column_name is to be expanded, where expand_interval is one of the options in this list. If you do not specify the BY expand_interval option, then the expansion interval defaults to the granularity of the element type of the Period value for the column. The expansion commences from the beginning value of the expansion period and terminates at the ending value of the expansion period, incrementing by interval_literal for each expanded row. In this process, the last expanded row might not cover the expansion interval duration, producing only a partial period. By definition, a partial period is a period with a duration that is less than the expanded interval. See “EXPAND ON Clause” on page 187 for details about the valid expansion intervals and anchor names. |
FOR period_expression |
an optional Period expression. For a comprehensive list of valid Period expressions, see SQL Data Types and Literals. The purpose of specifying a period expression is to limit the number of rows to be expanded to period_expression, where period_expression represents the period of interest. The expansion period is the overlapping period of the qualified row and the Period constant you specify as period_expression. Otherwise, the expanding period is the Period value of the selected row. The data type of period_expression must be comparable with the Period data type of the expanded column. If the specified period expression is either null or does not overlap with the row, then Teradata Database does not expand the row because it does not qualify for expansion. |
ORDER BY Clause |
|
ORDER BY |
the order in which result rows are to be sorted. If ORDER BY is not specified, rows are returned unsorted. |
ORDER BY expression |
an expression in the SELECT expression list, either by name, or by means of a constant, that specifies the numeric position of the expression in the expression list. If the sort field is a character string, the expression used in the ORDER BY phrase can include a type modifier to force the sort to be either CASESPECIFIC or NOT CASESPECIFIC. You can specify scalar subqueries and scalar UDFs, but you cannot specify LOB, ARRAY, or VARRAY columns in the ORDER BY expression list. |
ORDER BY column_name |
the names of columns used in the ORDER BY clause in the SELECT statement. These can be ascending or descending. You cannot include LOB, ARRAY, or VARRAY columns in the ORDER BY column list. |
ORDER BY column_name_alias |
a 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” on page 223. |
ORDER BY column_position |
the numeric position of the columns specified by the select list. These can be ascending or descending. The value you specify cannot be specified using a parameter and must be a positive constant integer literal with a value between 1 and the number of columns specified in the select list, inclusive. Note that Teradata Database treats macro and procedure parameters as expressions, not as the specification of a column position. This is a Teradata extension to the ANSI SQL:2011 standard. |
ASC |
that the 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 |
that the 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. |
NULLS FIRST |
that NULL results are to be listed first. |
NULLS LAST |
that NULL results are to be listed last. |
WITH Clause (see “WITH Clause” on page 24) |
ANSI Compliance
SELECT is ANSI SQL:2011-compliant with extensions.
In Teradata SQL, the FROM clause is optional. This is a Teradata extension to the ANSI SQL:2011 standard, in which a FROM clause is mandatory.
The WITH, SAMPLE, QUALIFY, and TOP clauses are Teradata extensions to the ANSI SQL:2011 standard.
The documented syntax specifies the ANSI SQL:2011 ordering of these clauses: FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Teradata Database does not enforce this ordering, but you should observe it when you write applications to maintain ANSI compliance.
Required Privileges
To select data from a table, you must have the SELECT privilege on the table or column set being retrieved.
To select data through a view, you must have the SELECT privilege on that view. Also, the immediate owner of the view (that is, the database or user in which the view resides) must have SELECT WITH GRANT OPTION privileges on all tables or views referenced in the view.
If indirect references are made to a table or view, then the privileges must be held by the immediate owner of the object being accessed rather than the user executing the query.
See SQL Data Control Language and Database Administration for more information.
Uses of the SELECT Statement
SELECT is the most frequently used SQL statement. Use SELECT statements to specify any of the following:
Specify fully qualified names in SELECT statements that reference objects in multiple databases and users. Name resolution problems can occur when databases and users referenced in a query contain tables or views with identical names, and those objects are not fully qualified. Name resolution problems can occur even if the identically named objects are not referenced in the query.
SELECT Subqueries
A subquery is a SELECT expression that is nested within another SQL statement or expression. You can specify SELECT expressions as subqueries in a main query, an outer query, or another subquery for these DML statements:
The upsert form of UPDATE does not support subqueries as WHERE clause conditions. See “UPDATE (Upsert Form)” on page 515 for more information about the upsert form of the UPDATE statement.
The syntax rules require that if there are any subqueries in the statement, then any tables referenced in the SELECT must be specified in a FROM clause, either of the subquery itself or of some outer query that contains the subquery.
Correlated subqueries are another category of subquery. A subquery is correlated when it references columns of outer tables in an enclosing or containing, outer query. Correlated subqueries provide an implicit loop function within any standard SQL DML statement.
See “Correlated Subqueries” on page 134 for details on correlated subqueries and their use.
Also see “Example 2: SELECT Statements With a Correlated Subquery” on page 40 for simple examples of correlated subqueries.
Scalar subqueries are another category of subquery that can be coded either as a correlated subquery or as a noncorrelated subquery. A correlated scalar subquery returns a single value for each row of its correlated outer table set. A noncorrelated scalar subquery returns a single value to its containing query. For information about scalar subqueries, see “Scalar Subqueries” on page 142.
Locks and Concurrency
A DML SELECT sets a default READ lock on the tables or rows referenced in the SELECT statement, depending on session isolation level (refer to the table, below) and the locking level the Lock Manager imposes or the locking level you specify using the LOCKING modifier. See “LOCKING Request Modifier” on page 414. If the SELECT statement references a view, then the system places a READ lock on each of its underlying tables.
Note: This topic does not apply to DDL statements such as CREATE JOIN INDEX and CREATE VIEW that nest SELECT subqueries in their object definition statements.
For outer SELECT statements and SELECT subqueries that are not nested within a DML statement that manipulates data using a DELETE, INSERT, MERGE, or UPDATE statement, the default locking severity is always READ whether the session transaction isolation level is SERIALIZABLE.
If the session transaction isolation level is READ UNCOMMITTED and the DBS Control flag AccessLockForUncomRead is set to TRUE, then the default locking level for SELECT operations depends on depends on the factors explained in the following paragraph and table.
For SELECT subqueries that are nested within a DML statement that manipulates data using a DELETE, INSERT, MERGE, or UPDATE statement, the default locking severity is ACCESS when the DBS Control flag AccessLockForUncomRead is set TRUE and the transaction isolation level for the session is READ UNCOMMITTED, as the following table indicates.
IF the transaction isolation level is … |
AND the DBS Control AccessLockForUncomRead flag is set … |
THEN the default locking severity for outer SELECT and ordinary SELECT subquery operations is … |
AND the default locking severity for SELECT operations embedded within a DELETE, INSERT, MERGE, or UPDATE statement is … |
SERIALIZABLE |
FALSE |
READ |
READ |
TRUE |
READ |
||
READ UNCOMMITTED
|
FALSE |
READ |
|
TRUE |
ACCESS |
When a SELECT statement does not specify a LOCKING modifier, but a view it uses to access tables does, Teradata Database places the lock specified by the view and does not comply with the defaults described in the preceding table.
For example, suppose you create this view.
CREATE VIEW access_view (a, b) AS
LOCKING TABLE accntrec FOR ACCESS
SELECT accntno, qualifyaccnt
FROM accntrec
WHERE qualifyaccnt = 1587;
This SELECT statement places an ACCESS lock on table accntrec regardless of the specified session isolation level and setting of the AccessLockForUncomRead flag.
SELECT a, b
FROM access_view;
For details, see “SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL” in SQL Data Definition Language.
Derived Tables
A derived table is a transient table that is created dynamically from one or more tables by evaluating a query expression or table expression specified as a subquery within a SELECT statement. You must specify a correlation name for each derived table you create.
The semantics of derived tables and views are identical. For details, see “CREATE VIEW” in SQL Data Definition Language.
NoPI Tables, Column‑Partitioned Tables, and SELECT Statements
The rules and restrictions are:
Because NoPI and column‑partitioned tables have no primary index, there is no single‑AMP primary index access available, so access must be by means of a full-table scan, unless:
Secondary and join index access paths work the same for NoPI and column‑partitioned tables as they do for primary index tables.
SELECT Statements in Embedded SQL
The rules and restrictions are:
Status Parameter |
Assigned Value |
SQLSTATE |
‘02000’ |
SQLCODE |
+100 |
Values are not assigned to the host variables specified in the INTO clause.
Status Parameter |
Assigned Value |
SQLSTATE |
‘21000’ |
SQLCODE |
-811 |
Values are not assigned to the host variables specified in the INTO clause.
Status Parameter |
Assigned Value |
SQLSTATE |
‘22509’ |
SQLCODE |
-303 |
SQLSTATE |
‘22003’ |
SQLCODE |
-304 |
SQLSTATE |
‘22003’ |
SQLCODE |
-413 |
Values are not assigned to the host variables specified in the INTO clause.
If no corresponding indicator host variable is specified, then Teradata Database assigns these values to the status parameters:
Status Parameter |
Assigned Value |
SQLSTATE |
‘22002’ |
SQLCODE |
-305 |
Values are not assigned to the host variables specified in the INTO clause.
Status Parameter |
Assigned Value |
SQLSTATE |
‘00000’ |
SQLCODE |
0 |
DEFAULT Function in SELECT Statements
The rules and restrictions are:
The resulting data type of the DEFAULT function is the data type of the constant or built‑in function specified as the default unless the default is NULL. If the default is NULL, the resulting date type of the DEFAULT function is the same as the data type of the column or expression for which the default is being requested.
DEFAULT
or DEFAULT(
column_name)
. When column name is not specified, the system derives the column based on context.
If the column context cannot be derived, the system returns an error.
When you specify a SELECT statement that does not also specify a FROM clause, the system always returns only a single row with the default value of the column, regardless of how many rows are in the table. This is similar to the behavior of the TYPE function.
For More Information
For more specific information about using DEFAULT within the SELECT statement, see:
Specifying UDTs in an SQL Request
The rules and restrictions are:
Invoking a Scalar UDF From a SELECT Statement
You must use the alias to reference the result of a scalar UDF invocation that has an alias.
Returning a Varying Column Table External UDF Result
The rules and restrictions are:
The system processes table functions like derived tables.
However, when you reference a table function that outputs a dynamic number of columns, you must specify either an output column list or the name of an existing table as part of the function invocation using the RETURNS … table_name option in the FROM clause of the SELECT request.
IF you specify … |
THEN the system uses the columns … |
an outcome column name list |
specified in the list as the function output columns. |
the name of an existing table |
from the definition for that table as the function output columns. |
In either case, the number of columns specified cannot exceed the defined maximum number of output columns specified by the VARYING COLUMNS maximum_output_columns specification of its CREATE FUNCTION (Table Form) definition.
See “CREATE FUNCTION (Table Form)” in SQL Data Definition Language.
For examples of both forms returning the same result, see “Example 13: Dynamic Row Results Returned by Specifying Table Functions” on page 45.
SELECT Statements and Derived Period Columns
A SELECT * on a table with a derived period column returns the two DATE or TIMESTAMP columns used to define the derived period column, not the derived period column. The two DATE or TIMESTAMP columns appear as regular columns in a nontemporal table.
You can only specify derived period columns with period predicate operators, begin and end bound functions, duration functions, comparison operators, logical predicate operators, and in the EXPAND clause. For example, you cannot specify derived period columns in WITH or HAVING clauses. You cannot specify derived period columns in functions, macros, or stored procedures such as P_INTERSECT, P_NORMALIZE, LDIFF, or RDIFF, except in a WHERE clause.
You cannot specify derived period columns in the select list.
SELECT and INSERT-SELECT Statements with Set Operators
Only the SELECT and INSERT-SELECT SQL statements can use the set operators UNION, INTERSECT, and MINUS/EXCEPT. The set operators enable you to manipulate the answers to two or more queries by combining the results of each query into a single result set.
The set operators can also be used within these operations:
You cannot use the set operators with LOB columns.
Joins
A SELECT statement can reference data in one or more sets of tables and views, including a mix of tables and views. The SELECT statement can define a join of tables or views.
You can join tables and views that have row‑level security constraints, as long as the tables and views being joined have identical row‑level security constraints. Otherwise, the system returns an error.
For more information about join operations, see “Chapter 2 Join Expressions” on page 243.
For information about how the Optimizer processes joins, see SQL Request and Transaction Processing.
Activity Count Indicates the Number of Rows Returned
When the result of a SELECT statement is returned, the activity count success response indicates the number of rows returned. If no rows are returned, then the activity count is 0.
SELECT and Queue Tables
A queue table is similar to an ordinary base table, with the additional unique property of being similar to an asynchronous first-in-first-out (FIFO) queue.
The first column of a queue table always contains Queue Insertion TimeStamp (QITS) values. The QITS value of a row indicates the time the row was inserted into the queue table, unless a different, user-supplied value is inserted.
The CREATE TABLE statement for the table must define the first column with the following data type and attributes:
TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
You can use SELECT statements to perform a FIFO peek on a queue table. In this case, data is returned from the queue table without deleting any of its rows.
If no rows are available, no rows are returned. The transaction does not enter a delay state.
To return the queue in FIFO order, specify the first column of the queue table in the ORDER BY clause of the SELECT statement.
To perform a FIFO pop on a queue table, use a SELECT AND CONSUME statement. For more information, see “SELECT AND CONSUME” on page 55.
Note: A SELECT AND CONSUME statement cannot specify a scalar subquery.
For more information about how to define a queue table, see “CREATE TABLE (Queue Table Form)” in SQL Data Definition Language.
Logging Problematic Queries
The recommended best practice for logging problematic DML requests (queries) is to specify XMLPLAN logging whenever you submit the queries. The XMLPLAN option captures (logs) the query plan generated by the Optimizer as an XML document that can be used by Teradata support tools to diagnose performance issues with the query. The query plan is stored in the DBC.DBQLXMLTbl system table.
For More Information
See "Using BEGIN QUERY LOGGING to Log Query Plan Information" in SQL Data Definition Language Detailed Topics for information on:
Example : Miscellaneous SELECT Statements
The following SELECT statements are syntactically correct in Teradata SQL. The first statement does not reference any tables, so it does not require a FROM clause. This syntax is a Teradata extension to the ANSI SQL:2011 standard.
SELECT DATE, TIME;
SELECT x1,x2
FROM t1,t2
WHERE t1.x1=t2.x2;
SELECT x1
FROM t1
WHERE x1 IN (SELECT x2
FROM t2);
Example : SELECT Statements With a Correlated Subquery
Use a correlated subquery to return the names of the employees with the highest salary in each department.
SELECT name
FROM personnel p
WHERE salary = (SELECT MAX(salary)
FROM personnel sp
WHERE p.department=sp.department);
Use a correlated subquery to return the names of publishers without books in the library.
SELECT pubname
FROM publisher
WHERE 0 = (SELECT COUNT(*)
FROM book
WHERE book.pubnum=publisher.pubnum);
Example : SELECT Statements With Scalar Subqueries in Expressions and as Arguments to Built‑In Functions
You can specify a scalar subquery in the same way that you would specify a column or constant to compose an expression or as an argument to a system function. You can specify an expression that is composed of a scalar subquery wherever an expression is allowed in a DML statement.
Following are examples of the types of expressions that you can code using scalar subqueries:
SELECT (fix_cost + (SELECT SUM(part_cost)
FROM parts)) AS total_cost, …
SELECT (SELECT prod_name
FROM prod_table AS p
WHERE p.pno = s.pno) || store_no …
SELECT CASE WHEN (SELECT count(*)
FROM inventory
WHERE inventory.pno = orders.pno) > 0
THEN 1
ELSE 0
END, …
SELECT SUM(SELECT count(*)
FROM sales
WHERE sales.txn_no = receipts.txn_no), …
… WHERE txn_no IN (1,2, (SELECT MAX(txn_no)
FROM sales
WHERE sale_date = CURRENT_DATE));
Example : SELECT Statements That Specify a System‑Derived PARTITION Column In Their Select Lists
You can specify a system‑derived PARTITION column in the select list of a statement. This example specifies an unqualified PARTITION column because its use is unambiguous:
SELECT orders.*, PARTITION
FROM orders
WHERE orders.PARTITION = 10
AND orders.o_totalprice > 100;
PARTITION must be qualified in this SELECT statement in the select list and in the WHERE clause because it joins two tables and specifies the PARTITION columns of both in its WHERE clause:
SELECT orders.*, lineitem.*, orders.PARTITION
FROM orders, lineitem
WHERE orders.PARTITION = 3
AND lineitem.PARTITION = 5
AND orders.o_orderkey = lineitem.1_orderkey;
You must specify PARTITION explicitly in the select list. If you specify * only, then PARTITION or PARTITION#Ln column information is not returned.
Usually, the table would be a row-partitioned table because:
Example : PARTITION Values Not Returned Because PARTITION Not Specified in the Select List
In this example, the value of PARTITION is not returned as one of the column values, even though it is specified in the WHERE clause, because it was not explicitly specified in the select list for the query:
SELECT *
FROM orders
WHERE orders.PARTITION = 10
AND orders.o_totalprice > 19.99;
Example : Qualification of PARTITION Not Necessary Because Specification Is Unambiguous
PARTITION does not have to be qualified in this example because its use is unambiguous:
SELECT orders.*, PARTITION
FROM orders
WHERE orders.PARTITION = 10
AND orders.o_totalprice > 100;
Example : Qualification of PARTITION Necessary to Avoid Ambiguity
PARTITION must be qualified in the two following examples to distinguish between PARTITION values in the orders table and PARTITION values in the lineitem table:
SELECT *
FROM orders, lineitem
WHERE orders.PARTITION = 3
AND lineitem.PARTITION = 5
AND orders.o_orderkey = lineitem.l_orderkey;
SELECT orders.*, lineitem.*, orders.PARTITION
FROM orders, lineitem
WHERE orders.PARTITION = 3
AND lineitem.PARTITION = 5
AND orders.o_orderkey = lineitem.l_orderkey;
Example : Selecting All Active Partitions From a Table
The following two examples provide a list of the populated row partitions in the orders table (assuming the maximum combined partition number for a populated row partition is 999 or 127, respectively, for the orders table):
SELECT DISTINCT PARTITION (FORMAT '999')
FROM orders
ORDER BY PARTITION;
SELECT DISTINCT CAST (PARTITION AS BYTEINT)
FROM orders
ORDER BY PARTITION;
Example : Using PARTITION With An Aggregate Function
The following example counts the number of rows in each populated row partition:
SELECT PARTITION, COUNT(*)
FROM orders
GROUP BY PARTITION
ORDER BY PARTITION;
Example : SELECT With Queue Tables
The following statement measures the queue depth of a queue table named shopping_cart:
SELECT COUNT(*)
FROM shopping_cart;
Assuming the column named cart_qits contains QITS values, this statement returns the shopping_cart queue table in FIFO order:
SELECT *
FROM shopping_cart
ORDER BY cart_qits;
Example : Specifying UDTs in the SELECT List and WHERE Clause of SELECT Statements
The following set of simple examples shows the valid use of UDT expressions in a SELECT statement:
SELECT euro_column
FROM t1;
SELECT euro_column.roundup(0)
FROM t1;
SELECT address_column.street(), address_column.zip()
FROM t2;
SELECT t.address_column.ALL
FROM t2 AS t;
SELECT address_column
FROM t2
WHERE address_column.zip() = '92127';
SELECT *
FROM t3
WHERE circle_column = NEW circle(1.34, 4.56, 10.3);
SELECT circle_column.area()
FROM t3;
The following example shows the need to cast the distinct UDT column named myDollar to compare it with a DECIMAL value.
SELECT *
FROM t4
WHERE myDollar < (CAST 3.20 AS DollarUDT);
Example : SELECT Statements Specifying the DEFAULT Function
You can specify the DEFAULT function with a column name in the select projection list. The DEFAULT function evaluates to the default value of the specified column.
Assume this table definition for the examples below:
CREATE TABLE table14 (
col1 INTEGER,
col2 INTEGER DEFAULT 10,
col3 INTEGER DEFAULT 20,
col4 CHARACTER(60)
col5 TIMESTAMP DEFAULT NULL,
col6 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
The following query returns the default value of col2 and col3 of table14 in all the resulting rows. Note that this is an inefficient form to get the default value of a column, because the system does a full table scan to perform the query. A more efficient method to get the default value of a column is to specify the query without a FROM clause.
SELECT DEFAULT(col2), DEFAULT(col3)
FROM table14;
Assuming there are four rows in table14, the returned row set is as follows:
DEFAULT(col2) DEFAULT(col3)
------------ ------------
10 20
10 20
10 20
10 20
If there are no rows in the table, then no rows are returned.
The following example returns the default value of col2 from table14. This helps discover the default value of a particular column. This is an inefficient form to get the default value of a column, because the system performs a full table scan to perform the query. A more efficient method to get the default value of a column is to specify the query without a FROM clause.
SELECT DISTINCT DEFAULT(col2)
FROM Table14;
The returned row set is as follows:
DEFAULT(col2) DEFAULT(col3)
----------------------- --------------
10 20
If there are no rows in the table, then no rows are returned.
When this SELECT statement does not specify a FROM clause, the system always returns a single row with the default value of the column, regardless of how many rows are in the table. This behavior is similar to the TYPE function.
SELECT DEFAULT(table14.col2);
The resulting row set is as follows:
DEFAULT(col2)
-------------
10
When the column passed as an input argument to the DEFAULT function does not have an explicit default value associated with it, the DEFAULT function evaluates to null.
Assume this table definition for the examples below:
CREATE TABLE table15 (
col1 INTEGER ,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL DEFAULT NULL,
col4 INTEGER CHECK (col4 > 10) DEFAULT 9);
Because col1 does not have an explicit default value, the DEFAULT function evaluates to null.
SELECT DEFAULT(Table15.Col1);
The resulting row set is as follows:
DEFAULT(col1)
-------------
?
Because col2 does not have an explicit default value, the DEFAULT function evaluates to null. The function returns null even though the NOT NULL constraint on the column does not permit nulls in the column.
SELECT DEFAULT(table15.col2);
The resulting row set is as follows:
DEFAULT(col2)
-------------
?
Because col3 has an explicit default value of null, the DEFAULT function evaluates to its explicit default value. The function returns null even though the NOT NULL constraint on the column does not permit nulls in the column.
SELECT DEFAULT(table15.col3);
The resulting row set is as follows:
DEFAULT(col3)
-------------
?
Even though col4 has a default value 9, which violates the CHECK constraint col4>10
, the DEFAULT function returns the value 9 even though check constraint does not permit
this value in the column.
SELECT DEFAULT(Table15.Col4);
The resulting row set is as follows:
DEFAULT(Col4)
-------------
9
Example : Dynamic Row Results Returned by Specifying Table Functions
The following example uses the extract_store_data table function to return dynamic row results in the store_data table:
SELECT *
FROM (TABLE(extract_store_data('…', 1000)
RETURNS store_data) AS store_sales;
The following equivalent examples use the sales_retrieve table function to return dynamic row results either by specifying the maximum number of output columns and their individual column names and data types or by specifying only the name of the table into which the converted rows are to be written.
SELECT *
FROM TABLE (sales_retrieve(9005)
RETURNS (store INTEGER,
item INTEGER,
quantity INTEGER)) AS s;
SELECT *
FROM TABLE (sales_retrieve(9005)
RETURNS sales_table) AS s;
Example : Scalar Subquery in the Select List of a SELECT Statement
You can specify a scalar subquery as a column expression or parameterized value in the select list of a query. You can assign an alias to a scalar subquery defined in the select list, thus enabling the rest of the query to reference the subquery by that alias.
The following example specifies a scalar subquery (SELECT AVG(price)…
) in its select list with an alias (avgprice
) and then refers to it in the WHERE clause predicate (t2.price < avgprice
).
SELECT category, title,(SELECT AVG(price)
FROM movie_titles AS t1
WHERE t1.category=t2.category) AS avgprice
FROM movie_titles AS t2
WHERE t2.price < avgprice;
See these topics for additional examples of specifying scalar subqueries in SELECT statements:
Example : SQL UDF in the Select List of a SELECT Statement
You can specify an SQL UDF in the select list of an SQL request. As if true of other expressions in a select list, you can also alias an SQL UDF. In this request, the aliased value expression cve is specified as cve in the select list and in the WHERE clause.
SELECT test.value_expression(t1.a1, t2.as) AS cve, cve+1
FROM t1, t2
WHERE t1.b1 = t2.b2
AND cve = 5;
Example : Creating a Time Series Using Expansion By an Interval Constant Value
Suppose you create a table named employee with this definition.
CREATE SET TABLE employee (
emp_id INTEGER,
emp_name CHARACTER(20) CHARACTER SET LATIN NOT CASESPECIFIC,
job_duration PERIOD(DATE))
PRIMARY INDEX (emp_id);
You insert 3 rows into employee so its contents are as follows:
employee |
|
|
emp_id |
emp_name |
job_duration |
1001
|
Xavier
|
2002-01-10, 9999-12-31
|
1002
|
Ricci
|
2007-07-01, 9999-12-31
|
1003
|
Charles
|
2006-02-10, 2008-06-01
|
When you specify an EXPAND ON clause using an interval constant, Teradata Database expands each row based on the specified interval value, and the duration in the expanded period is the interval value.
So you now use an EXPAND ON clause to retrieve the employee details specifying an interval constant period.
SELECT emp_id, emp_name, job_duration AS tsp
FROM employee
EXPAND ON job_duration AS tsp BY INTERVAL '1' YEAR
FOR PERIOD(DATE '2006-01-01', DATE '2008-01-01');
Teradata Database returns employee details for each year of a given period, as you specified in the FOR clause of the SELECT statement.
Teradata Database returns a warning to the requestor that some rows in the expanded result might have an expanded period duration that is less than the duration of the specified interval.
|
|
|
|
|
|
|
|
2006-02-10, 2008-06-01 |
2006-02-10, 2007-02-10 |
|
|
|
2006-02-10, 2008-06-01 |
2007-02-10, 2008-01-01 |
|
|
|
2007-07-01, 9999-12-31 |
2007-07-01, 2008-01-01 |
|
|
|
2002-01-10, 9999-12-31 |
2006-01-01, 2007-01-01 |
|
|
|
2002-01-10, 9999-12-31 |
2007-01-01, 2008-01-01 |
See “EXPAND ON Clause” on page 187 for additional examples of using the EXPAND ON clause in SELECT statements.
Example : Invoking an SQL UDF From a Derived Table
The first example invokes the SQL UDF value_expression in the select list of the derived table dt.
SELECT *
FROM (SELECT a1, test.value_expression(3,4), c1
FROM t1
WHERE a1>b1) AS dt (a,b,c);
The next example invokes the SQL UDF value_expression in the WHERE clause of the derived table dt.
SELECT *
FROM (SELECT a1, b1, c1
FROM t1
WHERE test.value_expression(b1,c1)>10) AS dt (a,b,c);
Example : Invoking a UDF or Method Using a RETURNS Specification
The table definitions used in this example are:
CREATE TABLE t1 (
int_col INTEGER,
var_char_col VARCHAR(40) CHARACTER SET UNICODE);
CREATE TABLE t2 (
int_col INTEGER,
decimal_col DECIMAL (10, 6));
These function definitions are used in this example to identify the TD_ANYTYPE parameters.
CREATE FUNCTION udf_1(
A INTEGER,
B TD_ANYTYPE)
RETURNS TD_ANYTYPE;
CREATE FUNCTION udf_3(
A INTEGER,
B TD_ANYTYPE)
RETURNS TD_ANYTYPE;
The following example invokes udf_1 using t1.int_col and t2.decimal_col as parameters and DECIMAL(10,6) as the explicitly specified return data type.
SELECT (udf_1 (t1.int_col, t2.decimal_col)
RETURNS DECIMAL(10,6));
The following example invokes udf_3 using t1.var_char_col and t2.decimal_col as parameters and VARCHAR(40) as the explicitly specified return data type.
SELECT (udf_3 (t1.var_char_col, t2.decimal_col)
RETURNS VARCHAR(40) CHARACTER SET LATIN);
The final example invokes method_2 using t2.decimal_col as a parameter and DECIMAL(10,6) as the explicitly specified return data type.
SELECT udt_col.(method_2(t2.decimal_col)
RETURNS DECIMAL(10,6));
Example : Invoking a UDF or Method Using a RETURNS STYLE Specification
This example uses the table and function definitions from “Example 18: Invoking a UDF or Method Using a RETURNS Specification.”
The following example invokes udf_2 using t1.var_char_col and t2.decimal_col as parameters and DECIMAL(10,6) as the implicitly specified return data type because the data type for column t2.decimal_col is DECIMAL(10,6).
SELECT (udf_2 (t1.var_char_col, t2.decimal_col)
RETURNS STYLE t2.decimal_col);
The following example invokes udf_3 using t1.var_char_col and t2.decimal_col as parameters and VARCHAR(40) CHARACTER SET UNICODE as the implicitly specified return data type because the data type for column t1.var_char_col is VARCHAR(40) CHARACTER SET UNICODE.
SELECT (udf_3 (t1.var_char_col, t2.decimal_col)
RETURNS STYLE t1.var_char_col);
The final example invokes method_2 using t2.decimal_col as a parameter and DECIMAL(10,6) as the implicitly specified return data type because the data type for column t2.decimal_col is DECIMAL(10,6).
SELECT udt_col.(method_2(t2.decimal_col)
RETURNS STYLE t2.decimal_col);
Example : Selecting Rows From a Table With Row‑Level Security Protection
This example shows how you can specify a row‑level security constraint column name in the select list or WHERE clause of a SELECT statement.
First, define the row-level security constraint. The create text for the group_membership constraint object looks like this.
CREATE CONSTRAINT group_membership SMALLINT, NOT NULL,
VALUES (exec:100, manager:90, clerk:50, peon:25),
INSERT SYSLIB.ins_grp_member,
SELECT SYSLIB.rd_grp_member;
In this table definition, Teradata Database implicitly adds a row-level security constraint column named group_membership to the emp_record table when it creates that table. The group_membership column contains the data for the row‑level security constraint.
CREATE TABLE emp_record (
emp_name VARCHAR(30),
emp_number INTEGER,
salary INTEGER,
group_membership CONSTRAINT)
UNIQUE PRIMARY INDEX(emp_name);
After you create emp_record, you can use a SELECT statement that retrieves the specified data from the group_membership column by specifying the name of that column in the select list for the query.
Following is an example of a SELECT statement on table emp_record that includes group_membership in the select list.
SELECT emp_name, group_membership
FROM emp_record
WHERE group_membership=90;
If you have the required security credentials, this query returns the emp_name and group_membership value name and value code for all managers. If you do not have the required credentials, no rows are returned. You cannot specify a value name as the search condition. You must specify a value code. In this example, the value code 90 represents the value name manager.
Suppose you then create this row‑level security constraints and inventory table.
CREATE CONSTRAINT classification_level SMALLINT, NOT NULL,
VALUES (top_secret:4, secret:3, confidential:2, unclassified:1),
INSERT SYSLIB.InsertLevel,
UPDATE SYSLIB.UpdateLevel,
DELETE SYSLIB.DeleteLevel,
SELECT SYSLIB.ReadLevel;
CREATE CONSTRAINT classification_category BYTE(8)
VALUES (nato:1, united_states:2, canada:3, united_kingdom:4,
france:5, norway:6, russia:7),
INSERT SYSLIB.insert_category,
UPDATE SYSLIB.update_category,
DELETE SYSLIB.delete_category,
SELECT SYSLIB.read_category;
CREATE TABLE inventory (
column_1 INTEGER,
column_2 INTEGER,
column_3 VARCHAR(30),
classification_level CONSTRAINT,
classification_category CONSTRAINT)
PRIMARY INDEX(column_1);
User joe then logs onto a Teradata Database session. The create text for joe looks like this.
CREATE USER joe AS
PERMANENT=1e6,
PASSWORD=Joe1234,
CONSTRAINT = classfication_level (top_secret),
CONSTRAINT = classification_category (united_states);
Because user joe is defined with the classification_level and classification_category row-level security constraints, he can execute this SELECT statement on inventory.
SELECT *
FROM inventory
WHERE column_1 = 1212;
The result set looks something like this, returning not only the data from the three columns explicitly defined for inventory, but also the data from the two row‑level security columns.
column_1 column_2 column_3 classification_level classification_category
-------- -------- -------- -------------------- -----------------------
1212 90505 Widgets 3 '4000000000000000'xb
Example : Application of Row-Level Security SELECT Constraint When User Lacks Required Privileges (SELECT Statement)
This example show how the SELECT constraint is applied when a user that does not have the required OVERRIDE privileges attempts to execute a SELECT statement on a table that has the row-level security SELECT constraint.
An EXPLAIN modifier is used with the SELECT statement to show the steps involved in the execution of the request and the outcome of the application of the constraint.
Table Definition
The statement used to create the table in this example is:
CREATE TABLE rls_tbl(
col1 INT,
col2 INT,
classification_levels CONSTRAINT,
classification_categories CONSTRAINT);
User’s Session Constraint Values
The user’s sessions constraint values are:
Constraint1Name LEVELS
Constraint1Value 2
Constraint3Name CATEGORIES
Constraint3Value '90000000'xb
EXPLAIN Request Modifier
This EXPLAIN request modifier is used to show the steps involved in the execution of the SELECT statement and the outcome of the application of the constraint.
EXPLAIN SELECT * FROM rls_tbl;
EXPLAIN Text
The system returns this EXPLAIN text.
*** Help information returned. 14 rows.
*** Total elapsed time was 1 second.
Explanation
------------------------------------------------------------------------
1) First, we lock a distinct RS."pseudo table" for read on a RowHash
to prevent global deadlock for RS.rls_tbl.
2) Next, we lock RS.rls_tbl for read.
3) We do an all-AMPs RETRIEVE step from RS.rls_tbl by way of an
all-rows scan with a condition of ("((SYSLIB.SELECTCATEGORIES (
'90000000'XB, RS.rls_tbl.categories ))= 'T') AND
((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T')") into Spool 1
(all_amps), which is built locally on the AMPs. The size of Spool
1 is estimated with no confidence to be 1 row (57 bytes). The
estimated time for this step is 0.03 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1: The total estimated time is 0.03 seconds.
Example : Using a Table Operator with Multiple PARTITION BY Inputs
The following example shows one way that you can use table operator functionality with multiple PARTITION BY inputs.
Suppose that you have the following tables:
WebLog |
||
cookie |
cart_amount |
page |
AAAA
|
$60
|
Thankyou
|
AAAA
|
$140
|
Thankyou
|
BBBB
|
$100
|
Thankyou
|
CCCC
|
|
Intro
|
CCCC
|
$200
|
Thankyou
|
DDDD
|
$100
|
Thankyou
|
AdLog |
||
cookie |
ad_name |
action |
AAAA
|
Champs
|
Impression
|
AAAA
|
Puppies
|
Click
|
BBBB
|
Apples
|
Click
|
CCCC
|
Baseball
|
Impression
|
CCCC
|
Apples
|
Click
|
In this example, the operator attribute_sales returns the amount of sales revenue that is attributed to online ads. The inputs to the operator are sales information from the store’s web logs and logs from the ad server. Both the input tables are partitioned on the user’s browser cookie. The operator also accepts two custom clauses, Clicks and Impressions, which supply the percentages of sales to attribute to ad clicks and the views that lead to a purchase, respectively.
The syntax is as follows:
SELECT adname, cart_amt
FROM attribute_sales (
ON (SELECT cookie, cart_amt FROM weblog WHERE page = 'thankyou' )
as W PARTITION BY cookie
ON adlog as S PARTITION BY cookie
USING clicks(.8) impressions(.2)) AS D1(adname,attr_revenue) ;
The output looks similar to the following:
|
ad_name
--------
|
|
attr_revenue
------------
|
|
Champs
|
|
$40
|
|
Puppies
|
|
$160
|
|
Apples
|
|
$240
|
|
Baseball
|
|
$40
|
Example : Using a Multiple Input Table Operator with DIMENSION Input
In this example, the table operator closest_store finds the closest retail store when a purchase is made over a cell phone. The operator takes the following input data sets:
pid |
x_coordinate |
y_coordinate |
P0
|
2
|
1
|
P1
|
1
|
5
|
P2
|
3
|
2
|
P3
|
0
|
4
|
sid |
x_coordinate |
y_coordinate |
SO
|
1
|
4
|
S1 |
2
|
3
|
The SELECT syntax is as follows:
SELECT pid, sid
FROM closest_store (
ON phone_purchases PARTITION BY pid,
ON stores DIMENSION) AS D;
The output looks similar to the following:
|
pid
---
|
|
sid
---
|
|
P0
|
|
S1
|
|
P1
|
|
S0
|
|
P2
|
|
$S1
|
|
P3
|
|
$SO
|
You can also make the table operator call using PARTITION BY ANY, which would keep the existing distribution of the rows on the AMPs. The SELECT syntax is as follows:
SELECT pid, sid
FROM closest_store (
ON phone_purchases PARTITION BY ANY,
ON stores DIMENSION) AS D;
For More Information
For more information on using SELECT statements, see: