Subquery Options - Teradata Database - Teradata Vantage NewSQL Engine
SQL Data Manipulation Language
Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™
Introduction to SQL Data Manipulation Language
Overview
Changes and Additions
Select Statements
Overview
SELECT
Syntax Elements
Select List
ANSI Compliance
Usage Notes
Uses of the SELECT Statement
SELECT Subqueries
Locks and Concurrency
Derived Tables
NoPI Tables and SELECT Statements
SELECT Statements in Embedded SQL
DEFAULT Function in SELECT Statements
Specifying UDTs in an SQL Request
Invoking a Scalar UDF From a SELECT Statement
Returning a Varying Column Table External UDF Result
SELECT Statements and Derived Period Columns
SELECT and INSERT-SELECT Statements with Set Operators
Joins
Activity Count Indicates the Number of Rows Returned
SELECT and Queue Tables
Logging Problematic Queries
Examples
Examples: SELECT Statements
Examples: SELECT Statements with a Correlated Subquery
Example: SELECT Statements With Scalar Subqueries in Expressions and as Arguments to Built-In Functions
Examples: SELECT and PARTITION
Example: SELECT With Queue Tables
Example: Specifying UDTs in the SELECT List and WHERE Clause of SELECT Statements
Example: SELECT Statements Specifying the DEFAULT Function
Example: Dynamic Row Results Returned by Specifying Table Functions
Example: Scalar Subquery in the Select List of a SELECT Statement
Example: SQL UDF in the Select List of a SELECT Statement
Example: Creating a Time Series Using Expansion By an Interval Constant Value
Example: Invoking an SQL UDF From a Derived Table
Example: Invoking a UDF or Method Using a RETURNS Specification
Example: Invoking a UDF or Method Using a RETURNS STYLE Specification
Example: Selecting Rows From a Table With Row-Level Security Protection
Example: Row-Level Security Constraint and SELECT Statement When User Lacks Required Privileges
Example: Using a Table Operator with Multiple PARTITION BY Inputs
Example: Using a Multiple Input Table Operator with DIMENSION Input
Example: Retrieving Data from a DATASET Column in CSV Format
Related Topics
SELECT AND CONSUME
Usage Notes
Locking and Concurrency
Transaction Processing Semantics
FIFO Operations and SELECT AND CONSUME
Rules and Restrictions for SELECT AND CONSUME Statements
Performance Characteristics of SELECT AND CONSUME Statements
Examples
Example: Returning the Row Having the Oldest QITS Value in a Queue Table
Related Topics
SELECT … INTO
Request Modifier
WITH Modifier
Syntax Elements
Nonrecursive Form
Recursive Form
ANSI Compliance
Usage Notes
Nonrecursive Named Query in a WITH Modifier
RECURSIVE Named Query in a WITH Modifier
WITH Modifiers
Rules and Restrictions for Embedded SQL
Using a WITH Statement Result as Input to a Table Function in the FROM Clause
Depth Control to Avoid Infinite Recursion
Examples
Example: Common Table Expression
Example: Recursive Common Table Expressions in a WITH Modifier
Example: Multiple Seed and Recursive Statements
Example: CTE Result Set as Input to a Table Function
Example: Specifying a Dynamic UDT in a Recursive Query
Examples: External UDFs
Example: Invoking an SQL UDF in a Recursive Query
Related Topics
WITH DELETED ROWS
AS JSON
Usage Notes
Rules for Using SELECT AS JSON
Examples
Examples: SELECT AS JSON
Distinct
Usage Notes
Examples
ALL
.ALL Operator
NORMALIZE
Usage Notes
Using SELECT with NORMALIZE
INSERT... SELECT and NORMALIZE
Examples
Example: Using NORMALIZE
TOP n
Usage Notes
What the TOP n Operator Does
Rules and Restrictions for the TOP n Operator
Evaluation Order of TOP n in a SELECT
TOP n Operator Out Performs QUALIFY RANK and QUALIFY ROW_NUMBER
Examples
Example: Comparing the Results of Specifying TOP n * and TOP n WITH TIES *
Related Topics
FROM Clause
Syntax Elements
FROM Clause
Single Table
Joined Tables
ANSI Compliance
Usage Notes
Fully-qualified Names and the FROM Clause
Subqueries and the FROM Clause
Self-join Operations and the FROM Clause
Examples
Example: FROM Clause Used for a Self-Join
Example: FROM Clause Left Outer Join
Related Topics
Table Function
Syntax Elements
Table Function Name
RETURNS Clause
LOCAL ORDER BY Clause
HASH BY Clause
AS Derived Table Name
Examples
Example: Specifying a TABLE Function in the FROM Clause
Example: Hash Ordering Input Parameters to a Table Function
Example: HASH BY and LOCAL ORDER BY Clauses in the Same Statement
Example: Local Ordering of Input Parameters to a Table Function
Table Operator
Syntax Elements
Table Operator Name
Function Mapping Name
ON Clause
EXECUTE MAP Clause
Output Table Clause
USING Custom Clause
AS Correlation Name
Usage Notes
Executing a Function Mapping
Function Mapping and the ON Clause
Function Mapping and the USING Clause
Examples
Examples: Table Operator Function Mapping
Example: SELECT and the Function Mapping Definition
Examples: Function Processing with Variable Substitution
Example: Function Processing with Variable Substitution for Input Tables and Parameters
Example: Function Processing with a Concatenated Variable Expression
Examples: Function Processing with Default Values for Variables
Example: Function Processing with Nested Variables
Example: Function Processing with a Scalar Subquery
Example: Function Processing with Input Table Variable Substitution
Example: Function Processing with Any Input Table Substitution
Derived Tables
Usage Notes
Uses for Derived Tables
Rules and Restrictions for Derived Tables
Examples
Example: Using Derived Tables To Do Multilevel Aggregation
WHERE Clause
Usage Notes
Aggregates in a WHERE Clause
Expressions Containing LOBs
UDT Comparisons
ARRAY or VARRAY Data Type
Row-Level Security Constraint Values
SELECT AND CONSUME Subqueries
DEFAULT Function in a Search Condition
DEFAULT Function Data Type
DEFAULT Function Column Name
DEFAULT Function Comparison Operators
AND and OR Logical Operators
Using WHERE to Filter Character Data
Scalar UDFs
Scalar Subqueries
SAMPLE Clause in a Subquery
WHERE Clause Defines Condition for Joining Table Rows
EXISTS Quantifier
Specifying a Column PARTITION or PARTITION#L n
Use Consistent Predicate Domains
Join Efficiency and Indexes
NoPI Tables, Column-Partitioned Tables, and WHERE Clause Search Conditions
Unconstrained Joins
Examples
Example: Simple WHERE Clause Predicate
Example: Using the table_name.* Syntax
Example: Delete from a Row-Partitioned Table Using the System-Defined PARTITION Column in the Search Condition
Example: INSERT … SELECT and DELETE Operations from a Row-Partitioned Table Using the PARTITION Column as a Search Condition
Example: DEFAULT Function in a WHERE Clause Search Condition
Example: Scalar Subquery in the WHERE Clause of a SELECT Statement
Example: Invoking an SQL UDF Within a Search Condition
Example: Specifying a Row-Level Security Constraint in a Search Condition
Specifying Subqueries in Search Conditions
Usage Notes
Specifying Subqueries
Examples
Example: Simple Subquery Using the IN Logical Predicate
Example: Simple Subquery Using an AVG Aggregate Expression
Example: Simple Subquery Using the ALL Logical Predicate
Example: Subquery Using the SUM and COUNT Aggregate Functions
Example: Subqueries Using the RANK Functions and QUALIFY Clauses
Correlated Subqueries
Usage Notes
Using Correlated Subqueries
EXISTS Quantifier and Correlated Subqueries
Outer References in Correlated Subqueries
Guidelines for Coding Noncorrelated Subqueries
Comparing Correlated and Noncorrelated Subqueries
Examples
Example: Combining Equality and Inequality Conditions in a Correlated Subquery
Example: Using SELECT COUNT(*) in a Correlated Subquery
Scalar Subqueries
GROUP BY Clause
Usage Notes
GROUP BY Clause Terminology
Ordinary Grouping Set Expressions
GROUP BY and Aggregate Operations
GROUP BY and Nonaggregate Operations
GROUP BY and DISTINCT
Comparing GROUP BY and Correlated Subqueries Using a Scalar UDF
GROUP BY and Built-in Ordered Analytic Functions
BLOB or CLOB Columns Not Allowed in a GROUP BY Expression
GROUP BY and Recursive Queries
ORDER BY and GROUP BY
WHERE, GROUP BY, and HAVING Clause Evaluation
Reason for Unexpected Row Length Errors: Sorting Rows for Grouping
How Teradata Database Resolves Multiple Grouping Sets Specifications
Example: Semantically Identical Grouping Sets Specifications and Their Resolution
Examples
Example: Simple GROUP BY Operation
Example: Specifying a GROUP BY Clause on Nonaggregate Expressions When the Select List Includes an Aggregate
Example: Specifying GROUP BY Using an Ordered Analytic Function
Example: SELECT Statement With a Scalar Subquery in Its GROUP BY Clause
Example: GROUP BY and PERIOD Value Expressions
Related Topics
CUBE Grouping Set Option
GROUPING SETS Option
ROLLUP Grouping Set Option
Examples
HAVING Clause
Syntax Elements
HAVING
search_condition
ANSI Compliance
Usage Notes
LOB Columns and the HAVING Clause
Aggregates and the HAVING Clause
Recursive Queries and the HAVING Clause
Row-level Security Constraints and the HAVING Clause
Scalar Subqueries and the HAVING Clause
DEFAULT Function and the HAVING Clause
SAMPLE Clause and the HAVING Clause
Evaluation Order of WHERE, GROUP BY, and HAVING Clauses
Tables References in a HAVING Clause
Aggregating a Join In a HAVING Clause
Examples
Examples: HAVING Clause
Example: Grouping Departments by Average Salaries
Example: Using a HAVING Clause to Aggregate a Join
Related Topics
QUALIFY Clause
Usage Notes
Rules and Restrictions for the QUALIFY Clause
Evaluation Order of WHERE, GROUP BY, and QUALIFY Clauses
QUALIFY Error Conditions
Examples
Example: Using the RANK Function in a QUALIFY Clause
Example: Reporting the Bottom Percentile of Items Using QUANTILE in a QUALIFY Clause
Example: Behavior of OLAP Aggregate Functions That Return Zeros
SAMPLE Clause
Usage Notes, SAMPLE Clause
Examples: Sample Clause
SAMPLEID Expression
EXPAND ON Clause
Syntax Elements
ANSI Compliance
Usage Notes
Time Series, Dense Representations, and Sparse Representations of Temporal Data
Rules and Restrictions for the EXPAND ON Clause
Rules and Restrictions for Interval Expansion
Anchor Period and Anchor Point Expansion
Rules and Restrictions for Anchor Point Expansion of the EXPAND ON Clause
Uses of Anchor Period Expansions
Anchored Interval Uses System-defined Business Calendar Set in the Session
Anchor Names
Anchor Results Can Differ Between the ISO Calendar and the Teradata or COMPATIBLE Calendars
Expand Does Not Consider Exceptions
Expand Interval Literal Expressions
Anchor Name Literal Expressions
WEEK_BEGIN Anchor Name and WeekStart Anchor Expansion
Examples
Example: Expansion on an Anchor Point Using WEEK_DAY
Example: Expansion on an Anchor Point Using ANCHOR_SECOND
Example: Expansion Over a UNION Operator
Example: EXPAND ON MONTH_BEGIN and MONTH_END
Example: EXPAND ON and DISTINCT
Example: Same Expansion in Two Different Sessions Using Different Time Zone Intervals
Example: EXPAND ON and OLAP Functions
Example: Same Expansion in Two Different Sessions in Different Time Zones
Example: EXPAND ON With a Join
Example: EXPAND ON For an Anchored Interval
Example: EXPAND ON and Span Grouping
Example: EXPAND ON for a Moving Average
Example: EXPAND ON for a WEEK_BEGIN Anchor Point
Example: EXPAND ON for a QUARTER_BEGIN Anchor Period
Example: Join Before Expansion
Example: Nullified EXPAND Operation
Example: Null Expansion Period Producing a Null Expanded Value
ORDER BY Clause
Usage Notes
Column References and ORDER BY
WITH and ORDER BY
PARTITION BY, HASH BY, and ORDER BY
Sorting and Default Sort Order Row Length Errors
Unexpected Sort Order When Querying DATE Data Type Columns
Specifying Collation
Japanese Character Sort Order Considerations
International Sort Orders
European Sort Order
Examples
Example: Ordering on a Column Name
Example: Ordering on a Column Name Not Specified in the Select List
Example: Ordering on Column Position
Example: Ordering on a Column Name Alias
Example: Ordering on an Expression
Example: Ordering on Column Name or Column Position Using an Ascending or Descending Sort Sequence
Example: Effects of Default and User-Specified Case Sensitivity on the Ordering of a Result Set
Example: Ordering By a Column Alias Name
Example: Ordering on a PERIOD Value Expression
Related Topics
WITH Clause
Set Operators
Overview
Teradata SQL Set Operators
Syntax for query_term
SELECT statement
query_expression
Syntax for query_factor
INTERSECT
ALL
Syntax for query_expression
UNION, MINUS, EXCEPT
ALL
ORDER BY
expression
ASC
DESC
NULLS FIRST
NULLS LAST
ANSI Compliance
Related Topics
Rules for Set Operators
Duplicate Rows
Operations That Support Set Operators
SELECT AND CONSUME Statement
Support for ORDER BY Clause
Restrictions on the Data Types Involved in Set Operations
Related Topics
Precedence of Set Operators
Example: Select Statement
Using Parentheses to Customize Precedence
Related Topics
Retaining Duplicate Rows Using the ALL Option
Example
Attributes of a Set Result
Examples
Example
Example
Example
Example of How the Character Set is Determined for the Query
Set Operators With Derived Tables
Example
Restrictions
Example
Set Operators in Subqueries
Examples
Example
Example
Example
Example
Example
Example
Example
Set Operators in INSERT … SELECT Statements
Example: Simple INSERT … SELECT Using Set Operators
Example: INSERT … SELECT from a View that Uses Set Operators
Example: INSERT … SELECT from a Derived Table with Set Operators
Set Operators in View Definitions
Support for the GROUP BY Clause
Restrictions
Examples
Example 1
Example 2
Example 3
Related Topics
Queries Connected by Set Operators
Number of Expressions in SELECT Statements
WITH Clause
GROUP BY and ORDER BY Clauses
Table Name in SELECT Statements
Data Type Compatibility
Related Topics
INTERSECT Operator
Syntax Elements
query_expression_1
ALL
query_expression_2
ANSI Compliance
Rules for INTERSECT
Attributes of a Set Result
Data Type of Nulls
Duplicate Row Handling
Example
MINUS/EXCEPT Operator
Syntax Elements
query_expression_1
ALL
query_expression_2
ANSI Compliance
Usage Notes
MINUS/EXCEPT and NULL
Duplicate Rows
UNION Operator
Syntax Elements
query_expression_1
ALL
query_expression_2
ANSI Compliance
Valid UNION Operations
Unsupported Operations
Description of a UNION Operation
UNION and NULL
Duplicate Rows
Unexpected Row Length Errors: Sorting Rows for UNION
Examples
Example: Selecting the Name, Project, and Employee Hours
Example: Determining the Number and Names of Employees
Example: Merging Lists of Values
Example: Performing a Union Operation to Find Hours Worked
UNION Operator and the Outer Join
Example: Effect of Explicit NULLs on Data Type of a UNION
Example: Effect of the Order of SELECT Statements on Data Type
Example: LOB Support in a UNION ALL Query
Related Topics
Join Expressions
Overview
Joins
Inner Joins
Ordinary Inner Join
Cross Join
Self-Join
Outer Joins
Definition of the Outer Join
Outer Join Relational Algebra
Left Outer Join
Right Outer Join
Full Outer Join
Multitable Joins
Coding ON Clauses for Outer Joins
Coding ON Clauses With WHERE Clauses for Outer Joins
Outer Join Case Study
Case Study Examples
Heuristics for Determining a Reasonable Answer Set
Guidelines for Using Outer Joins
Statement Syntax
Overview
Statement Independence Support
Null
ABORT
Usage Notes
Examples
Related Topics
BEGIN TRANSACTION
Usage Notes
Rules for Embedded SQL
Explicit Transactions
Implicit Transactions
Rules for Transactions Containing DDL Statements
Teradata Database Transaction Handling Protocol
Specifying SQL Request Modifiers With Explicit Transactions
Nested BEGIN TRANSACTION/END TRANSACTION Pairs
Scenarios
Related Topics
CALL
Usage Notes
Invocation
Rules for Executing SQL Procedures and External Stored Procedures
Rules For Specifying Input And Output Parameters
Rules For Call Arguments In BTEQ And CLIv2
Rules For Call Arguments In ODBC And JDBC
Rules For Call Arguments In Nested Procedures
Rules For Calling Procedures From Embedded SQL
Rules for Calling a Procedure With Dynamic Result Sets From Embedded SQL
Session Dateform and Called Procedures
Retrieving Values of Output Parameters
Status of Unqualified Referenced Objects
Dropped, Renamed, or Replaced Objects
Java External Procedure-Specific Behavior
Called Procedure Priority
Errors and Failures in Procedure Execution
Errors and Failures in Nested Procedures
Aborting a CALL Statement
Asynchronous and Synchronous Abort Logic
Examples
Example: Input Arguments in BTEQ and CLIv2
Example: Input and Output Arguments in BTEQ and CLIv2
Example: Stored Procedure and Embedded SQL Input Arguments
Example: Stored Procedures and Embedded SQL Input and Output Arguments
Example: Preventing Memory Overflow Errors for INOUT Parameters
Example: Input and Output Arguments in ODBC
Example: Input and Output Arguments in JDBC
Example: Calling a Stored Procedure that Returns Dynamic Result Sets from Embedded SQL
Example: Using an SQL UDF as an Argument for an External Stored Procedure
Example: Specifying a RETURNS or RETURNS STYLE Clause for an OUT Parameter Return Type
Example: Using RETURNS and RETURNS STYLE Clauses in the Same Procedure Call
Related Topics
CHECKPOINT
COMMENT (Comment-Retrieving Form)
COMMIT
DELETE
Syntax Elements
Isolated Loading Options
Delete Table Options
FROM Clause
WHERE Clause
Usage Notes
Locks and Concurrency
DELETE Processing Time
Unconstrained Fastpath Delete Processing
Restrictions on Fastpath Deletions
Fastpath Positioning Requirements
Position Within the Transaction
Proper Positioning For the Session Mode and Transaction Type
Fastpath Delete and Join Indexes
Fastpath Delete for Multiple DELETE Statements
Constrained (Slow Path) DELETE Processing
Duplicate Rows and DELETE Processing
General Rules for Using DELETE
Deleting Rows Using Views
Subqueries in a DELETE Statement
Rules for Using Scalar Subqueries in a DELETE Statement
Rules for Using Correlated Subqueries in a DELETE Statement
DELETE Statement in the Last Request
Deferred Deletion Applies Only to Range Terms
Deferred Deletion is Supported for LIKE Terms
Deferred Deletion and Join Indexes
Collation and Row Deletion
Column Partitioned Tables and DELETE
DELETE for NoPI Tables
Queue Tables and DELETE
DELETE Support for External UDT Expressions
DELETE Support for SQL UDT Expressions
Embedded SQL and Stored Procedure Error Condition Handling
DELETE Support for Load Isolated Tables
Examples
Example: DELETE Set of Rows
Example: DELETE All Rows
Example: DELETE Single Row
Example: Join Condition DELETE
Example: Self-Join Condition DELETE
Example: Delete Rows with an Equality Constraint on a Partitioning Column
Example: Delete Rows with an Indirect Constraint on the Target Table Partitioning Column
Example: Macro For DELETE
Example: DELETE ALL Multistatement Request
Example: Join Condition DELETE With FROM Clause and Correlation Name
Example: Join Condition DELETE With No FROM Clause
Example: Join Condition DELETE With Derived Table Subquery
Example: UDT Support For DELETE Statements
Example: DELETE and NoPI Tables
Example: Using an SQL UDF in a DELETE Request WHERE Condition
Example: Deleting from a Table with an Implicit Isolated Load Operation
Example: Deleting from a Table with an Explicit Isolated Load Operation
Example: Deleting a Row From a Table With Row-Level Security Protection
Example: Row-Level Security DELETE and SELECT Constraints For User Lacking Required Privileges (DELETE)
EXPLAIN Request Modifier with a DELETE Statement
ECHO
END TRANSACTION
EXECUTE (Macro Form)
INSERT/INSERT … SELECT
Syntax Elements
Isolated Loading Options
Table Name
Values Options
VALUES
expression
column_name
JSON
DEFAULT VALUES
Subquery Options
Error Logging Options
ANSI Compliance
Usage Notes
Locks and Concurrency
Inserting using the DEFAULT Function Option, the DEFAULT VALUES Option, or Without Specifying a Value
Inserting into UDT Columns
Inserting into Row-Partitioned Tables, Global Temporary Tables, and NoPI Tables
Rules for Using HASH BY or LOCAL ORDER BY to Insert Rows
Multistatement and Iterated INSERT Requests
Inserting When Using a DEFAULT Function
Using INSERT … SELECT With Tables That Have Row-Level Security
INSERT, DEFAULT Function, PERIOD Value Constructor, Scalar UDFs, UDTs, and Stored Procedures
Inserting Rows into Queue Tables
Valid and Invalid INSERT Operations
Duplicate Rows and INSERT
INSERT Process
Inserting Rows through Views
Subqueries in INSERT Requests
SELECT AND CONSUME Subqueries in INSERT Requests
Data Takes the Attributes of the New Table
Logging Errors for INSERT … SELECT Requests
Fast Path INSERT … SELECT Requests
INSERT in Embedded SQL and Stored Procedures
Inserting into Load Isolated Tables
Rules for Using the JSON Option
Examples
Example: Inserting a Row
Example: Insert Using a SELECT Subquery
Example: Insert Using a SELECT Subquery Without Target Column List
Example: Insert With Named Columns
Example: Insert Ordered Entries
Example: Bulk Insert
Example: INSERT Operation Using Single-Writer Lock
Example: INSERT and GENERATED ALWAYS Identity Columns
Example: INSERT and GENERATED BY DEFAULT Identity Columns
Example: Identity Columns and INSERT … SELECT
Example: INSERT and Queue Tables
Example: Non-Valid Use of PARTITION In VALUES Clause of INSERT Request
Example: Simple INSERT Requests Using a DEFAULT Function
Example: Using the DEFAULT Function With INSERT … SELECT
Example: Logging Errors With INSERT … SELECT
Example: Using the ST_Geometry Data Type to Represent Other Geospatial Data Types for INSERTs
Example: Using the ST_Geometry Data Type When Inserting Geospatial Data Into Tables
Example: INSERT Using a Scalar Subquery
Example: INSERT Using a PERIOD Value Constructor
Example: Passing an SQL UDF to a Single-Row Request
Example: Using the HASH BY Option With a NoPI Table
Example: Using the LOCAL ORDER BY Option
Example: Using the RANDOM Option to Randomly Redistribute Data Blocks of Rows and Individual Rows Before Copying Them Locally
Example: Inserting into a Table with an Implicit Isolated Load Operation
Example: Inserting into a Table with an Explicit Isolated Load Operation
LOCKING Request Modifier
Syntax Elements
ANSI Compliance
Usage Notes
LOCKING Request Modifier and CALL Requests
LOCKING Request Modifier Use With DML Statements
Canceling a Lock
Positioning Explicit Lock Requests
Using Locks with NULL SQL Requests
Using LOCKING ROW
Multiple Locks
Referencing a Locked Object
Specify the Keyword For the Object To Be Locked
Locks and Views
When the Request and View Referenced Include LOCKING Request Modifiers
READ Locks and Canceled Rollback Operations
Determining Which Locks Are Set
Examples
Example: LOCKING Request Modifier
Example: LOCKING Request Modifier and Secondary Indexes
Example: LOCKING ROW
Example: NOWAIT Option
MERGE
Syntax Elements
Usage Notes
Exceptions to Full ANSI Compliance
Locks and Concurrency
About the MERGE Statement
Rules and Limitations for MERGE
Logging MERGE Errors In an Error Table
Examples
Example: Using MERGE to Update and Insert
Example: MERGE With Subquery
Example: Using the DEFAULT Function With MERGE
Example: Logging MERGE Errors
Example: Using MERGE for Update and Insert Operations Within a Single SQL Request
Example: Target Table Composite Primary Index
Example: ON Clause Conditions Must Be ANDed With The Primary Index and Partitioning Column Equality Constraints
Example: For a RPPI Table, the ON Clause Must Specify a Condition on the Partitioning Column and the INSERT Specification Must Match
Example: Incorrect Examples Because of ON Clause Errors or Mismatches Between the ON Clause and the INSERT Specification
Example: MERGE With ON Clause and UPI or USI
Example: MERGE Updating a Primary Index
Example: MERGE and Identity Columns
Example: MERGE and Target Columns
Example: Reference the Source or Target in the ON, WHEN MATCHED, or WHEN NOT MATCHED Clauses
Example: Specifying the Partitioning Column Set in the ON Clause When the Target Relation Has a Row-Partitioned Primary Index
Example: You Cannot Substitute the System-Derived PARTITION Column For the Partitioning Column Set For a MERGE Operation With a RPPI Target Table
Example: With a Guaranteed Single-Row Source Relation, You Do Not Need To Specify the Partitioning Column Set
Example: Using the Target Table as the Source Table
Example: Using the BEGIN Period Bound Function as a Condition When Merging Into a RPPI Table
Example: Using the BEGIN and END Period Bound Functions as Conditions When Merging Into a RPPI Table
Example: Failure Because the Matching Condition is Defined on a PERIOD Bound Function
Example: Invoking an SQL UDF From a MERGE Request
Example: Merging into a Table with an Implicit Isolated Load Operation
Example: Merging into a Table with an Explicit Isolated Load Operation
Example: Executing a MERGE Update Request When Both the Target Table and the Source Table Have Row-Level Security Constraints
Example: Application of Row-Level Security SELECT Constraints When User Lacks Required Privileges (MERGE Request)
ROLLBACK
Syntax Elements
ANSI Compliance
Usage Notes
Definition and Termination of ANSI Transactions
ROLLBACK Is Explicit
Rules for Embedded SQL
ROLLBACK and ABORT are Synonyms
Actions Performed by ROLLBACK
Actions Performed by ROLLBACK with Embedded SQL
ROLLBACK with a WHERE Clause
ROLLBACK with a UDT in the WHERE Clause
Rules for Using a Scalar UDF in a ROLLBACK Request
Rules for Using Correlated Subqueries in a ROLLBACK Request
Rules for Using Scalar Subqueries in ROLLBACK Requests
Multiple ROLLBACK Requests
Two Types of ROLLBACK Requests
ROLLBACK with BTEQ
Examples
Example: ROLLBACK with a UDT in the WHERE Clause
Example: Using an SQL UDF in the WHERE Clause of a ROLLBACK Request
UPDATE
Syntax Elements
Isolated Loading Options
Target Table Options
FROM Clause
SET Clause
ANSI Compliance
Usage Notes
Locks and Concurrency
Activity Count
Duplicate Rows and UPDATE
Duplicate Row Checks
Large Objects and UPDATE
UPDATE Processing Time
Rules for Embedded SQL and Stored Procedures
Queue Tables and UPDATE
Rules for Updating Partitioning Columns of a Row-Partitioned Table
Rules for Updating a Table with a Row-Partitioned Join Index
Rules for Updating Column-Partitioned Tables
Identity Columns and UPDATE
Updating of GENERATED ALWAYS Identity Columns and PARTITION Columns
Updating Distinct UDT Columns
Updating Structured UDT Columns
Updating Structured UDTs Using a Mutator SET Clause
Rules for Updating Rows Using Views
Rules for Using Scalar Subqueries in UPDATE Requests
Rules for Using a Scalar UDF in an UPDATE Request
Rules for Using the DEFAULT Function With Update
Rules for Using a PERIOD Value Constructor With UPDATE
Nonvalid Uses of UPDATE
FROM Clause and UPDATE
UPDATEs With a Join
UPDATE Support for Load Isolated Tables
Examples
Example: UPDATE to Set a Specific Value
Example: UPDATE by a Percentage
Example: UPDATE to Set a Null Value
Example: Updating a Nonpartitioned Column with an Equality Constraint
Example: UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause
Example: UPDATE With a Subquery in its WHERE Clause
Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause
Example: UPDATE With a Scalar Noncorrelated Subquery in its SET Clause
Example: UPDATE With a Scalar Correlated Subquery in its SET Clause
Example: UPDATE With a Correlated Subquery in its WHERE Clause
Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause
Example: UPDATE With a Join
Example: UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses
Example: UPDATE With a Mutator SET Clause
Example: Update Requests Using the DEFAULT Function
Example: UPDATE Using a PERIOD Value Constructor
Example: Updating a NoPI Table
Example: Updating a Table with an Implicit Isolated Load Operation
Example: Updating a Table with an Explicit Isolated Load Operation
Example: Application of Row-Level Security SELECT and UPDATE Constraints When User Lacks Required Privileges (UPDATE Request)
UPDATE (Upsert Form)
Syntax Elements
Isolated Loading Options
Target Table Options
SET Clause
WHERE Clause
VALUES Clause
ANSI Compliance
Usage Notes
Definition of Upsert
Purpose of the Atomic Upsert Operation
Rules for Using Atomic Upsert
Locks and Concurrency
UPDATE (Upsert Form) Insert Operations and Row-Partitioned Tables
UPDATE (Upsert Form) Update Operations and RPPI Tables
UPDATE (Upsert Form) and Join Indexes
UPDATE (Upsert Form) and Subqueries
Queue Tables and UPDATE (Upsert Form)
UPDATE (Upsert Form) As a Triggering Action
UPDATE (Upsert Form) As a Triggered Action
Rules and Restrictions
Unsupported Syntax or Features
Using UPDATE (Upsert Form) Requests with Scalar Subqueries
Using UPDATE (Upsert Form) Requests on RPPI Tables
Using UPDATE (Upsert Form) with the DEFAULT Function
UDTs and UPDATE (Upsert Form)
UPDATE (Upsert Form) Support for Load Isolated Tables
Examples
UPDATE Upsert Examples
Example: Upsert Update
Example: Upsert Insert
Example: Upsert Specifying Different Tables
Example: Upsert on Primary Index
Example: Upsert Without Specifying Primary Index
Example: Upsert Without ELSE Clause
Example: Upsert Update Using the DEFAULT Function
Example: Upsert Update Using a Period Bound Function
USING Request Modifier
Usage Notes
USING Is Not Supported for Embedded SQL
Actions Performed by USING
USING Variables
USING Support for Scalar UDFs
Valid Request Types
USING and the EXPLAIN Request Modifier
USING Support for UDTs
USING Support for Large Objects
Inline Mode
Deferred Mode
Locator Mode
LOB Transfer Mode Comparison
Client Application Restrictions on USING With Large Objects
USING and DateTime System Functions
ANSI DateTime Considerations
Example of ANSI DateTime and Interval With USING
ANSI DateTime and Parameterized Requests
Array Considerations for Specifying TOP n as a USING Parameter
Character String Definitions in a USING Request Modifier
data_type Considerations for a Japanese Character Site
Non-GRAPHIC Character Data Representation
Character String Assignment and GRAPHIC Columns
CHARACTER and GRAPHIC Server Character Set Limitations for INSERT Operations
Character Data Import Process
UPPERCASE Option and Character Parameter Definition in USING
Examples
Example: USING Request Modifier
Example: USING Request Modifier with Variables
Example: USING Request Modifier Reads Character Strings
Example: Inline Mode Processing of a Large Object
Example: Deferred Mode Processing of a Large Object
Example: Deferred Mode Processing of CLOBs Using the DEFERRED BY NAME Phrase
Example: DEFERRED MODE Processing of BLOBs Using the DEFERRED BY NAME Phrase
Example: Locator Mode Processing of a Large Object
Example: Using a Locator Multiple Times Within a Session
Example: Iterated Requests
Example: USING Request Modifier with TOP Operator
Example: Non-Support for Iterated Requests With TOP n
Example: Dynamic UDT Expressions
Example: USING and SQL UDF Invocation
Query and Workload Analysis Statements
Overview
COLLECT DEMOGRAPHICS
COLLECT STATISTICS (QCD Form)
Usage Notes
Invocation
Where Column and Index Statistics Are Stored
Rules and Guidelines for COLLECT STATISTICS (QCD Form)
Collecting QCD Statistics on Multiple Columns
Collecting QCD Statistics on the PARTITION Column of a Table
COLLECT STATISTICS (QCD Form) and UDTs
COLLECT STATISTICS (QCD Form) And Large Objects
COLLECT STATISTICS (QCD Form) Does Not Collect Statistics For The Optimizer
Implicit Recollection of QCD Statistics Is Not Supported
COLLECT STATISTICS (QCD Form) Is Not Treated As DDL By The Transaction Manager
COLLECT STATISTICS (QCD Form) Collects New Statistics Even If Statistics Exist In the Data Dictionary or QCD
Quality of Statistics As a Function of Sample Size
Examples
Example: Collect Statistics on a Single-Column NUSI
Example: Collect Statistics On Index Using an Alternate Syntax
Example: Collecting Single-Column PARTITION Statistics
Example: Collecting Multi-Column PARTITION Statistics
DROP STATISTICS (QCD Form)
DUMP EXPLAIN
EXPLAIN Request Modifier
Usage Notes
Usage Notes
EXPLAIN and the USING Request Modifier
Capturing EXPLAIN Text Output in XML Format
EXPLAIN Report Overview
Substitute Characters in Object Names and Literals Returned in an EXPLAIN Report
Standard Form of Display for EXPLAIN
EXPLAIN Processes SQL Requests Only
Teradata Visual Explain Utility
EXPLAIN and Embedded SQL
EXPLAIN and Procedures
Effect of Request Cache Peeking on EXPLAIN Reports
Using EXPLAIN to Determine the Database Objects that a View Accesses
2PC Session Mode
INITIATE INDEX ANALYSIS
Usage Notes
Invocation
Boundary Options
Rules for Performing INITIATE INDEX ANALYSIS
Sequence of Events for an Index Analysis
Retrieving a Completed Index Analysis
Using the WITH INDEX TYPE or WITH NO INDEX TYPE Option
Using the CHECKPOINT Option
CHECKPOINT and TIME LIMIT Options
Evaluation of the ChangeRate Variable by INITIATE INDEX ANALYSIS
INITIATE INDEX ANALYSIS Not Supported From Macros
Examples
Example: Index Analysis
Example: Index Analysis and Privileges
Example: Index Analysis and Table List
Example: Index Analysis and Duplicate Table Specifications
Example: Index Analysis and Repeated Parameters
Example: Using a CHECKPOINT
Example: Setting a TIME LIMIT on an Index Analysis
Example: Specifying a CHECKPOINT and a TIME LIMIT
Example: Include NUSI and Simple Join Indexes Only in the Analysis
Example: Exclude All Join Indexes From The Analysis
Example: Include All Valid Index Types in the Analysis
INITIATE PARTITION ANALYSIS
INSERT EXPLAIN
Usage Notes
Examples
RESTART INDEX ANALYSIS
Notation Conventions
About Notation Conventions
Syntax Diagram Conventions
Character Shorthand Notation Used in This Document
Performance Considerations
Logging Row Counts for DML Statements
Optimizing INSERT ... SELECT Requests
Bulk SQL Error Logging
Using the TOP n Row Option
Using Recursive Queries
Sampling Methods
Distincts and Multiple Aggregate Distincts
Merge Joins and Performance
Additional Information
Teradata Links
column_name
Name of a column for which the insert operation supplies a new row value.
column_name can be specified in any order.
If a column_name is omitted, then any default value defined in the CREATE TABLE or CREATE VIEW request is used.
You cannot specify a derived period column name.
The begin and the end columns of a derived period column can be independently updated. The end column value must be greater than the begin column value of the derived period column.
subquery
The row or rows to be inserted consist of column values accessed by a query specification.
If a column name list is not specified, then the SELECT request must query the same number of columns as there are in the table that is receiving new rows.
You can specify nulls in the select expression list for columns that are not to be assigned values.
You cannot specify an ORDER BY clause as part of a subquery in an INSERT … SELECT request.
If the INSERT operation includes a column name list, values are assigned by matching column_name position with position of items in the select expression list.
HASH BY hash_list
Hash rows being inserted into a NoPI table across the AMPs based on the hash_list .
HASH BY RANDOM
Hash rows being inserted into a NoPI table across the AMPs randomly, a block at a time.
LOCAL ORDER BY order_list
Order rows being inserted into a table locally on the AMPs.