SELECT - Teradata Database

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:

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.

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:

Values are not assigned to the host variables specified in the INTO clause.

Values are not assigned to the host variables specified in the INTO clause.

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:

Values are not assigned to the host variables specified in the INTO clause.

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.

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.

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:

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.

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:

AAAA

$60

Thankyou

AAAA

$140

Thankyou

BBBB

$100

Thankyou

CCCC

Intro

CCCC

$200

Thankyou

DDDD

$100

Thankyou

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:

P0

2

1

P1

1

5

P2

3

2

P3

0

4

SO

1

4

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: