第1页 / 共492页
第2页 / 共492页
第3页 / 共492页
第4页 / 共492页
第5页 / 共492页
第6页 / 共492页
第7页 / 共492页
第8页 / 共492页
teradata资料.pdf
Preface
Purpose
Audience
Supported Software Release
Prerequisites
Changes to This Book
Additional Information
References to Microsoft Windows and Linux
Table of Contents
Chapter 1 The SELECT Statement
SELECT
ANSI Compliance
Authorization
Rules for UDTs
Rules for Embedded SQL
Rules for Using the DEFAULT Function With SELECT
Rules for Returning a Varying Column Table UDF Result
System Exerts a READ LOCK on the Tables or Rows Referenced By a SELECT Statement
Only SELECT Uses Set Operators
Joins
Success Response Indicates the Number of Rows Returned
Uses of the SELECT Statement
SELECT Subqueries
SELECT and Queue Tables
Related Topics
Simple SELECT Statement
SELECT AND CONSUME
ANSI Compliance
Authorization
Attributes of a Queue Table
Transaction Processing
Locks
FIFO Order Is Approximate
Rules for Embedded SQL
Restrictions
Performance Characteristics
Related Topics
SELECT … INTO
WITH RECURSIVE Clause
ANSI Compliance
How Nonrecursive WITH Works
How WITH RECURSIVE Works
WITH RECURSIVE Without a Recursive Statement
Using a WITH Clause Result as Input to a Table Function in the FROM Clause
Depth Control to Avoid Infinite Recursion
WITH and WITH RECURSIVE Restrictions
SQL Objects That Cannot Specify WITH or WITH RECURSIVE
Restrictions for Embedded SQL
Related Topics
DISTINCT, ALL, and .ALL Options
ANSI Compliance
DISTINCT Operator and UDTs
DISTINCT and Large Objects
DISTINCT and the WITH Clause
DISTINCT and the TOP n Operator
DISTINCT and Recursive Queries
Reason for Unexpected Row Length Errors: Sorting Rows Before Eliminating Duplicates
.ALL Option and Structured UDTs
UDT And Table Definitions For the Example Sets
TOP n Operator
ANSI Compliance
About TOP n
Restrictions
Evaluation Order
TOP n Operator Replaces QUALIFY RANK and QUALIFY ROW_NUMBER
FROM Clause
ANSI Compliance
When Fully Qualified Names Are Required
FROM Clause Required for Subqueries
Self-Joins
FROM Clause Using Both a Permanent Table Name and a Correlation Name
How Column References Affect Self-Joins
Derived Tables
Introduction
ANSI Compliance
Uses for Derived Tables
Rules for Derived Tables
Derived Tables and Multilevel Aggregates
Restricted Use With SELECT AND CONSUME Requests
WHERE Clause
ANSI Compliance
Restricted Use With Aggregates
Restricted Use With Large Objects
Restricted Use With UDTs
Restricted Use With SELECT AND CONSUME Requests
Rules For Using a DEFAULT Function As Part of the Search Condition Of a WHERE Clause
WHERE With AND and OR Operators
WHERE In Character String Searches
WHERE Usage In Join Conditions
Join Efficiency and Indexes
EXISTS Quantifier
Unconstrained Joins
System-Derived PARTITION and PARTITION#Ln Columns
Subqueries in Search Conditions
ANSI Compliance
Restriction on Number of Subqueries Permitted
Restricted Use With SELECT AND CONSUME Statements
Restricted Use With TOP n Operator
Correlated Subqueries
Definitions
ANSI Compliance
Behavior of Outer References
Rules for Using Correlated Subqueries
When Uncorrelated Subqueries Are Desired
Comparing the Behavior of Uncorrelated and Correlated Subqueries
Combining Equality and Inequality Constraints in a Correlated Subquery
Correlated Subqueries and the SELECT Statement
Rules for Using Correlated Subqueries in a SELECT Statement
SELECT COUNT(*)
EXISTS Quantifier and Correlated Subqueries
GROUP BY Clause
ANSI Compliance
GROUP BY Clause Terminology
Aggregate Handling
GROUP BY and Ordered Analytical Functions
GROUP BY and Large Objects
GROUP BY and Recursive Queries
Combining GROUP BY With ORDER BY
Reason for Unexpected Row Length Errors: Sorting Rows for Grouping
Evaluation Order of WHERE, GROUP BY, and HAVING Clauses
Examples
How Extended GROUP BY Operator Specifications Are Resolved
Introduction
CUBE Option
ANSI Compliance
SAMPLE Clause and CUBE
How CUBE Summarizes Data
GROUPING SETS Option
ANSI Compliance
SAMPLE Clause and GROUPING SETS
How GROUPING SETS Summarizes Data
ROLLUP Option
ANSI Compliance
SAMPLE Clause and ROLLUP
How ROLLUP Summarizes Data
HAVING Clause
ANSI Compliance
HAVING And UDTs
HAVING And Large Objects
HAVING And Aggregate Functions
HAVING and Recursive Queries
Rules For Using a DEFAULT Function As Part of the Search Condition Of a HAVING Clause
Evaluation Order of WHERE, GROUP BY, and HAVING Clauses
HAVING Error Conditions
Aggregating a Join In a HAVING Clause
QUALIFY Clause
ANSI Compliance
QUALIFY And UDTs
QUALIFY And Large Objects
QUALIFY and Complex Joins Involving Subqueries
QUALIFY and Statistical Functions
Rules For Using a DEFAULT Function As Part of the Search Condition Of a QUALIFY Clause
Evaluation Order of WHERE, GROUP BY, and QUALIFY Clauses
TOP n Operator and QUALIFY
QUALIFY Error Conditions
SAMPLE Clause
ANSI Compliance
About SAMPLE
Rules for Using the SAMPLE Clause
GROUP BY and SAMPLE
TOP n Operator and SAMPLE
Simple Random Sampling
Stratified Random Sampling
Sampling With Or Without Replacement
Randomized and Proportional Row Allocation
Examples Using Stratified Sampling
SAMPLEID Expression
ANSI Compliance
Definition
Rules for Using SAMPLEID
Using SAMPLEID With Stratified Sampling
ORDER BY Clause
ANSI Compliance
Column Name
Column Position
Combining ORDER BY And WITH Clauses
ORDER BY and UDTs
Reason for Unexpected Row Length Errors: Sorting and Default Sort Order
Specifying Collation
Case Sensitivity
Japanese Character Sort Order Considerations
International Sort Orders
European Sort Order
WITH Clause
ANSI Compliance
Expressions In A WITH Clause
WITH And UDTs
WITH And LOBs
A WITH Clause Is Not Allowed When You Specify The TOP n Operator
Using TITLE Phrases
How Multiple WITH Clauses Work Together
Combining WITH And ORDER BY Clauses
Combining WITH and GROUP BY Clauses
Chapter 2 Join Expressions
Joins
Definition
Join Varieties
Natural and Theta Joins
Inner and Outer Joins
Inner Joins
Ordinary Inner Join
Definitions: Join and Inner Join
Components of an Inner Join
The Default Join
Joins on Views Containing an Aggregate
Cross Join
Definition
Why Perform a Cross Join?
How to Write a Cross Join
Self-Join
Definition
Outer Joins
Definition of the Outer Join
Definition: Outer Join
Syntax
Simple Outer Join Example
Outer Join Is Equivalent to Inner Join for Selected Foreign Key-Primary Key Relationships
Components of an Outer Join
Types of Outer Join
Terminology
Inner/Outer Table Example
Projected Column List
Nulls and the Outer Join
Example
Rules for Using the DEFAULT Function as a Search Condition for an Outer Join ON Clause
Use of the DEFAULT Function in an Outer Join ON Condition
Review of Relevant Relational Algebra for the Outer Join
Basic SQL Query
Relational Algebra for the Outer Join
Left Outer Join
Definition
Inner/Outer Table Example
Practical Example
Right Outer Join
Definition
Inner/Outer Table Example
Practical Example
Full Outer Join
Definition
Inner/Outer Table Example
Practical Example
Multitable Joins
Introduction
Definition: Temporary Derived Table
ON Clause Evaluation Order
Example 1a
Example 1b
Example 2
Coding ON Clauses for Outer Joins
Introduction
Comparing the ON Clause Join Conditions of Examples 1 and 2
Related Topics
Coding ON Clauses With WHERE Clauses for Outer Joins
Introduction
Use Join Conditions in ON Clauses, Not Search Conditions
Using Search Conditions In ON Clauses
A Guide to Placing Search Conditions in a Join
Related Topics
Rules And Recommendations for Coding ON and WHERE Clauses for Outer Joins
Outer Join Case Study
Acknowledgement
Purpose of the Case Study
Topics for the Case Study
Case Study Examples
Introduction
Defining the Business Question
Heuristics for Determining a Reasonable Answer Set
Introduction
Single Table Cardinality Estimate
Inner Join Cardinality Estimate
First Attempt
Brief Analysis of the Result
EXPLAIN Text for Example 1
Analysis of the EXPLAIN Text
The Coding Error
Second Attempt
Example 2: Outer Join But Does Not Return the Correct Answer
Brief Analysis of the Result
EXPLAIN Text for Example 2
Analysis of the EXPLAIN Text
The Coding Error
Third Attempt
Example 3: Outer Join That Is Really an Inner Join
Brief Analysis of the Result
EXPLAIN Text for Example 3
Analysis of the EXPLAIN Text
The Coding Error
Final Attempt: The Correct Answer
Example 4: Outer Join
Brief Analysis of the Result
EXPLAIN Text for Example 4
Analysis of the EXPLAIN Text
Guidelines for Getting the Desired Answer Using Outer Joins
Introduction
Guidelines
Chapter 3 SQL Data Manipulation Language Statement Syntax
Null
ANSI Compliance
Authorization
Invocation
Definition: Null Statement
ABORT
ANSI Compliance
Authorization
How ANSI Transactions Are Defined and Terminated
ABORT Is Explicit
ABORT and ROLLBACK Are Synonyms
ABORT With a WHERE Clause
ABORT With a UDT In Its WHERE Clause
Rules For Using Correlated Subqueries In An ABORT Request
Multiple ABORT Requests
Two Types of ABORT Requests
Rules For Using ABORT In Embedded SQL
Related Topics
BEGIN TRANSACTION
ANSI Compliance
Authorization
Rules for Embedded SQL
Explicit Transactions
Implicit Transactions
Rules for Transactions Containing DDL Requests
Teradata Database Transaction Handling Protocol
Using Request Modifiers With BEGIN TRANSACTION
Nested BEGIN TRANSACTION/END TRANSACTION Pairs
Scenarios
Related Topics
CALL
ANSI Compliance
Authorization
Privileges Granted Automatically
Invocation
Rules for Executing SQL Stored Procedures and External Stored Procedures
Delimiting The CALL Statement
General 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 Stored Procedures From Embedded SQL
Rules for Calling a Stored 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 Stored Procedure-Specific Behavior
CALL Request Priority
Errors and Failures in Procedure Execution
Errors and Failures in Nested Procedures
Aborting a CALL Request
Asynchronous and Synchronous Abort Logic
Related Topics
CHECKPOINT
Interactive Syntax
Embedded SQL and Stored Procedure Syntax
ANSI Compliance
Authorization
General Usage Notes
Usage Notes for Stored Procedures and Embedded SQL
COMMENT (Comment-Retrieving Form)
ANSI Compliance
Authorization
Privileges Granted Automatically
Rules for Using COMMENT (Comment-Retrieving Form)
Related Topics
COMMIT
ANSI Compliance
Authorization
How ANSI Transactions Are Defined and Terminated
COMMIT Is Explicit
Rules for Embedded SQL
Relation to ABORT and ROLLBACK
COMMIT and BTEQ
BTEQ Example
Examples
Related Topics
DELETE (Basic/Searched Form)
Basic Syntax
Join Condition Syntax
ANSI Compliance
Authorization
Recommendation
Locks
DELETE Processing Time
Unconstrained (Fast Path) DELETE Processing
Constrained (Slow Path) DELETE Processing
Duplicate Rows and DELETE Processing
General Rules for Using DELETE
Deleting Rows Using Views
Subqueries in DELETE Requests
Rules for Using Correlated Subqueries In a DELETE Statement
DELETE and Partitioned Primary Indexes
Queue Tables and DELETE
DELETE Support For UDT Expressions
Embedded SQL and Stored Procedure Error Condition Handling
DELETE (Positioned Form)
ECHO
ANSI Compliance
Authorization
ECHO Not Supported for Embedded SQL
How ECHO Works
ECHO and BTEQ
END TRANSACTION
ANSI Compliance
Authorization
Usage Notes
Rules for Embedded SQL
Related Topics
EXECUTE (Macro Form)
ANSI Compliance
Authorization
Recommendation
Rules for Performing Macros
Access Logging and Errors
Related Topics
EXPLAIN Request Modifier
ANSI Compliance
Authorization
EXPLAIN and USING Request Modifier
EXPLAIN Report Overview
EXPLAIN Processes SQL Requests Only
Teradata Visual Explain Utility
EXPLAIN and Embedded SQL
EXPLAIN and Stored Procedures
Effect of Request Cache Peeking on EXPLAIN Reports
Standard Form of Display for EXPLAIN
Using EXPLAIN to Determine All Database Objects a View Accesses
2PC Session Mode
Related Topics
GROUP BY Clause
HAVING Clause
INSERT/INSERT … SELECT
ANSI Compliance
Authorization
DEFAULT Function Option
DEFAULT VALUES Option
Inserting Into Identity Columns When DEFAULT VALUES Is Specified
Inserting Into NOT NULL Columns
Inserting Into Distinct UDT Columns
Inserting Into Structured UDT Columns
Inserting Into PPI Tables
Inserting Into Global Temporary Tables
Rules for Inserting When Using a DEFAULT Function
Rules for Using INSERT … SELECT With a DEFAULT Function
INSERT, UDTs, and Stored Procedures
Inserting Into Queue Tables
Inserting Into Queue Tables Using Iterated Requests
Valid INSERT Operations
Nonvalid INSERT Operations
Large Objects and INSERT
Duplicate Rows and INSERT
Duplicate Rows and INSERT … SELECT
The INSERT Process
Inserting Rows Through Views
Subqueries In INSERT Statements
SELECT AND CONSUME Subqueries in INSERT Statements
Data Takes the Attributes of the New Table
Logging Errors In an Error Table With INSERT … SELECT
Fast Path INSERT … SELECT Statements
INSERT … SELECT Performance and Target Table Identity Column Primary Indexes
Rules for Fast Path INSERT … SELECT Statements
General Rules for INSERT in Embedded SQL and Stored Procedures
Rules for Valued INSERT in Embedded SQL
Rules for INSERT … SELECT in Embedded SQL and Stored Procedures
Related Topics
LOCKING Request Modifier
ANSI Compliance
Authorization
Applicability
Locking Severities
LOCKING Request Modifier Use With Different DML Statements
Cancelling a Lock
When Explicit Locking Is Necessary
Positioning Explicit Lock Requests
Using LOCKING ROW
Using Locks with NULL SQL Statements
Multiple Locks
Referencing a Locked Object
Specify the Keyword For the Object To Be Locked
Locks and Views
When Both the Request and View Referenced Include LOCKING Request Modifiers
READ Locks and Cancelled Rollback Operations
Determining Which Locks Are Set
Related Topics
MERGE
ANSI Compliance
Authorization
Exceptions to Full ANSI Compliance
Differences in MERGE Statement Behavior Between Teradata Database V2R5.0 and Teradata Database 12.0 Releases
Locking and Concurrency Issues
About the MERGE Statement
Rules and Limitations for MERGE
Rules for Using the DEFAULT Function With MERGE Requests
Large Objects and MERGE
Rules for Using MERGE With PPI Tables
Logging MERGE Errors In an Error Table
MERGE Insert Operations and UDTs
MERGE Update Operations and UDTs
Queue Tables and MERGE
MERGE as a Triggering Action
MERGE As a Triggered Action
Related Topics
ORDER BY Clause
QUALIFY Clause
ROLLBACK
ANSI Compliance
Authorization
How ANSI Transactions Are Defined and Terminated
ROLLBACK Is Explicit
Rules for Embedded SQL
ROLLBACK and ABORT Are Synonyms
How ROLLBACK Works
How ROLLBACK Works With Embedded SQL
ROLLBACK With A WHERE Clause
ROLLBACK With a UDT In Its WHERE Clause
Rules for Using Correlated Subqueries in a ROLLBACK Statement
Multiple ROLLBACK Requests
Two Types of ROLLBACK Requests
ROLLBACK With BTEQ
Related Topics
SAMPLE Clause
SELECT
SELECT … INTO
UPDATE
Basic Form, No FROM Clause Syntax
Basic Form, FROM Clause Syntax
Joined Tables Syntax
ANSI Compliance
Authorization
Locks Set by UPDATE
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 PPI Table
Identity Columns and UPDATE
Update of GENERATED ALWAYS Identity Columns and PARTITION Columns Is Not Permitted
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 the DEFAULT Function With Update
Nonvalid Uses of UPDATE
FROM Clause
UPDATEs With a Join
UPDATE (Positioned Form)
UPDATE (Upsert Form)
ANSI Compliance
Authorization
UDTs and UPDATE (Upsert Form)
Large Objects and UPDATE (Upsert Form)
UPDATE (Upsert Form) Insert Operations and Partitioned Primary Indexes
UPDATE (Upsert Form) Update Operations and Partitioned Primary Indexes
UPDATE (Upsert Form) and Subqueries
Queue Tables and UPDATE (Upsert Form)
Definition: Upsert
Purpose of the Atomic Upsert Operation
Rules for Using Atomic Upsert
UPDATE (Upsert Form) As a Triggering Action
UPDATE (Upsert Form) As a Triggered Action
Restrictions
Using the DEFAULT Function With the Upsert Form of UPDATE
Examples
Related Topics
USING Request Modifier
ANSI Compliance
Authorization
USING Not Supported for Embedded SQL
How USING Works
USING Variables
Valid Request Types
USING Support for UDTs
USING Support for Large Objects
Application-Specific 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
Character String Definitions in a USING Request Modifier
data_type Considerations for a Japanese Character Site
CHARACTER and GRAPHIC Server Character Set Limitations for INSERT Operations
Non-GRAPHIC Character Data Representation
Character String Assignment and GRAPHIC Columns
Character Data Import Process
UPPERCASE Option and Character Parameter Definition in USING
WHERE Clause
WITH Clause
Appendix A Notation Conventions
Syntax Diagram Conventions
Notation Conventions
Paths
Required Items
Optional Items
Abbreviations
Loops
Excerpts
Character Shorthand Notation Used In This Book
Introduction
Character Symbols
Pad Characters
Predicate Calculus Notation Used in This Book
Appendix B ANSI SQL Standards
The Individual ANSI SQL Standards
Glossary
Index
Teradata Database
SQL Reference
Data Manipulation Statements
Release 12.0
B035-1146-067A
September 2007
The product described in this book is a licensed product of Teradata, a division of NCR Corporation.
NCR, Teradata and BYNET are registered trademarks of NCR Corporation.
Adaptec and SCSISelect are registered trademarks of Adaptec, Inc.
AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc.
EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation.
Engenio is a trademark of Engenio Information Technologies, Inc.
Ethernet is a trademark of Xerox Corporation.
GoldenGate is a trademark of GoldenGate Software, Inc.
Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company.
IBM, CICS, DB2, MVS, RACF, Tivoli, and VM are registered trademarks of International Business Machines Corporation.
Intel, Pentium, and XEON are registered trademarks of Intel Corporation.
KBMS is a registered trademark of Trinzic Corporation.
Linux is a registered trademark of Linus Torvalds.
LSI, SYM, and SYMplicity are registered trademarks of LSI Logic Corporation.
Active Directory, Microsoft, Windows, Windows Server, and Windows NT are either registered trademarks or trademarks of Microsoft
Corporation in the United States and/or other countries.
Novell is a registered trademark of Novell, Inc., in the United States and other countries. SUSE is a trademark of SUSE LINUX Products GmbH,
a Novell business.
QLogic and SANbox are registered trademarks of QLogic Corporation.
SAS and SAS/C are registered trademark of SAS Institute Inc.
Sun Microsystems, Sun Java, Solaris, SPARC, and Sun are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. or other
countries.
Unicode is a registered trademark of Unicode, Inc.
UNIX is a registered trademark of The Open Group in the US and other countries.
NetVault is a trademark and BakBone is a registered trademark of BakBone Software, Inc.
NetBackup and VERITAS are trademarks of VERITAS Software Corporation.
Other product and company names mentioned herein may be the trademarks of their respective owners.
THE INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED ON AN “AS-IS” BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-
INFRINGEMENT. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY
NOT APPLY TO YOU. IN NO EVENT WILL NCR CORPORATION (NCR) BE LIABLE FOR ANY INDIRECT, DIRECT, SPECIAL, INCIDENTAL OR
CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS OR LOST SAVINGS, EVEN IF EXPRESSLY ADVISED OF THE POSSIBILITY OF SUCH
DAMAGES.
The information contained in this document may contain references or cross references to features, functions, products, or services that are
not announced or available in your country. Such references do not imply that NCR intends to announce such features, functions, products,
or services in your country. Please consult your local NCR representative for those features, functions, products, or services available in your
country.
Information contained in this document may contain technical inaccuracies or typographical errors. Information may be changed or updated
without notice. NCR may also make improvements or changes in the products or services described in this information at any time without notice.
To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this
document. Please e-mail: teradata-books@lists.ncr.com
Any comments or materials (collectively referred to as “Feedback”) sent to NCR will be deemed non-confidential. NCR will have no obligation
of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform, create derivative works of and
distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, NCR will be free to use any ideas,
concepts, know-how or techniques contained in such Feedback for any purpose whatsoever, including developing, manufacturing, or marketing
products or services incorporating Feedback.
Copyright © 2000 - 2007 by NCR Corporation. All Rights Reserved.
Preface
Purpose
SQL Reference: Data Manipulation Statements describes how to use SQL to manipulate data.
Use this book with the other volumes in the SQL Reference book set.
Audience
Application programmers and end users are the principal audience for this manual. System
administrators, database administrators, security administrators, NCR field engineers, and
other technical personnel responsible for designing, maintaining, and using the Teradata
Database might also find this manual to be useful.
Supported Software Release
This book supports Teradata® Database 12.0.
Prerequisites
If you are not familiar with the Teradata Database, you should read Introduction to Teradata
Warehouse before reading this document.
Additional information about developing applications using embedded SQL is found in
Teradata Preprocessor2 for Embedded SQL Programmer Guide.
DML functions and operators are documented in SQL Reference: Functions and Operators.
You should be familiar with basic relational database management technology. This book is
not an SQL primer.
SQL Reference: Data Manipulation Statements
3
Preface
Changes to This Book
Changes to This Book
This book includes the following changes to support the current release:
Date
Description
September 2007
Added support for dynamic result sets from table UDFs to
SELECT.
Updated ABORT and ROLLBACK for effects of parallelism on
multiple ABORT or ROLLBACK requests.
Did the following for CALL:
Updated default INOUT data typing.
Added rules for dynamic result sets and embedded SQL.
Added information about calling Java external stored
procedures.
Added information about synchronous and asynchronous
abort logic for calling stored procedures.
Updated DELETE for rules regarding deleting rows from PPI
tables.
Updated INSERT/INSERT … SELECT for the following:
Error logging.
Rules for inserting into PPI tables.
Updated MERGE for the following:
Multirow source table extensions and other fundamental
differences between the V2R5.0 and Teradata Database 12.0
implementations.
Locking and concurrency issues regarding the differences
between the V2R5.0 and Teradata Database 12.0
implementations.
Error logging.
Using MERGE as a substitute for MultiLoad.
Using MERGE as a substitute for UPDATE.
Using MERGE as a substitute for INSERT … SELECT and
conditional INSERT … SELECT requests.
Using MERGE to do join updates.
Updated rules for MERGE with PPI tables.
Added MERGE issues with tables having batch referential
integrity constraints.
Added rules for updating MLPPI tables to UPDATE.
Added rules for updating MLPPI tables to UPDATE (Upsert
Form).
Performed DR fixes and enhancement requests.
4
SQL Reference: Data Manipulation Statements
Preface
Changes to This Book
Date
Description
September 2006
Moved material about interpreting EXPLAIN reports to SQL
Reference: Statement and Transaction Processing.
Added information about explicit requirement to issue one of the
following statements to terminate a transaction when operating in
ANSI session mode:
ABORT
COMMIT
ROLLBACK
Corrected authorization information for EXPLAIN request
modifier.
Added information about using the DEFAULT function to
SELECT, INSERT, MERGE, and UPDATE statements.
Added information about using the DEFAULT function to
WHERE and ON search_condition clauses.
November 2005
Added UDT-related .ALL option to Chapter 1.
Updated the following items in Chapter 1 with respect to UDTs:
SELECT
SELECT AND CONSUME
WITH RECURSIVE clause
DISTINCT operator
WHERE clause
HAVING clause
QUALIFY clause
ORDER BY clause
WITH clause
Updated the following statements in Chapter 3 with respect to
UDTs:
ABORT
DELETE
INSERT
MERGE
ROLLBACK
UPDATE
UPDATE (Upsert Form)
USING
Updated CALL statement in Chapter 3 for relaxed OUT parameter
requirements for BTEQ, CLIv2, ODBC, and JDBC.
SQL Reference: Data Manipulation Statements
5
Preface
Additional Information
Date
Description
November 2004
Updated CALL statement for external stored procedures and to lift
the restriction of being unsupported in triggers or in multi-
statement SQL requests.
Added SELECT AND CONSUME statement to support queue
tables.
Updated DELETE, MERGE, and UPDATE to support queue
tables.
Added TOP option and WITH RECURSIVE clause to SELECT.
Added TABLE to FROM clause to support table functions.
Updated USING row descriptor to support iterated requests.
Additional Information
Additional information that supports this product and the Teradata Database is available at
the following Web sites.
Type of Information
Description
Source
Overview of the
release
The Release Definition provides the
following information:
Information too
late for the manuals
Additional
information related
to this product
Overview of all the products in the
release
Information received too late to be
included in the manuals
Operating systems and Teradata
Database versions that are certified
to work with each product
Version numbers of each product
and the documentation for each
product
Information about available
training and support center
Use the NCR Information Products
Publishing Library site to view or
download the most recent versions of
all manuals.
Specific manuals that supply related
or additional information to this
manual are listed.
http://www.info.ncr.com/
Click General Search. In the
Publication Product ID field, enter
1725 and click Search to bring up
the following Release Definition:
Base System Release Definition
B035-1725-067K
http://www.info.ncr.com/
Click General Search, and do one of
the following:
In the Product Line field, select
Software - Teradata Database for
a list of all of the publications for
this release,
In the Publication Product ID
field, enter a book number.
6
SQL Reference: Data Manipulation Statements
Preface
References to Microsoft Windows and Linux
Type of Information
CD-ROM images
Description
Source
This site contains a link to a
downloadable CD-ROM image of all
customer documentation for this
release. Customers are authorized to
create CD-ROMs for their use from
this image.
http://www.info.ncr.com/
Click General Search. In the Title or
Keyword field, enter CD-ROM, and
Click Search.
Ordering
information for
manuals
Use the NCR Information Products
Publishing Library site to order
printed versions of manuals.
http://www.info.ncr.com/
Click How to Order under Print &
CD Publications.
General
information about
Teradata
Teradata.com
The Teradata home page provides
links to numerous sources of
information about Teradata. Links
include:
Executive reports, case studies of
customer experiences with
Teradata, and thought leadership
Technical information, solutions,
and expert advice
Press releases, mentions and
media resources
References to Microsoft Windows and Linux
This book refers to “Microsoft Windows” and “Linux.” For Teradata Database 12.0, these
references mean the following:
“Windows” is Microsoft Windows Server 2003 32-bit and Microsoft Windows Server 2003
64-bit.
“Linux” is SUSE Linux Enterprise Server 9 and SUSE Linux Enterprise Server 10.
Teradata plans to release Teradata Database support for SUSE Linux Enterprise Server 10
before the next major or minor release of the database. Therefore, information about this
SUSE release is included in this document. The announcement regarding availability of SUSE
Linux Enterprise Server 10 will be made after Teradata Database 12.0 GCA. Please check with
your account representative regarding SUSE Linux Enterprise Server 10 availability in your
location.
SQL Reference: Data Manipulation Statements
7
Preface
References to Microsoft Windows and Linux
8
SQL Reference: Data Manipulation Statements
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
