Example: Various 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 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:
- Arithmetic expressions
SELECT (fix_cost + (SELECT SUM(part_cost) FROM parts)) AS total_cost, …
- String expressions
SELECT (SELECT prod_name FROM prod_table AS p WHERE p.pno = s.pno) || store_no …
- CASE expressions
SELECT CASE WHEN (SELECT count(*) FROM inventory WHERE inventory.pno = orders.pno) > 0 THEN 1 ELSE 0 END, …
- Aggregate expressions
SELECT SUM(SELECT count(*) FROM sales WHERE sales.txn_no = receipts.txn_no), …
- Value list expressions
… WHERE txn_no IN (1,2, (SELECT MAX(txn_no) FROM sales WHERE sale_date = CURRENT_DATE));
Example: Partition Lock for a Row-Partitioned Table
In this example, the query retrieves rows from table slppit1 with a WHERE condition on the partitioning column that qualifies a single partition. An all-AMPs partition lock is used to lock the single partition being accessed and a proxy lock is used to serialize the placement of the all-AMPs partition range lock.
The table definition for this example is as follows:
CREATE TABLE HLSDS.SLPPIT1 (PI INT, PC INT, X INT, Y INT)
PRIMARY INDEX (PI)
PARTITION BY (RANGE_N(PC BETWEEN 1 AND 10 EACH 1));
An EXPLAIN of the SELECT statement shows the partition lock:
Explain SELECT * FROM HLSDS.SLPPIT1 WHERE PC = 5;
1) First, we lock HLSDS.slppit1 for read on a reserved RowHash in a single partition to prevent global deadlock.
2) Next, we lock HLSDS.slppit1 for read on a single partition.
3) We do an all-AMPs RETRIEVE step from a single partition of HLSDS.slppit1 with a condition of ("HLSDS.slppit1.Pc = 5") with a residual condition of ("HLSDS.slppit1.Pc = 5") into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 1 row (65 bytes). The estimated time for this step is 0.07 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
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#L n column information is not returned.
Usually, the table would be a row-partitioned table because:
- For a nonpartitioned table, PARTITION is always 0.
- For a column-partitioned table without row partitioning, PARTITION is always 1.
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.
|
emp_id
------ |
emp_name
-------- |
job_duration
------------ |
tsp
--- |
|
|---|---|---|---|---|
| 1003 | Charles | 2006-02-10, 2008-06-01 | 2006-02-10, 2007-02-10 | |
| 1003 | Charles | 2006-02-10, 2008-06-01 | 2007-02-10, 2008-01-01 | |
| 1002 | Ricci | 2007-07-01, 9999-12-31 | 2007-07-01, 2008-01-01 | |
| 1001 | Xavier | 2002-01-10, 9999-12-31 | 2006-01-01, 2007-01-01 | |
| 1001 | Xavier | 2002-01-10, 9999-12-31 | 2007-01-01, 2008-01-01 |
For additional examples of using the EXPAND ON clause in SELECT statements, see “EXPAND ON Clause”.
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: 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 joethen logs onto a Teradata Database session. The create text for joelooks like this.
CREATE USER joe AS PERMANENT=1e6, PASSWORD=Joe1234, CONSTRAINT = classfication_level (top_secret), CONSTRAINT = classification_category (united_states);
Because user joeis 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: Row-Level Security Constraint and SELECT Statement When User Lacks Required Privileges
This example shows 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:
SELECT * FROM rls_tbl;
SELECT statement processing includes a step to RETRIEVE from RS.rls_tbl with a condition of SYSLIB.SELECTCATEGORIES ( '90000000'XB, RS.rls_tbl.categories ) AND SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels).
The table definition used in this example is:
CREATE TABLE rls_tbl( col1 INT, col2 INT, classification_levels CONSTRAINT, classification_categories CONSTRAINT);
The user’s session constraint values are:
Constraint1Name LEVELS Constraint1Value 2 Constraint3Name CATEGORIES Constraint3Value '90000000'xb
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:
- The phone_purchases table, which contains entries for purchases made over a cell phone, along with normalized spatial coordinates of the phone when the online purchase was made.
- The stores table, which contains the location of all the retail stores and their associated normalized spatial coordinates. The stores table is a smaller fact table that is provided as DIMENSION input.
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;