Teradata Database
SQL Quick ReferenceRelease 13.0
B035-1510-098BApril 2009
The product or products described in this book are licensed products of Teradata Corporation or its affiliates.
Teradata, BYNET, DBC/1012, DecisionCast, DecisionFlow, DecisionPoint, Eye logo design, InfoWise, Meta Warehouse, MyCommerce, SeeChain, SeeCommerce, SeeRisk, Teradata Decision Experts, Teradata Source Experts, WebAnalyst, and You’ve Never Seen Your Business Like This Before are trademarks or registered trademarks of Teradata Corporation or its affiliates.
Adaptec and SCSISelect are trademarks or registered trademarks of Adaptec, Inc.
AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc.
BakBone and NetVault are trademarks or registered trademarks of BakBone Software, Inc.
EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation.
GoldenGate is a trademark of GoldenGate Software, Inc.
Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company.
Intel, Pentium, and XEON are registered trademarks of Intel Corporation.
IBM, CICS, RACF, Tivoli, and z/OS are registered trademarks of International Business Machines Corporation.
Linux is a registered trademark of Linus Torvalds.
LSI and Engenio are registered trademarks of LSI Corporation.
Microsoft, Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other countries.
Novell and SUSE are registered trademarks of Novell, Inc., in the United States and other countries.
QLogic and SANbox are trademarks or registered trademarks of QLogic Corporation.
SAS and SAS/C are trademarks or registered trademarks of SAS Institute Inc.
SPARC is a registered trademark of SPARC International, Inc.
Sun Microsystems, Solaris, Sun, and Sun Java are trademarks or registered trademarks of Sun Microsystems, Inc., in the United States and other countries.
Symantec, NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and other countries.
Unicode is a collective membership mark and a service mark of Unicode, Inc.
UNIX is a registered trademark of The Open Group in the United States and other countries.
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 TERADATA CORPORATION 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 Teradata Corporation intends to announce such features, functions, products, or services in your country. Please consult your local Teradata Corporation 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. Teradata Corporation 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: [email protected]
Any comments or materials (collectively referred to as “Feedback”) sent to Teradata Corporation will be deemed non-confidential. Teradata Corporation 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, Teradata Corporation 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 – 2009 by Teradata Corporation. All Rights Reserved.
SQL Quick Reference 3
Preface
Purpose
This book is a quick reference for the SQL dialect supported by the Teradata Database.
Audience
All users of Teradata SQL who need information about how to structure an SQL statement.
Supported Software Release
This book supports Teradata® Database 13.0.
Prerequisites
You should be familiar with basic computer technology, the Teradata Database, and the Teradata SQL language.
It may be helpful to review the following books:
• Introduction to Teradata
• The SQL book set
Changes to This Book
Date Description
Teradata Database 13.0
April 2009
• Updated syntax diagrams throughout the book
• Added the following new syntax diagrams to Chapter 1:
• Period Literals
• PERIOD(DATE) Data Type
• PERIOD(TIME) Data Type
• PERIOD(TIME WITH TIME ZONE) Data Type
• PERIOD(TIMESTAMP) Data Type
PrefaceChanges to This Book
4 SQL Quick Reference
Teradata Database 13.0(Continued)
• Added the following new syntax diagrams to Chapter 1:
• PERIOD(TIMESTAMP WITH TIME ZONE) Data Type
• VARIANT_TYPE UDT
• Geospatial Data Types
• Added the following new syntax diagrams to Chapter 2:
• CURRENT_ROLE
• CURRENT_USER
• CONTAINS
• IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED
• MEETS
• PRECEDES
• SUCCEEDS
• BEGIN
• END
• LAST
• INTERVAL
• PRIOR
• NEXT
• P_INTERSECT
• LDIFF
• RDIFF
• P_NORMALIZE
• Period Value Constructor
• Arithmetic Operators
• Scalar UDF Expression
• Aggregate UDF Expression
• NEW VARIANT_TYPE
• Added the following new syntax diagrams to Chapter 3:
• COLLECT STATISTICS (Recollect Statistics)
• CREATE GLOP SET
• CREATE REPLICATION RULESET/REPLACE REPLICATION RULESET
• DROP GLOP SET
• DROP REPLICATION RULESET
• SHOW QUERY LOGGING
• Added the following new syntax diagrams to Chapter 4:
• GRANT CONNECT THROUGH
• REVOKE CONNECT THROUGH
• Added the following new syntax diagrams to Chapter 5:
• HASH BY Clause
• LOCAL ORDER BY Clause
Date Description
PrefaceChanges to This Book
SQL Quick Reference 5
Teradata Database 13.0(Continued)
• Added the following new syntax diagrams to Chapter 7:
• SIGNAL
• RESIGNAL
• GET DIAGNOSTICS
Teradata Database 12.0
September 2007
• Updated existing syntax diagrams in Chapters 2, 3, 4, 5, 6, 7, 8, 9 and 10
• Added the following new syntax diagrams to Chapter 1:
• Hexadecimal Name Literals
• Unicode Character String Literals
• Unicode Delimited Identifier
• Added the following new syntax diagrams to Chapter 2:
• DEGREES, RADIANS
• STRING_CS
• Modified the syntax diagrams for Window Aggregate Functions in Chapter 2 to support the following aggregate functions:
• CORR
• COVAR_POP
• COVAR_SAMP
• REGR_AVGX
• REGR_AVGY
• REGR_COUNT
• REGR_INTERCEPT
• REGR_R2
• REGR_SLOPE
• REGR_SXX
• REGR_SXY
• REGR_SYY
• STDDEV_POP
• STDDEV_SAMP
• VAR_POP
• VAR_SAMP
• Added the following new syntax diagrams to Chapter 3:
• CREATE ERROR TABLE
• Java Simple and Object Mapped Data Types
• DROP ERROR TABLE
• LOGGING ONLINE ARCHIVE OFF
• LOGGING ONLINE ARCHIVE ON
• SET QUERY_BAND
Date Description
PrefaceAdditional Information
6 SQL Quick Reference
Additional Information
Teradata Database 12.0 (Continued)
• Added the following new syntax diagrams to Chapter 5:
• INITIATE PARTITION ANALYSIS
• DIAGNOSTIC COSTPRINT
• DIAGNOSTIC DUMP COSTS
• DIAGNOSTIC HELP COSTS
• DIAGNOSTIC SET COSTS
• DIAGNOSTIC HELP PROFILE
• DIAGNOSTIC SET PROFILE
• Added the following new syntax diagrams to Chapter 6:
• HELP COLUMN - Syntaxes 6, 7 and 8
• HELP ERROR TABLE
Date Description
URL Description
http://www.info.teradata.com/ Use the Teradata Information Products Publishing Library site to:
• View or download a manual:
1 Under Online Publications, select General Search.
2 Enter your search criteria and click Search.
• Download a documentation CD-ROM:
1 Under Online Publications, select General Search.
2 In the Title or Keyword field, enter CD-ROM, and click Search.
• Order printed manuals:
Under Print & CD Publications, select How to Order.
http://www.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
http://teradatauniversitynetwork.com Teradata University Network fosters education on data warehousing, business intelligence (BI) and database administration (DBA).
PrefaceReferences to Microsoft Windows and Linux
SQL Quick Reference 7
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: [email protected]
References to Microsoft Windows and Linux
This book refers to “Microsoft Windows” and “Linux.” For Teradata Database 13.0, these references mean:
• “Windows” is Microsoft Windows Server 2003 64-bit.
• “Linux” is SUSE Linux Enterprise Server 9 and SUSE Linux Enterprise Server 10.
PrefaceReferences to Microsoft Windows and Linux
8 SQL Quick Reference
SQL Quick Reference 9
Table of Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Supported Software Release . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Changes to This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Additional Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6
References to Microsoft Windows and Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Chapter 1: Data Types and Literals 21
Byte and BLOB Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Character and CLOB Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Data Literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
DateTime and Interval Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Decimal/Numeric Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
PERIOD Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
UDT Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Geospatial Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Default Value Control Phrases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Output Format Phrases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Chapter 2: SQL Functions and Expressions . . . . . . . . . . . . . . . . . . . . . 39
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Arithmetic Operators and Functions/Trigonometric/Hyperbolic Functions . . . . . . . . . . . . . 42
Trigonometric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Hyperbolic Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Attribute Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Built-In Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Table of Contents
10 SQL Quick Reference
CASE Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49
Comparison Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50
Data Type Conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51
Byte Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51
Character-to-Character Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52
Character-to-DATE Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
Character-to-INTERVAL Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
Character-to-Period Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54
Character-to-Numeric Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54
Character-to-TIME Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54
Character-to-TIMESTAMP Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55
Character-to-UDT Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
DATE-to-Character Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
DATE-to-DATE Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57
DATE-to-Numeric Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57
DATE-to-Period Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .58
DATE-to-TIMESTAMP Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .58
DATE-to-UDT Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
INTERVAL-to-Character Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
INTERVAL-to-INTERVAL Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60
INTERVAL-to-Numeric Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61
INTERVAL-to-UDT Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61
Numeric-to-Character Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61
Numeric-to-DATE Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62
Numeric-to-INTERVAL Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62
Numeric-to-Numeric Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
Numeric-to-UDT Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
Period-to-Character Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Period-to-DATE Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Period-to-Period Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Period-to-TIME Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Period-to-TIMESTAMP Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
TIME-to-Character Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
TIME-to-Period Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66
TIME-to-TIME Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66
TIME-to-TIMESTAMP Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .67
TIME-to-UDT Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68
TIMESTAMP-to-Character Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68
Table of Contents
SQL Quick Reference 11
TIMESTAMP-to-DATE Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
TIMESTAMP-to-Period Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
TIMESTAMP-to-TIME Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
TIMESTAMP-to-TIMESTAMP Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
TIMESTAMP-to-UDT Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
UDT-to-Byte Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
UDT-to-Character Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
UDT-to-DATE Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
UDT-to-INTERVAL Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
UDT-to-Numeric Conversion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
UDT-to-TIME Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
UDT-to-TIMESTAMP Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
UDT-to-UDT Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
DateTime and Interval Functions and Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Hash-Related Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Logical Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Ordered Analytical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Period Functions and Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
String Operator and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
UDF Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
UDT Expressions and Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Chapter 3: SQL Data Definition Language . . . . . . . . . . . . . . . . . . . . . . . 95
ALTER FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
ALTER METHOD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
ALTER PROCEDURE (External Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
ALTER PROCEDURE (SQL Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
ALTER REPLICATION GROUP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
ALTER TRIGGER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
ALTER TYPE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
BEGIN LOGGING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
BEGIN QUERY LOGGING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
COLLECT STATISTICS (Optimizer Form). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
COLLECT STATISTICS (Alternate Optimizer Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Table of Contents
12 SQL Quick Reference
COLLECT STATISTICS (Recollect Statistics) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
COMMENT (Comment Placing Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
CREATE AUTHORIZATION/REPLACE AUTHORIZATION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
CREATE CAST/REPLACE CAST. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .109
CREATE DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
CREATE ERROR TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
CREATE FUNCTION/REPLACE FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111
CREATE FUNCTION (Table Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
CREATE GLOBAL TEMPORARY TRACE TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
CREATE GLOP SET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
CREATE HASH INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
CREATE INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122
CREATE JOIN INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123
CREATE MACRO/REPLACE MACRO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .125
CREATE METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .126
CREATE ORDERING/REPLACE ORDERING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .128
CREATE PROCEDURE (External Form)/REPLACE PROCEDURE (External Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
CREATE PROCEDURE (SQL Form)/REPLACE PROCEDURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .131
CREATE PROFILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .138
CREATE RECURSIVE VIEW/REPLACE RECURSIVE VIEW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .139
CREATE REPLICATION GROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .140
CREATE REPLICATION RULESET/REPLACE REPLICATION RULESET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141
CREATE ROLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141
CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
CREATE TABLE (Queue Table Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .151
CREATE TRANSFORM/REPLACE TRANSFORM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .156
CREATE TRIGGER/REPLACE TRIGGER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157
CREATE TYPE (Distinct Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158
CREATE TYPE (Structured Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .160
CREATE USER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .164
Table of Contents
SQL Quick Reference 13
CREATE VIEW/REPLACE VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
DELETE DATABASEDELETE USER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
DROP AUTHORIZATION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
DROP CAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
DROP DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
DROP ERROR TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
DROP FUNCTION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
DROP GLOP SET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
DROP HASH INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
DROP INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
DROP JOIN INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
DROP MACRO/DROP PROCEDURE/DROP TABLE/DROP TRIGGER/DROP VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
DROP ORDERING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
DROP PROFILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
DROP REPLICATION GROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
DROP REPLICATION RULESET. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
DROP ROLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
DROP STATISTICS (Optimizer Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
DROP TRANSFORM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
DROP TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
DROP USER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
END LOGGING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
END QUERY LOGGING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
LOGGING ONLINE ARCHIVE OFF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
LOGGING ONLINE ARCHIVE ON. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
MODIFY DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
MODIFY PROFILE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
MODIFY USER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
RENAME FUNCTION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
RENAME MACRO/RENAME PROCEDURE/RENAME TABLE/
Table of Contents
14 SQL Quick Reference
RENAME TRIGGER/RENAME VIEW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184
REPLACE METHOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .185
SET QUERY_BAND. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187
SET ROLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
SET SESSION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
SET SESSION ACCOUNT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL. . . . . . . . . .189
SET SESSION COLLATION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .189
SET SESSION DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .189
SET SESSION DATEFORM. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .189
SET SESSION FUNCTION TRACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190
SET SESSION OVERRIDE REPLICATION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190
SET SESSION SUBSCRIBER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190
SET TIME ZONE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190
HELP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .191
HELP CAST. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193
HELP COLUMN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193
HELP CONSTRAINT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .194
HELP DATABASE/HELP USER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195
HELP ERROR TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195
HELP FUNCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195
HELP HASH INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .197
HELP INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .197
HELP JOIN INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .198
HELP MACRO/HELP TABLE/HELP VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .198
HELP METHOD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .198
HELP PROCEDURE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199
HELP REPLICATION GROUP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199
HELP SESSION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199
HELP STATISTICS (Optimizer Form). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199
HELP STATISTICS (QCD Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .200
HELP TRANSFORM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .200
HELP TRIGGER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .200
HELP TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .201
HELP VOLATILE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .201
Table of Contents
SQL Quick Reference 15
HELP (Online Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
SHOW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
SHOW CAST/SHOW ERROR TABLE/SHOW FUNCTION/SHOW HASH INDEX/SHOW JOIN INDEX/SHOW MACRO/SHOW METHOD/SHOW PROCEDURE/SHOW REPLICATION GROUP/SHOW TABLE/SHOW TRIGGER/SHOW TYPE/SHOW VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
SHOW QUERY LOGGING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Chapter 4: SQL Data Control Language . . . . . . . . . . . . . . . . . . . . . . . . . 209
GIVE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
GRANT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
GRANT CONNECT THROUGH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
GRANT LOGON. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
REVOKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
REVOKE CONNECT THROUGH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
REVOKE LOGON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Chapter 5: SQL Data Manipulation Language . . . . . . . . . . . . . . . . . . 219
SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
SELECT AND CONSUME. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
WITH [RECURSIVE] Request Modifier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
DISTINCT, ALL, and .ALL Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
TOP n Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
FROM Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
HASH BY Clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
LOCAL ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Subqueries in Search Conditions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Table of Contents
16 SQL Quick Reference
GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .229
HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .229
QUALIFY Clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .230
SAMPLE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .230
SAMPLEID Expression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .230
ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231
WITH Clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231
Outer Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231
Null. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231
ABORT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .232
BEGIN TRANSACTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .232
CALL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .232
CHECKPOINT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233
COMMENT (Comment-Retrieving Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233
COMMIT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .233
DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .234
ECHO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
END TRANSACTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
EXECUTE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
INSERT/INSERT . . . SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
LOCKING Request Modifier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236
MERGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237
ROLLBACK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238
UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238
USING Request Modifier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .239
COLLECT DEMOGRAPHICS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240
COLLECT STATISTICS (QCD Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .240
DROP STATISTICS (QCD Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .241
DUMP EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .241
EXPLAIN Request Modifier. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .241
INITIATE INDEX ANALYSIS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .242
INITIATE PARTITION ANALYSIS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .242
INSERT EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243
RESTART INDEX ANALYSIS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .243
DIAGNOSTIC COSTPRINT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244
DIAGNOSTIC DUMP COSTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244
DIAGNOSTIC HELP COSTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244
DIAGNOSTIC SET COSTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245
Table of Contents
SQL Quick Reference 17
DIAGNOSTIC HELP PROFILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
DIAGNOSTIC SET PROFILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
DIAGNOSTIC DUMP SAMPLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
DIAGNOSTIC HELP SAMPLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
DIAGNOSTIC SET SAMPLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
DIAGNOSTIC “Validate Index” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Chapter 6: SQL Cursor Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
CLOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
DECLARE CURSOR. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
FETCH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
OPEN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
POSITION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
PREPARE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
REWIND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
SELECT ... INTO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
SELECT AND CONSUME ... INTO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
UPDATE (Positioned Form) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Chapter 7: SQL Stored Procedures: Control Statements and Condition Handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
BEGIN - END Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
CASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
DECLARE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
FOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
IF. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
ITERATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
LEAVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
LOOP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
REPEAT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
SET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
DECLARE CONDITION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
DECLARE HANDLER (Basic Syntax) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
Table of Contents
18 SQL Quick Reference
SIGNAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .279
RESIGNAL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .279
GET DIAGNOSTICS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .280
Chapter 8: Static Embedded SQL Statements . . . . . . . . . . . . . . . . . .281
BEGIN DECLARE SECTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281
COMMENT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281
DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281
DECLARE STATEMENT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .282
DECLARE TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .282
END DECLARE SECTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .282
END-EXEC Statement Terminator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .282
EXEC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283
EXEC SQL Statement Prefix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283
INCLUDE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283
INCLUDE SQLCA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283
INCLUDE SQLDA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .284
WHENEVER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .284
Chapter 9: Dynamic Embedded SQL Statements. . . . . . . . . . . . . . .285
DESCRIBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .285
EXECUTE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .285
EXECUTE IMMEDIATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .286
PREPARE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .286
Chapter 10: SQL Client-Server Connectivity Statements . . . .287
CONNECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .287
GET CRASH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .287
LOGOFF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .287
LOGON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .288
SET BUFFERSIZE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .288
SET CHARSET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .288
SET CONNECTION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .288
Table of Contents
SQL Quick Reference 19
SET CRASH. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
SET ENCRYPTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Chapter 11: Multisession Asynchronous Programming With Embedded SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
ASYNC Statement Modifier. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
TEST. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
WAIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Appendix A: How to Read Syntax Diagrams . . . . . . . . . . . . . . . . . . . 293
Syntax Diagram Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Table of Contents
20 SQL Quick Reference
SQL Quick Reference 21
CHAPTER 1 Data Types and Literals
Byte and BLOB Data Types
BLOB Data Type
BYTE Data Type
VARBYTE Data Type
BINARY LARGE OBJECT
BLOB
KMG
n( )
1101B095
attribute
1101A430
BYTE
( n ) attributes
1101E171
VARBYTE ( n )
attributes
Chapter 1: Data Types and LiteralsCharacter and CLOB Data Types
22 SQL Quick Reference
Character and CLOB Data Types
CASESPECIFIC Phrase
CHARACTER SET Phrase
CHARACTER Data Type
CLOB Data Type
FF07D205
NOT
CASESPECIFIC
CS
1101A409
CHARACTER SET
CHAR SET
LATIN
KANJISJIS
KANJI1
GRAPHIC
UNICODE
1101F190
CHARACTER
n( )CHAR server_character_setCHARACTER SET
CHAR SET
attributes
FF07D234
n(
GRAPHIC
) attributes
CHARACTER LARGE OBJECT
CLOB
KMG
n( )
1101B093
A
A
CHARACTER SET
CHAR SET
attributeLATIN
UNICODE
Chapter 1: Data Types and LiteralsData Literals
SQL Quick Reference 23
UPPERCASE Phrase
VARCHAR Data Type
Data Literals
CHARACTER String Literals
FF07D206
UPPERCASE
UC
1101G191
VARCHAR
CHARACTER VARYING
CHAR VARYING
server_character_setCHARACTER SET
CHAR SET
attributes
A
A
( n )
FF07D192
LONG VARCHAR
attributes
FF07D235
n(VARGRAPHIC )
attributes
FF07D236
LONG VARGRAPHIC
attributes
1101A009
'string'
_character_set
Chapter 1: Data Types and LiteralsData Literals
24 SQL Quick Reference
DATE Literals
DECIMAL Literals
FLOATING POINT Literals
GRAPHIC Literals
Hexadecimal Byte Literals
Hexadecimal Character Literals
1101A036
DATE ' string '
KR01A143
± n.
±.n
±n.n
KR01A142
± nE± m
± n.E± m
±.nE± m
±n.nE± m
HH01A090
G '< ABC >'
1101A391
'hexadecimal digits' XBVF
1101A392
'hexadecimal digits' XCVF
_character_set
Chapter 1: Data Types and LiteralsData Literals
SQL Quick Reference 25
Hexadecimal Integer Literals
Hexadecimal Name Literals
INTEGER Literals
INTERVAL DAY Literals
INTERVAL DAY TO HOUR Literals
1101A390
'hexadecimal digits' X
I1248
1101A494
XN'hexadecimal_name_body '
KR01A141
± n
1101A026
INTERVAL
sign
DAY' string '
1101A027
INTERVAL
sign
DAY TO HOUR' string '
Chapter 1: Data Types and LiteralsData Literals
26 SQL Quick Reference
INTERVAL DAY TO MINUTE Literals
INTERVAL DAY TO SECOND Literals
INTERVAL HOUR Literals
INTERVAL HOUR TO MINUTE Literals
INTERVAL HOUR TO SECOND Literals
1101A028
INTERVAL
sign
DAY TO MINUTE' string '
1101A029
INTERVAL
sign
DAY TO SECOND' string '
1101A030
INTERVAL
sign
HOUR' string '
1101A037
INTERVAL
sign
HOUR TO MINUTE' string '
1101A038
INTERVAL
sign
HOUR TO SECOND' string '
Chapter 1: Data Types and LiteralsData Literals
SQL Quick Reference 27
INTERVAL MINUTE Literals
INTERVAL MINUTE TO SECOND Literals
INTERVAL MONTH Literals
INTERVAL SECOND Literals
INTERVAL YEAR Literals
1101A031
INTERVAL
sign
MINUTE' string '
1101A032
INTERVAL
sign
MINUTE TO SECOND' string '
1101A025
INTERVAL
sign
MONTH' string '
1101A033
INTERVAL
sign
SECOND' string '
1101A023
INTERVAL
sign
YEAR' string '
Chapter 1: Data Types and LiteralsData Literals
28 SQL Quick Reference
INTERVAL YEAR TO MONTH Literals
Period Literals
Time Literals
Timestamp Literals
Unicode Character String Literals
1101A024
INTERVAL
sign
YEAR TO MONTH' string '
1101A591
PERIOD
UNTIL_CHANGED,-‘(beginning_bound ending_bound )’
1101A021
TIME ' string '
1101A022
TIMESTAMP ' string '
1101A493
U& 'Unicode_string_body '
U& ' ' 'Unicode_string_body '
' '
UESCAPE 'Unicode_esc_char '
_character_set
A
A
Chapter 1: Data Types and LiteralsDateTime and Interval Data Types
SQL Quick Reference 29
Unicode Delimited Identifier
DateTime and Interval Data Types
DATE Data Type
INTERVAL DAY Data Type
INTERVAL DAY TO HOUR Data Type
INTERVAL DAY TO MINUTE Data Type
1101A495
U&" Unicode_delimiter_body " UESCAPE 'Unicode_esc_char '
FF07D139
DATE
attributes
FF07D123
INTERVAL DAY
( )precision attributes
FF07D124
INTERVAL DAY
( )precision
TO HOUR
attributes
FF07D125
INTERVAL DAY
( )precision
TO MINUTE
attributes
Chapter 1: Data Types and LiteralsDateTime and Interval Data Types
30 SQL Quick Reference
INTERVAL DAY TO SECOND Data Type
INTERVAL HOUR Data Type
INTERVAL HOUR TO MINUTE Data Type
INTERVAL HOUR TO SECOND Data Type
INTERVAL MINUTE Data Type
FF07D126
INTERVAL DAY
( )precision
TO SECOND
( )fractional_seconds_precision attributes
FF07D127
INTERVAL HOUR
( )precision attributes
FF07D128
INTERVAL HOUR
( )precision
TO MINUTE
attributes
FF07D102
TO SECONDINTERVAL HOUR
( )precision ( )fractional_seconds_precision attributes
FF07D103
INTERVAL MINUTE
( )precision attributes
Chapter 1: Data Types and LiteralsDateTime and Interval Data Types
SQL Quick Reference 31
INTERVAL MINUTE TO SECOND Data Type
INTERVAL MONTH Data Type
INTERVAL SECOND Data Type
INTERVAL YEAR Data Type
INTERVAL YEAR TO MONTH Data Type
TIME Data Type
FF07D104
TO SECONDINTERVAL MINUTE
( )precision ( )fractional_seconds_precision attributes
1101A013
INTERVAL MONTH
attributes(precision)
FF07D105
INTERVAL SECOND
(precision )
,fractional_seconds_precision
attributes
FF07D121
INTERVAL YEAR
( )precision attributes
FF07D122
INTERVAL YEAR
( )precision
TO MONTH
attributes
FF07D117
TIME
( )fractional_seconds_precision attributes
Chapter 1: Data Types and LiteralsDecimal/Numeric Data Types
32 SQL Quick Reference
TIME WITH TIME ZONE Data Type
TIMESTAMP Data Type
TIMESTAMP WITH TIME ZONE Data Type
Decimal/Numeric Data Types
BIGINT Data Type
BYTEINT Data Type
FF07D119
TIME
( )fractional_seconds_precision
WITH TIME ZONE
attributes
FF07D118
TIMESTAMP
( )fractional_seconds_precision attributes
FF07D120
TIMESTAMP
( )fractional_seconds_precision
WITH TIME ZONE
attributes
1101A393
attributesBIGINT
FF07D186
BYTEINT
attributes
Chapter 1: Data Types and LiteralsPERIOD Data Types
SQL Quick Reference 33
DECIMAL/NUMERIC Data Types
FLOAT/REAL/DOUBLE PRECISION Data Types
INTEGER Data Type
SMALLINT Data Type
PERIOD Data Types
PERIOD(DATE) Data Type
1101A455
DECIMAL
n( )DEC
NUMERIC ,m
attributes
FF07D189
FLOAT
REAL
DOUBLE PRECISION
attributes
FF07D188
INTEGER
INT attributes
FF07D187
SMALLINTattributes
PERIOD(DATE)
attributes1101A571
Chapter 1: Data Types and LiteralsUDT Data Types
34 SQL Quick Reference
PERIOD(TIME) Data Type
PERIOD(TIME WITH TIME ZONE) Data Type
PERIOD(TIMESTAMP) Data Type
PERIOD(TIMESTAMP WITH TIME ZONE) Data Type
UDT Data Types
UDT Data Type
VARIANT_TYPE UDT
PERIOD(TIME )
(fractional_seconds_precision)
1101A617
attributes
PERIOD(TIME WITH TIME ZONE )
(fractional_seconds_precision)
1101A618
attributes
PERIOD(TIMESTAMP )
(fractional_seconds_precision)
1101A619
attributes
PERIOD(TIMESTAMP WITH TIME ZONE )
(fractional_seconds_precision)
1101A620
attributes
udt_name
SYSUDTLIB.
1101A366
attribute
parameter_name VARIANT_TYPE
1101A575
Chapter 1: Data Types and LiteralsGeospatial Data Types
SQL Quick Reference 35
Geospatial Data Types
MBR Type
ST_Geometry Type
Well-Known Text Representation
1181A002
MBR
SYSUDTLIB.MBR attribute
1181A001
ST_GEOMETRY
SYSUDTLIB.ST_GEOMETRY attribute
1181A003
point
geometrycollection
geosequence
multisurface
multicurve
multipoint
surface
curve
1181A004
POINT EMPTY
( x y )
point
curve
1181A005
LINESTRING EMPTY,
( x y )
Chapter 1: Data Types and LiteralsGeospatial Data Types
36 SQL Quick Reference
surface
1181A006
POLYGON EMPTY
EMPTY )(,
,
( x y )
multipoint
1181A007
MULTIPOINT EMPTY
EMPTY )(
,
( x y )
multicurve
1181A008
MULTILINESTRING EMPTY
EMPTY )(,
,
( x y )
multisurface
1181A009
MULTIPOLYGON EMPTY
EMPTY )(
EMPTY )(,
,
,
( x y )
1181A010
geometrycollection
point
curve
surface
multipoint
multicurve
multisurface
geometrycollection
geosequence
GEOMETRYCOLLECTION EMPTY,
( )
Chapter 1: Data Types and LiteralsDefault Value Control Phrases
SQL Quick Reference 37
Default Value Control Phrases
COMPRESS Phrase
DEFAULT Phrase
NOT NULL Phrase
WITH DEFAULT Phrase
geosequence
1181A018
GEOSEQUENCE EMPTY
,
(( x y ) ,
,
( ts ) ,
n n ,
( linkID ) ), ,
n n
( ufcount
count count
)
n n
COMPRESS
constant
constant
1101A075
,
( )
255
NULL
FF07R018
DEFAULT constant_valueDATE quotestringTIME quotestringTIMESTAMP quotestringINTERVAL quotestring qualifier
sign
FF07D148
NOT NULL
FF07D149
WITH DEFAULT
Chapter 1: Data Types and LiteralsOutput Format Phrases
38 SQL Quick Reference
Output Format Phrases
AS
FORMAT
NAMED
TITLE
FF07D146
value_expression
AS
name
FF07D250
FORMAT 'format_string'
(expression) name)(NAMED
FF07D147
FF07D145
TITLE quotestring
SQL Quick Reference 39
CHAPTER 2 SQL Functions and Expressions
Aggregate Functions
AVG
CORR
COUNT
COVAR_POP
COVAR_SAMP
GROUPING
1101B410
AVERAGE ( )value_expression
AVG DISTINCT
AVE ALL
1101B217
CORR value_expression_1, value_expression_2( )
1101A411
COUNT ( )value_expression
DISTINCT
*
ALL
1101B216
COVAR_POP value_expression_1, value_expression_2( )
1101A456
COVAR_SAMP value_expression_1, value_expression_2( )
1101A461
GROUPING expression( (
Chapter 2: SQL Functions and ExpressionsAggregate Functions
40 SQL Quick Reference
KURTOSIS
MAX
MIN
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
1101B215
KURTOSIS value_expression( )
DISTINCTALL
1101B412
MAXIMUM ( )value_expression
DISTINCTALL
MAX
1101B413
MINIMUM ( )value_expression
MIN DISTINCTALL
1101B414
REGR_AVGX dependent_variable_expression, independent_variable_expression( )
1101B415
REGR_AVGY dependent_variable_expression, independent_variable_expression( )
1101B416
REGR_COUNT dependent_variable_expression, independent_variable_expression( )
1101B417
REGR_INTERCEPT dependent_variable_expression, independent_variable_expression ( )
1101B418
REGR_R2 dependent_variable_expression, independent_variable_expression( )
Chapter 2: SQL Functions and ExpressionsAggregate Functions
SQL Quick Reference 41
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
SKEW
STDDEV_POP
STDDEV_SAMP
SUM
1101B419
REGR_SLOPE dependent_variable_expression, independent_variable_expression( )
1101B420
REGR_SXX dependent_variable_expression, independent_variable_expression( )
1101B421
REGR_SXY dependent_variable_expression, independent_variable_expression( )
1101B422
REGR_SYY dependent_variable_expression, independent_variable_expression( )
1101B428
SKEW value_expression( )
DISTINCTALL
1101B424
STDDEV_POP value_expression( )
DISTINCTALL
1101B425
STDDEV_SAMP value_expression( )
DISTINCTALL
1101B423
SUM ( )value_expression
DISTINCTALL
Chapter 2: SQL Functions and ExpressionsArithmetic Operators and Functions/Trigonometric/Hyperbolic Functions
42 SQL Quick Reference
VAR_POP
VAR_SAMP
Arithmetic Operators and Functions/Trigonometric/Hyperbolic Functions
Arithmetic Operators
Teradata Database supports the following arithmetic operators:
1101B426
VAR_POP value_expression( )
DISTINCTALL
1101B427
VAR_SAMP value_expression( )
DISTINCTALL
Operator Function
** Exponentiate
This is a Teradata extension to the ANSI SQL-99 standard.
* Multiply
/ Divide
MOD Modulo (remainder).
MOD calculates the remainder in a division operation.
For example, 60 MOD 7 = 4: 60 divided by 7 equals 8, with a remainder of 4. The result takes the sign of the dividend, thus:
-17 MOD 4 = -1
-17 MOD -4 = -1
17 MOD -4 = 1
17 MOD 4 = 1
This is a Teradata extension to the ANSI SQL-99 standard.
+ Add
- Subtract
+ Unary plus (positive value)
- Unary minus (negative value)
Chapter 2: SQL Functions and ExpressionsArithmetic Operators and Functions/Trigonometric/Hyperbolic Functions
SQL Quick Reference 43
ABS
CASE_N
DEGREES/ RADIANS
EXP
LN
LOG
1101A480
ABS arg( )
1101A069
A
A
NO CASE
UNKNOWN
OR UNKNOWN
, UNKNOWN
,
)
CASE_N
,
conditional_expression(
1101A481
DEGREES ( arg )
RADIANS
1101A484
EXP arg( )
1101A485
LN arg( )
1101A486
LOG arg( )
Chapter 2: SQL Functions and ExpressionsArithmetic Operators and Functions/Trigonometric/Hyperbolic Functions
44 SQL Quick Reference
NULLIFZERO
RANDOM
RANGE_N
SQRT
1101F225
NULLIFZERO ( )arg
1101C025
RANDOM lower_bound, upper_bound( )
NO RANGE
UNKNOWN
OR UNKNOWN
, UNKNOWN
,
BETWEENRANGE_N test_expression(
1101B068
A B
B
C
D
C
A
start_expression end_expressionAND
* EACH range_size
* end_expressionAND
*
start_expression
end_expressionAND EACH range_size
*
D
| range_list |
)
end_expressionAND
start_expression,
end_expressionAND EACH range_size
start_expression, end_expressionAND
* EACH range_size
range_list
1101A487
SQRT arg( )
Chapter 2: SQL Functions and ExpressionsTrigonometric Functions
SQL Quick Reference 45
WIDTH_BUCKET
ZEROIFNULL
Trigonometric Functions
COS, SIN, TAN, ACOS, ASIN, ATAN, ATAN2
Hyperbolic Functions
COSH, SINH, TANH, ACOSH, ASINH, ATANH
1101A492
WIDTH BUCKET value_expression, lower_bound, upper_bound, partition_count( )
1101F226
ZEROIFNULL ( )arg
1101A482
COS ( arg )
SIN
TAN
ACOS
ASIN
ATAN
ATAN2 ( x, y )
1101A483
COSH ( arg )
SINH
TANH
ACOSH
ASINH
ATANH
Chapter 2: SQL Functions and ExpressionsAttribute Functions
46 SQL Quick Reference
Attribute Functions
BYTES
CHARACTERS
CHARACTER_LENGTH
DEFAULT
FORMAT
OCTET_LENGTH
TITLE
1101F174
BYTE ( byte_expression
BYTES
(
1101A488
string_expressionCHARACTERS ( )
CHARS
CHAR
FF07D088
CHARACTER_LENGTH (string_expression)CHAR_LENGTH
1101A394
DEFAULT( column_name )
1101A489
column_nameFORMAT ( )
1101A513
OCTET_LENGTH string_expression( )
, character_set_name
1101B039
TITLE )expression(
Chapter 2: SQL Functions and ExpressionsBuilt-In Functions
SQL Quick Reference 47
TYPE
Built-In Functions
ACCOUNT
CURRENT_DATE
CURRENT_ROLE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
1101A491
expressionTYPE ( )
FF07R001
ACCOUNT
FF07D135
CURRENT_DATE
1101A565
CURRENT_ROLE
FF07D136
CURRENT_TIME
( )fractional_precision
FF07D137
CURRENT_TIMESTAMP
( )fractional_precision
1101A564
CURRENT_USER
Chapter 2: SQL Functions and ExpressionsBuilt-In Functions
48 SQL Quick Reference
DATABASE
DATE
PROFILE
ROLE
SESSION
TIME
USER
FF07R002
DATABASE
FF07D134
DATE
PROFILE
KZ01A006
ROLE
KZ01A007
FF07R003
SESSION
FF07D271
TIME
FF07D272
USER
Chapter 2: SQL Functions and ExpressionsCASE Expressions
SQL Quick Reference 49
CASE Expressions
Valued CASE Expression
Searched CASE Expression
COALESCE Expression
NULLIF Expression
1101A012
value_expression_1CASE
END
A
B
scalar_expression_mELSE
WHEN BA value_expression_n THEN scalar_expression_n
FF07D224
CASE
END
A
A
scalar_expression_mELSE
WHEN search_condition_n THEN scalar_expression_n
1101E227
COALESCE
2,
( )scalar_expression_n
HH01B094
NULLIF scalar_expression1, scalar_expression2( )
Chapter 2: SQL Functions and ExpressionsComparison Operators
50 SQL Quick Reference
Comparison Operators
Teradata Database supports the following comparison operators:
Comparison Operators
Comparison Operators in Logical Expressions
ANSI Operator Teradata Extensions Function
= EQ Tests for equality.
<> ^=
NE
NOT=
Tests for inequality.
< LT Tests for less than.
<= LE Tests for less than or equal.
> GT Tests for greater than.
>= GE Tests for greater than or equal.
FF07D160
scalar_expression comparison_operator scalar_expression
1101D219
expression_1 expression_2
quantifier
,operator
expression_1 quantifieroperator constant( )
expression_1 operator subquery( )
quantifier
operator subquery( )expression_1( )
,
Chapter 2: SQL Functions and ExpressionsData Type Conversions
SQL Quick Reference 51
Data Type Conversions
CAST
Teradata Conversion Syntax
Byte Conversion
CAST
1101A627
CAST AS ansi_sql_data_type
data_definition_list
expression( )
1101A626
expression ( data_type
, data_attribute
, data_type
)
, data_attribute
,data_attribute
CAST ASbyte_expression byte_data_type
data_attribute
UDT_data_type
( (
1101B335
data_attribute
Chapter 2: SQL Functions and ExpressionsCharacter-to-Character Conversion
52 SQL Quick Reference
Teradata Conversion
Character-to-Character Conversion
CAST
Teradata Conversion
1101A623
byte_expression ( byte_data_type
, data_attribute
, byte_data_type
)
, data_attribute
,data_attribute
CAST AScharacter_expression character_data_type
data_attribute
( (
1101A625
data_attribute
1101A624
character_expression ( character_data_type
, data_attribute
, character_data_type
)
, data_attribute
,data_attribute
Chapter 2: SQL Functions and ExpressionsCharacter-to-DATE Conversion
SQL Quick Reference 53
Character-to-DATE Conversion
CAST
Teradata Conversion
Character-to-INTERVAL Conversion
CAST
Teradata Conversion
1101B244
CAST AS )character_expression DATE(
data_attribute
1101B255
character_expression
data_attribute ,
DATE )(
, data_attribute
1101B245
CAST AS )character_expression interval_data_type(
data_attribute
1101B256
character_expression interval_data_type )(
data_attribute , , data_attribute
Chapter 2: SQL Functions and ExpressionsCharacter-to-Period Conversion
54 SQL Quick Reference
Character-to-Period Conversion
CAST
Character-to-Numeric Conversion
CAST
Teradata
Character-to-TIME Conversion
CAST
CAST AScharacter_expression period_data_type
data_attribute
( )
1101A587
1101A628
(character_expression numeric_data_type
data_attribute
)CAST AS
1101A629data_attribute , , data_attribute
character_expression ( numeric_data_type )
1101A246
CAST AScharacter_expression
)
( A
time_data_attributeWITH TIME ZONE
TIME
(fractional_seconds_precision)
A
Chapter 2: SQL Functions and ExpressionsCharacter-to-TIMESTAMP Conversion
SQL Quick Reference 55
Teradata Conversion
Character-to-TIMESTAMP Conversion
CAST
Teradata Conversion
1101B257
character_expression
(fractional_seconds_precision)
TIME(
)
A
WITH TIME ZONE
, data_attribute
A
data_attribute ,
1101A247
CAST AS TIMESTAMPcharacter_expression
)
( A
timestamp_data_attributeWITH TIME ZONE
(fractional_seconds_precision)
A
1101B258
character_expression
(fractional_seconds_precision)
TIMESTAMP
)
( A
A
data_attribute ,
WITH TIME ZONE
, data_attribute
Chapter 2: SQL Functions and ExpressionsCharacter-to-UDT Conversion
56 SQL Quick Reference
Character-to-UDT Conversion
CAST
DATE-to-Character Conversion
CAST
Teradata Conversion
CAST AScharacter_expression UDT_data_definition( (
1101A336
1101A248
CAST AS character_data_typedate_expression
)
( A
character_data_attributeCHARACTER SET server_character_set
A
1101B259
data_expression character_data_type(
)
A
CHARACTER SET server_character_set
, data_attribute
A
data_attribute ,
Chapter 2: SQL Functions and ExpressionsDATE-to-DATE Conversion
SQL Quick Reference 57
DATE-to-DATE Conversion
CAST
Teradata Conversion
DATE-to-Numeric Conversion
CAST
1101A249
date_expression
date_data_attribute
DATEASCAST )(
date_data_attribute
1101B260
date_expression
data_attribute
DATE
, DATE
)(
,
, data_attribute
, data_attribute
1101A250
CAST AS )date_expression numeric_data_type
numeric_data_attribute
(
Chapter 2: SQL Functions and ExpressionsDATE-to-Period Conversion
58 SQL Quick Reference
Teradata Conversion
DATE-to-Period Conversion
CAST
DATE-to-TIMESTAMP Conversion
CAST
1101B261
date_expression numeric_data_type )(
, data_attributedata_attribute ,
CAST ASdate_expression period_data_type( )
1101A589period_data_attribute
1101A251
date_expression
(fractional_seconds_precision)
TIMESTAMPASCAST
)
( A
timestamp_data_attributeWITH TIME ZONE
A
Chapter 2: SQL Functions and ExpressionsDATE-to-UDT Conversion
SQL Quick Reference 59
Teradata Conversion
DATE-to-UDT Conversion
CAST
INTERVAL-to-Character Conversion
CAST
1101B262
date_expression
(fractional_seconds_precision)
TIMESTAMP
)
( A
A
data_attribute ,
WITH TIME ZONE
, data_attribute
CAST ASdate_expression UDT_data_definition( (
1101A337
1101A252
CAST interval_expression character_data_typeAS
)
( A
character_data_attributeserver_character_setCHARACTER SET
A
Chapter 2: SQL Functions and ExpressionsINTERVAL-to-INTERVAL Conversion
60 SQL Quick Reference
Teradata Conversion
INTERVAL-to-INTERVAL Conversion
CAST
Teradata Conversion
1101B263
interval_expression character_data_type(
)
A
CHARACTER SET server_character_set
, data_attribute
A
data_attribute ,
1101A253
CAST AS )interval_expression interval_data_type
interval_data_attribute
(
interval_data_attribute
1101B264
interval_expression
, data_attribute
, interval_data_type
interval_data_type )(
data_attribute
,
, data_attribute
Chapter 2: SQL Functions and ExpressionsINTERVAL-to-Numeric Conversion
SQL Quick Reference 61
INTERVAL-to-Numeric Conversion
CAST
Teradata Conversion
INTERVAL-to-UDT Conversion
CAST
Numeric-to-Character Conversion
CAST
1101A254
CAST AS )interval_expression numeric_data_type
numeric_data_attribute
(
1101B265
interval_expression
data_attribute ,
numeric_data_type )(
, data_attribute
CAST ASinterval_expression UDT_data_definition( (
1101A338
1101A630data_attribute
(numeric_expression character_data_type )CAST AS
Chapter 2: SQL Functions and ExpressionsNumeric-to-DATE Conversion
62 SQL Quick Reference
Teradata Conversion
Numeric-to-DATE Conversion
CAST
Teradata Conversion
Numeric-to-INTERVAL Conversion
CAST
1101A631
numeric_expression character_data_type(
)
A
CHARACTER SET server_character_set
, data_attribute
A
data_attribute ,
1101B077
numeric_expressionCAST AS DATE( )
data_attribute
1101B385
numeric_expression
data_attribute ,
DATE )(
, data_attribute
1101A281
CAST AS )numeric_expression interval_data_type
interval_data_attribute
(
Chapter 2: SQL Functions and ExpressionsNumeric-to-Numeric Conversion
SQL Quick Reference 63
Teradata Conversion
Numeric-to-Numeric Conversion
CAST
Teradata Conversion
Numeric-to-UDT Conversion
CAST
1101B273
numeric_expression
data_attribute ,
interval_data_type )(
, data_attribute
1101A632
( numeric_expression numeric_data_typenumeric_data_attribute
)CAST AS
numeric_data_attribute
1101A633
numeric_expression
, data_attribute
, numeric_data_type
numeric_data_type )(
data_attribute
,
, data_attribute
CAST ASnumeric_expression UDT_data_definition( (
1101A334
Chapter 2: SQL Functions and ExpressionsPeriod-to-Character Conversion
64 SQL Quick Reference
Period-to-Character Conversion
CAST
Period-to-DATE Conversion
CAST
Period-to-Period Conversion
CAST
CAST ASperiod_expression character_data_type(
1101A598
CHARACTER SET server_character_set
)
A
A
character_data_attribute
CAST AS DATEperiod_expression( )
1101A600date_data_attribute
CAST period_expression period_data_type
period_data_attribute
AS )(
1101A568period_data_attribute
Chapter 2: SQL Functions and ExpressionsPeriod-to-TIME Conversion
SQL Quick Reference 65
Period-to-TIME Conversion
CAST
Period-to-TIMESTAMP Conversion
CAST
TIME-to-Character Conversion
CAST
CAST ASperiod_expression
(fractional_seconds_precision) time_data_attributeWITH TIME ZONE
(
)
1101A604
TIME
A
A
CAST AS TIMESTAMPperiod_expression
(fractional_seconds_precision)
timestamp_data_attributeWITH TIME ZONE
(
)
1101A605
A
A
1101A266
CAST AS character_data_typetime_expression
)
( A
character_data_attributeCHARACTER SET server_character_set
A
Chapter 2: SQL Functions and ExpressionsTIME-to-Period Conversion
66 SQL Quick Reference
Teradata Conversion
TIME-to-Period Conversion
CAST
TIME-to-TIME Conversion
CAST
1101B274
time_expression character_data_type(
)
A
CHARACTER SET server_character_set
, data_attribute
A
data_attribute ,
CAST AStime_expression period_data_type
period_data_attribute
( )
1101A610
1101A267
time_expression
(fractional_seconds_precision)
TIME
ASCAST
)
( A
time_data_attributeWITH TIME ZONE
A
time_data_attribute
Chapter 2: SQL Functions and ExpressionsTIME-to-TIMESTAMP Conversion
SQL Quick Reference 67
Teradata Conversion
TIME-to-TIMESTAMP Conversion
CAST
Teradata Conversion
1101B275
time_expression
(fractional_seconds_precision)
TIME(
(fractional_seconds_precision)
B
WITH TIME ZONE
, data_attribute
A
B
A
)
WITH TIME ZONE
, data_attribute
, TIME
,
data_attribute
1101A268
time_expression
(fractional_seconds_precision)
TIMESTAMPASCAST
)
( A
timestamp_data_attributeWITH TIME ZONE
A
1101B276
time_expression
(fractional_seconds_precision)
TIMESTAMP
)
( A
A
data_attribute ,
WITH TIME ZONE
, data_attribute
Chapter 2: SQL Functions and ExpressionsTIME-to-UDT Conversion
68 SQL Quick Reference
TIME-to-UDT Conversion
CAST
TIMESTAMP-to-Character Conversion
CAST
Teradata Conversion
CAST AStime_expression UDT_data_definition( (
1101A340
1101A269
CAST AS character_data_typetimestamp_expression
)
( A
character_data_attributeCHARACTER SET server_character_set
A
1101B277
timestamp_expression character_data_type(
)
A
CHARACTER SET server_character_set
, data_attribute
A
data_attribute ,
Chapter 2: SQL Functions and ExpressionsTIMESTAMP-to-DATE Conversion
SQL Quick Reference 69
TIMESTAMP-to-DATE Conversion
CAST
Teradata Conversion
TIMESTAMP-to-Period Conversion
CAST
TIMESTAMP-to-TIME Conversion
CAST
1101A270
CAST AS )timestamp_expression DATE
date_data_attribute
(
1101B278
timestamp_expression
data_attribute ,
DATE )(
, data_attribute
CAST AStimestamp_expression period_data_type
period_data_attribute
( )
1101A608
1101A271
timestamp_expression
(fractional_seconds_precision)
TIME
ASCAST
)
( A
time_data_attributeWITH TIME ZONE
A
Chapter 2: SQL Functions and ExpressionsTIMESTAMP-to-TIMESTAMP Conversion
70 SQL Quick Reference
Teradata Conversion
TIMESTAMP-to-TIMESTAMP Conversion
CAST
1101B279
timestamp_expression
(fractional_seconds_precision)
TIME
)
( A
A
data_attribute ,
WITH TIME ZONE
, data_attribute
1101A272
timestamp_expression
(fractional_seconds_precision)
TIMESTAMP
ASCAST
)
( A
data_attributeWITH TIME ZONE
A
data_attribute
Chapter 2: SQL Functions and ExpressionsTIMESTAMP-to-UDT Conversion
SQL Quick Reference 71
Teradata Conversion
TIMESTAMP-to-UDT Conversion
CAST
UDT-to-Byte Conversion
CAST
1101B280
timestamp_expression
(fractional_seconds_precision)
TIMESTAMP(
(fractional_seconds_precision)
C
WITH TIME ZONE
, data_attribute
B
C
B
A
A
)
WITH TIME ZONE
, data_attribute
, TIMESTAMP
,
data_attribute
CAST AStimestamp_expression UDT_data_definition( (
1101A341
1101A344
CAST AS )byte_data_definitionUDT_expression(
Chapter 2: SQL Functions and ExpressionsUDT-to-Character Conversion
72 SQL Quick Reference
Teradata Conversion
UDT-to-Character Conversion
CAST
Teradata Conversion
UDT-to-DATE Conversion
CAST
1101B345
UDT_expression
data_attribute ,
byte_data_type )(
, data_attribute
1101A346
CAST AS )character_data_definitionUDT_expression(
1101B347
UDT_expression
data_attribute ,
character_data_type )(
, data_attribute
1101B348
CAST AS DATE )UDT_expression(
date_data_attribute
Chapter 2: SQL Functions and ExpressionsUDT-to-INTERVAL Conversion
SQL Quick Reference 73
Teradata Conversion
UDT-to-INTERVAL Conversion
CAST
Teradata Conversion
UDT-to-Numeric Conversion
CAST
1101B349
UDT_expression
data_attribute ,
DATE )(
, data_attribute
1101A350
CAST AS )interval_data_definitionUDT_expression(
1101B351
UDT_expression
data_attribute ,
interval_data_type )(
, data_attribute
1101A352
CAST AS )numeric_data_definitionUDT_expression(
Chapter 2: SQL Functions and ExpressionsUDT-to-TIME Conversion
74 SQL Quick Reference
Teradata Conversion
UDT-to-TIME Conversion
CAST
Teradata Conversion
1101B353
UDT_expression
data_attribute ,
numeric_data_type )(
, data_attribute
1101A354
CAST AS TIME
(fractional_seconds_precision)
UDT_expression(
)
A
WITH TIME ZONE time_data_attribute
A
1101B342
UDT_expression
(fractional_seconds_precision)
TIME
)
( A
A
data_attribute ,
WITH TIME ZONE
, data_attribute
Chapter 2: SQL Functions and ExpressionsUDT-to-TIMESTAMP Conversion
SQL Quick Reference 75
UDT-to-TIMESTAMP Conversion
CAST
Teradata Conversion
UDT-to-UDT Conversion
CAST
DateTime and Interval Functions and Expressions
Arithmetic Operators and Result Types
The following arithmetic operations are permitted for DateTime and Interval data types:
1101A355
CAST AS TIMESTAMP
(fractional_seconds_precision)
UDT_expression(
)
A
WITH TIME ZONE timestamp_data_attribute
A
1101B343
UDT_expression
(fractional_seconds_precision)
TIMESTAMP
)
( A
A
data_attribute ,
WITH TIME ZONE
, data_attribute
1101A356
CAST AS )UDT_data_definitionUDT_expression(
Chapter 2: SQL Functions and ExpressionsDateTime and Interval Functions and Expressions
76 SQL Quick Reference
ANSI DateTime Expressions
date_time_expression Syntax
date_time_term Syntax
ANSI Interval Expressions
interval_expression Syntax
First Value Type Operator Second Value Type Result Type
DateTime - DateTime Interval
DateTime + Interval DateTime
DateTime - Interval DateTime
Interval + DateTime DateTime
Interval + Interval Interval
Interval - Interval Interval
Interval * Number Interval
Interval / Number Interval
Number * Interval Interval
FF07D266
interval_expression
date_time_term
date_time_term+
date_time_expression interval_term±
FF07D265
AT
date_time_primary
TIME ZONE
LOCAL
interval_expression
1101A010
interval_expression
interval_term
interval_term±
date_time_expression date_time_term )( start
TO end
Chapter 2: SQL Functions and ExpressionsDateTime and Interval Functions and Expressions
SQL Quick Reference 77
interval_term Syntax
numeric_term Syntax
numeric_factor Syntax
ADD_MONTHS (DATE Syntax)
ADD_MONTHS (TIMESTAMP Syntax)
EXTRACT
FF07D268
interval_term
interval_primary
numeric_factor*
numeric_term interval_factor*
±
/
FF07D270
numeric_term
numeric_factor
numeric_factor*
/
FF07D269
numeric_primary±
FF07D202
ADD_MONTHS ( )date_expression, integer_expression
FF07D208
ADD_MONTHS ( )timestamp_expression, integer_expression
FF07D144
EXTRACT
MONTH
( )YEAR valueFROM
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
Chapter 2: SQL Functions and ExpressionsHash-Related Functions
78 SQL Quick Reference
Hash-Related Functions
HASHAMP
HASHBAKAMP
HASHBUCKET
HASHROW
HH01A027
HASHAMP (
expression
)
HH01A028
HASHBAKAMP (
expression
)
HH01A026
HASHBUCKET (
expression
)
1101B026
HASHROW,
(
expression
)
Chapter 2: SQL Functions and ExpressionsLogical Predicates
SQL Quick Reference 79
Logical Predicates
ANY/ALL/SOME Quantifiers
BETWEEN/NOT BETWEEN
CONTAINS
EXISTS/NOT EXISTS
1101B090
comparison_operator constantexpression ( )ALL
IN
OR
NOT
ANYSOME
,
,
Constantssyntax
Subquerysyntax
comparison_operator subqueryexpression ( )ALL
IN
NOT
ANYSOME
( ) comparison_operator subqueryexpression ( )ALL
IN
NOT
ANYSOME
HH01A038
NOT
expr1 BETWEEN expr2 AND expr3
period_expression period_expression
datetime_expression
CONTAINS
NOT
1101A582
datetime_expression period_expression CONTAINS
NOT
HH01A047
subquery
NOT
EXISTS
Chapter 2: SQL Functions and ExpressionsLogical Predicates
80 SQL Quick Reference
IN/NOT IN (Syntax 1)
IN/NOT IN (Syntax 2)
IS NULL/IS NOT NULL
IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED
1101A309
NOT
expression_1 IN
OR
,expression_2
( constant
signed_constant_1 TO signed_constant_2
)
datetime_literal
HH01B002NOT
IN subquery
,
( )( ))expression )
NOT
expression IN subquery( ))
HH01A042
NOT
expression IS NULL
END(period_value_expression) IS UNTIL_CHANGED
NOT1101A639
Chapter 2: SQL Functions and ExpressionsLogical Predicates
SQL Quick Reference 81
LIKE
MEETS
OVERLAPS
FF07D196
NOT
LIKE
ESCAPE escape_character
( pattern_expression )
,
NOT
expression LIKE
ESCAPE escape_character
( subquery )
,
NOT
expression LIKE
ESCAPE escape_character
( subquery )( )
NOT
expression LIKE pattern_expression
ESCAPE escape_character
,
expression( )
ALL
ANY
SOME
ALL
ANY
SOME
ALL
ANY
SOME
period_expression period_expression
datetime_expression
MEETS
NOT
1101A581
datetime_expression period_expression MEETS
NOT
1101A612
datetime_expression, datetime_expression datetime_expression, datetime_expression( OVERLAPS
datetime_expression, interval_expression
) ( )
period_expression period_expression
row_subquery
datetime_expression, interval_expression
row_subquery
Chapter 2: SQL Functions and ExpressionsLogical Predicates
82 SQL Quick Reference
PRECEDES
SUCCEEDS
period_expression period_expression
datetime_expression
PRECEDES
NOT
1101A580
datetime_expression period_expression PRECEDES
NOT
period_expression period_expression
datetime_expression
SUCCEEDS
NOT
1101A583
datetime_expression period_expression SUCCEEDS
NOT
Chapter 2: SQL Functions and ExpressionsOrdered Analytical Functions
SQL Quick Reference 83
Ordered Analytical Functions
Window Aggregate Functions (AVG, CORR, COUNT, COVAR_POP, COVAR_SAMP, MAX, MIN, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP)
1101A465
window
AAVG
*
value_expression( )
COUNT value_expression( )
COVAR_POP value_expression_1, value_expression_2( )
COVAR_SAMP value_expression_1, value_expression_2( )
CORR value_expression_1, value_expression_2( )
MAX value_expression( )
MIN value_expression( )
REGR_AVGX dependent_variable_expression, independent_variable_expression( )
REGR_AVGY dependent_variable_expression, independent_variable_expression( )
REGR_COUNT dependent_variable_expression, independent_variable_expression( )
REGR_INTERCEPT dependent_variable_expression, independent_variable_expression( )
REGR_R2 dependent_variable_expression, independent_variable_expression( )
REGR_SLOPE dependent_variable_expression, independent_variable_expression( )
REGR_SXX dependent_variable_expression, independent_variable_expression( )
REGR_SXY dependent_variable_expression, independent_variable_expression( )
REGR_SYY dependent_variable_expression, independent_variable_expression( )
STDDEV_POP value_expression( )
STDDEV_SAMP value_expression( )
SUM value_expression( )
VAR_POP value_expression( )
VAR_SAMP value_expression( )
A
Chapter 2: SQL Functions and ExpressionsOrdered Analytical Functions
84 SQL Quick Reference
CSUM
window
OVER (
ROWS UNBOUNDED PRECEDING
CURRENT ROW
UNBOUNDED FOLLOWINGROWS BETWEEN
CURRENT ROW
B
A
column_referencePARTITION BY
,
value PRECEDING
UNBOUNDED PRECEDING AND
value PRECEDING
value FOLLOWING
UNBOUNDED FOLLOWING
CURRENT ROW
value PRECEDING
value FOLLOWING
value FOLLOWING
value PRECEDING AND
value FOLLOWING AND
UNBOUNDED FOLLOWINGCURRENT ROW AND
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING
value_expressionORDER BY
,
ASC
DESC
BA
1101B464
RESET WHEN condition
)
1101A398
CSUM,
ASC
DESC
( (value_expression, sort_expression
Chapter 2: SQL Functions and ExpressionsOrdered Analytical Functions
SQL Quick Reference 85
MAVG
MDIFF
MLINREG
MSUM
PERCENT_RANK
1101A399
MAVG,
ASC
DESC
( (value_expression, width, sort_expression
1101A400
MDIFF,
ASC
DESC
( (value_expression, width, sort_expression
1101A401
MLINREGASC
DESC
( (value_expression, width, sort_expression
1101A402
MSUM,
ASC
DESC
( (value_expression, width, sort_expression
1101A567
PERCENT_RANK()
PARTITION BY column_reference
,OVER (
ASC
value_expressionORDER BY )A
A
DESC
,
RESET WHEN condition
Chapter 2: SQL Functions and ExpressionsOrdered Analytical Functions
86 SQL Quick Reference
QUANTILE
RANK (Teradata-Specific Function)
RANK (SQL:2008 Window Function)
ROW_NUMBER
1101A403
QUANTILE,
ASC
DESC
( (quantile_constant, sort_expression
1101A404
RANK,
ASC
DESC
( (sort_expression
1101A566
RANK()
PARTITION BY column_reference
,OVER (
ASC
value_expressionORDER BY )A
A
DESC
,
RESET WHEN condition
1101C108
ROW_NUMBER()
PARTITION BY column_reference
,OVER (
ASC
value_expressionORDER BY )A
A
DESC
,
RESET WHEN condition
Chapter 2: SQL Functions and ExpressionsPeriod Functions and Operators
SQL Quick Reference 87
Period Functions and Operators
BEGIN
END
LAST
INTERVAL
PRIOR
NEXT
BEGIN(period_value_expression)
1101A595
END(period_value_expression)
1101A596
LAST(period_value_expression)
1101A597
INTERVAL (period_expression) interval_qualifier
1101A577
PRIOR (datetime_expression)
1101A578
NEXT (datetime_expression)
1101A579
Chapter 2: SQL Functions and ExpressionsPeriod Functions and Operators
88 SQL Quick Reference
P_INTERSECT
LDIFF
RDIFF
P_NORMALIZE
Period Value Constructor
Arithmetic Operators
period_expression period_expressionP_INTERSECT
1101A584
period_expression period_expressionLDIFF
1101A592
period_expression period_expressionRDIFF
1101A593
period_expression period_expressionP_NORMALIZE
1101A594
PERIOD (datetime_expression)
1101A585
PERIOD (datetime_expression, datetime_expression)
PERIOD (datetime_expression, UNTIL_CHANGED)
period_expression interval_expression + _
1101A586
interval_expression period_expression + _
Chapter 2: SQL Functions and ExpressionsSet Operators
SQL Quick Reference 89
Set Operators
Syntax for query_term
Syntax for query_factor
Syntax for query_expression
INTERSECT Operator
MINUS/EXCEPT Operator
UNION Operator
FF07D178
(query_expression )
SELECT statement
HH01A061
query_term
INTERSECT query_termquery_factor
ALL
FF07D179
query_expression
query_factor
UNION
MINUS
EXCEPT
ALL
query_factor
(query_expression ) ORDER BY expression
,
ASC
DESC
FF07D176
ALLquery_expression_1 INTERSECT query_expression_2
FF07D177
ALLquery_expression_1 MINUS query_expression_2
EXCEPT
FF07D175
ALLquery_expression_1 UNION query_expression_2
Chapter 2: SQL Functions and ExpressionsString Operator and Functions
90 SQL Quick Reference
String Operator and Functions
CHAR2HEXINT
Concatenation Operator
INDEX
LOWER
POSITION
SOUNDEX
STRING_CS
1101E173
CHAR2HEXINT ( character_string_expression )
FF07D195
string_expression_1 string_expression_2
string_expression_n
FF07D253
INDEX string_expression_1( ),string_expression_2
FF07D091
LOWER (character_string_expression)
FF07D090
POSITION (string_expression_1 string_expression_2)IN
KO01A060
SOUNDEX string_expression( )
1101A515
STRING_CS string_expression( )
Chapter 2: SQL Functions and ExpressionsString Operator and Functions
SQL Quick Reference 91
SUBSTRING/SUBSTR (ANSI Syntax)
SUBSTRING/SUBSTR (Teradata Syntax)
TRANSLATE
TRANSLATE_CHK
TRIM
UPPER
SUBSTRING
FOR n2
(string_expression )FROM n1
FF07D256
FF07D257
SUBSTR (string_expression,n1 )
,n2
1101E198
TRANSLATE character_string_expression
_encoding
( USING source_repertoire_name
_TO _target_repertoire_nameA
A
_suffix WITH ERROR
)
1101E199
TRANSLATE_CHK character_string_expression
_encoding
( USING source_repertoire_name
_TO _target_repertoire_nameA
A
_suffix
)
1101F200
TRIM string_expression( )
FROM
trim_expression
character_setBOTH
TRAILING
LEADING
FF07D258
UPPER character_string_expression( )
Chapter 2: SQL Functions and ExpressionsUDF Expressions
92 SQL Quick Reference
VARGRAPHIC
UDF Expressions
Scalar UDF Expression
Aggregate UDF Expression
1101E197
VARGRAPHIC character_string_expression( )
1101A640
udf_name,
(
argument
(
1101A640
udf_name,
(
argument
(
Chapter 2: SQL Functions and ExpressionsUDT Expressions and Methods
SQL Quick Reference 93
UDT Expressions and Methods
UDT Expression
NEW
NEW VARIANT_TYPE
CAST
constructor_name
udf_name
NEW
SYSUDTLIB.
1101B363
A
table_name.
expression udt_nameAS
column_name
,(
database_name.
argument
A
(
,(
argument
(
( (
method_name,
(
argument
(.
.
constructor_nameNEW
SYSUDTLIB.1101B364
,(
argument
(
NEW VARIANT_TYPE expression AS alias_name
AS alias_name
,
table_name.column_name
( )
1101A576
Chapter 2: SQL Functions and ExpressionsUDT Expressions and Methods
94 SQL Quick Reference
Method Invocation
1101B365
A
A method_name,
(
argument
(
CAST
constructor_name
udf_name
NEW
.
.
SYSUDTLIB.
table_name.
expression udt_nameAS
column_name
,(
database_name.
argument
(
,(
argument
(
( (
SQL Quick Reference 95
CHAPTER 3 SQL Data Definition Language
ALTER FUNCTION
ALTER METHOD
1101D097
SPECIFIC FUNCTION
EXECUTE
SYSUDTLIB.
PROTECTED
NOT
ALTER specific_function_name
FUNCTION function_name
;
A
A
COMPILE
ONLY
,
data_typeUDT_name
( )database_name.
database_name.
1101B369
SPECIFIC METHOD FOR UDT_name
EXECUTE PROTECTED
NOT
ALTER
,
specific_method_name
METHOD
INSTANCECONSTRUCTOR
data_typeUDT_name
( )
method_name
;
A
A
COMPILE
ONLY
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
Chapter 3: SQL Data Definition LanguageALTER PROCEDURE (External Form)
96 SQL Quick Reference
ALTER PROCEDURE (External Form)
ALTER PROCEDURE (SQL Form)
ALTER REPLICATION GROUP
ALTER PROCEDURE procedure_name
ONLY
LANGUAGE COMPILEC
CPP
JAVA
database_name.
A
A
1101B284
;
NOT
EXECUTE PROTECTED
1101S001
ALTER PROCEDURE procedure_name A
database_name. LANGUAGE SQL
COMPILE
A
WITH
,
NOSPL
NOWARNING
;2
1101B239
ALTER REPLICATION GROUP replication_group_name
database_name.
table_nameADD
A
A
;,
database_name.
table_nameDROP
database_name.
table_nameADD
,
,
database_name.
table_nameDROP
,
,
Chapter 3: SQL Data Definition LanguageALTER TABLE
SQL Quick Reference 97
ALTER TABLE
Basic Table Parameters Modification Syntax
ALTER TABLEdatabase_name.
table_name
NO PROTECTION
FALLBACKA
WITH JOURNAL TABLE =
database_name.
table_name
NO
DUAL
JOURNAL
BEFORE
AFTER JOURNAL
NO
DUAL
LOCAL
NOT LOCAL
FREESPACE = integer
PERCENTDATABLOCKSIZE = data_block_size
BYTES
KILOBYTES
KBYTES
IMMEDIATE
DATABLOCKSIZE
MAXIMUM
MINIMUM
Alter TableOptions
,
DEFAULT FREESPACE
DEFAULT
,
LOG
NO
ON COMMIT
PRESERVE
DELETE ROWS
CHECKSUM = integrity_checking_level
IMMEDIATE
1101A389
Chapter 3: SQL Data Definition LanguageALTER TABLE
98 SQL Quick Reference
1101H034
ADD
CONSTRAINT name
UNIQUE
PRIMARY KEY
column_name
,DROP CONSTRAINT name
MODIFY
CONSTRAINT name
column_name ( boolean_condition )CHECK
DROP CHECK
column_name
CONSTRAINT name
ADD ( boolean_condition )CHECK
column_name
CONSTRAINT name
DROP INCONSISTENT REFERENCES
ADD
DROP
FOREIGN KEY
CONSTRAINT name
column_name
REFERENCES table_name,
column_name
DROP column_name
ADD column_name NULL
ADD column_name
data type attributes
CONSTRAINTname
ColumnStorage
Attributes
ColumnConstraintAttributes
ColumnChanges
ConstraintChanges
ReferenceDefinition
TableColumn
Definition
A
a
b
b
a
UniqueDefinition
,
data type attributes
,
data type
RENAME old_column_name TO new_column_nameAS
( )
( )
( )
WITH CHECK OPTIONNO
COMPRESS
NULLNOT
constant
constant
,
( )
255
NULL
;
NO
UNIQUE
PRIMARY KEY
CHECK ( boolean_condition )
REFERENCES table_name
( column_name )
,WITH CHECK OPTION
NO
Chapter 3: SQL Data Definition LanguageALTER TABLE
SQL Quick Reference 99
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageALTER TABLE
100 SQL Quick Reference
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
Chapter 3: SQL Data Definition LanguageALTER TABLE
SQL Quick Reference 101
Primary Index Modification Syntax
ALTER TABLE
NOT PARTITIONED
DROP RANGE#Ln
WHERE
BETWEEN start_expressionAND end_expression
UNKNOWN
,
conditional_expression
PARTITION BY,
partitioning_expressionWITH DELETE
INSERT save_tableINTO
MODIFY
PRIMARY INDEX
NOT NAMED
database_name.
index_namecolumn_name
;
,
)(
UNIQUENOT
table_name
B
1101F112
BA
A
C
15
15
EACH range_size NO RANGEOR UNKNOWN
UNKNOWN,
,DC
NO RANGEOR UNKNOWN
UNKNOWN,
( (partitioning_expression
ADD RANGE#Ln
BETWEEN start_expressionAND end_expression
UNKNOWN
ED
EACH range_size NO RANGEOR UNKNOWN
UNKNOWN,
,FE
NO RANGEOR UNKNOWN
UNKNOWN,
F
WITH DELETE
INSERT save_tableINTO
ADD RANGE#Ln
BETWEEN start_expressionAND end_expression
UNKNOWN
GEACH range_size
NO RANGEOR UNKNOWN
UNKNOWN,
,
,
G
UNKNOWN,
NO RANGEOR UNKNOWN
UNKNOWN,
Chapter 3: SQL Data Definition LanguageALTER TRIGGER
102 SQL Quick Reference
Partitioned Primary Index Revalidation Syntax
Set Down/Reset Down Syntax
ALTER TRIGGER
ALTER TABLE REVALIDATE PRIMARY INDEXtable_name
join_index_namedatabase_name.
WITH DELETE
INSERT save_table
INTO
A
1101A432
;
A
1101B543
;
ALTER TABLE SETtable_name
RESET
DOWN
database_name.
ALTER TRIGGER
;database_name. table_name
trigger_name ENABLED
DISABLED
TIMESTAMP
1101A201
trigger_name
Chapter 3: SQL Data Definition LanguageALTER TYPE
SQL Quick Reference 103
ALTER TYPE
ALTER TYPE ADD
DROP
ATTRIBUTEUDT_name attribute_name
1101C368
data_type
UDT_name ;
,
ADD METHOD
INSTANCE
CONSTRUCTOR
method_name
COMPILE
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SPECIFIC METHOD
FOR UDT_name
specific_method_name
,
data_type
UDT_name
( (
,
data_type
UDT_name
( (
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
RETURNS data_type
UDT_name
LANGUAGE C
CPP
PARAMETER STYLE
DETERMINISTIC
NOT
NO SQL
SQL
TD_GENERAL
SYSUDTLIB.
DROP
a
c
c d
e
e
d
b
ba
Chapter 3: SQL Data Definition LanguageBEGIN LOGGING
104 SQL Quick Reference
BEGIN LOGGING
BEGIN LOGGING
FIRST
LAST
FIRST AND LAST
EACH
DENIALS WITH TEXT
ON A
ALL
BY
A B,
operation
GRANTdatabase_nameuser_name
,
B
ON AUTHORIZATION authorization_name
object_name
VIEW
DATABASE database_name
USER user_name
TABLETABLE
MACRO
1101W044
database_name.user_name.
PROCEDURE
FUNCTION
TYPE
;
Chapter 3: SQL Data Definition LanguageBEGIN QUERY LOGGING
SQL Quick Reference 105
BEGIN QUERY LOGGING
BEGIN QUERY LOGGING
1101D109
logging_option
;
,A
A
WITH
NONE
limit_optionLIMIT
limit_optionAND
ON ALL
'account_name'ACCOUNT =
'application_name'APPLNAME =
'account_name'
,
( )
user_name
user_name
,
'application_name'
,
( )
Chapter 3: SQL Data Definition LanguageCOLLECT STATISTICS (Optimizer Form)
106 SQL Quick Reference
COLLECT STATISTICS (Optimizer Form)
ON
join_index_name
USING SAMPLE
A
B table_name_1TEMPORARYVOLATILE
COLLECT STATISTICS
COLLECT STAT
hash_index_name
A
UNIQUE
COLUMN
index_name
B
ORDER BY (
( )
)
column_name_1
column_name
,
column_name( )
,
INDEX
ALL
,
column_name_2
VALUES
HASH
PARTITION
PARTITION
database_name.user_name.
C
E
E
D
C
FROM
64
64
database_name.user_name.
join_index_name
table_name_2TEMPORARYVOLATILE
database_name.user_name.
database_name.user_name.
1101C322
;
D
COLUMN column_name_3
column_name( )
,
PARTITION
PARTITION
64
hash_index_name
Chapter 3: SQL Data Definition LanguageCOLLECT STATISTICS (Alternate Optimizer Form)
SQL Quick Reference 107
COLLECT STATISTICS (Alternate Optimizer Form)
COLLECT STATISTICS (Recollect Statistics)
COLLECT STATISTICSONUSING SAMPLE
column_name_1 )(
( )
,
A
B C
COLLECT STAT
COLUMN
PARTITION
COLUMN
column_name_1
,
INDEX
INDEX
column_name_1
index_name
PARTITION
64
64
C D
a
aFROM
TEMPORARY
table_name
VOLATILE
table_name
join_index_name
BATEMPORARYVOLATILE
hash_index_name
database_name.user_name.
database_name.user_name.
( )column_name_2
,
1101C321
;D
COLUMN
database_name.user_name.
database_name.user_name.
COLLECT STATISTICS ON
table_name
1101A548
;database_name.
user_name.
Chapter 3: SQL Data Definition LanguageCOMMENT (Comment Placing Form)
108 SQL Quick Reference
COMMENT (Comment Placing Form)
CREATE AUTHORIZATION/REPLACE AUTHORIZATION
1101E022
;
COMMENTobject_kind
object_namedatabase_name.
user_name.'comment'ON
AS
IS
1101C227
AUTHORIZATIONCREATE
REPLACE
DOMAIN
USER PASSWORD'user_name' 'password'
'domain_name'
database_name.user_name.
AS
;
authorization_name DEFINER
DEFAULT
INVOKER
A
A
Chapter 3: SQL Data Definition LanguageCREATE CAST/ REPLACE CAST
SQL Quick Reference 109
CREATE CAST/REPLACE CAST
CREATE CAST
SYSUDTLIB.source_UDT_name
ASsource_predefined_data_type(
REPLACE
target_predefined_data_type ) WITH
SYSUDTLIB.
target_UDT_name
A
1101A358
B
A
C
;
C
SPECIFIC METHOD
SPECIFIC FUNCTION specific_function_name
FUNCTION
AS ASSIGNMENT
function_name
FOR UDT_namespecific_method_name
INSTANCE
METHOD,
(method_name
data_type
UDT_name
B
(
,(
data_type
UDT_name
(
Chapter 3: SQL Data Definition LanguageCREATE DATABASE
110 SQL Quick Reference
CREATE DATABASE
CREATE ERROR TABLE
1101E030
,
A
;
CREATE DATABASE name AS
FROM database_name
A
SPOOL = n
BYTES
ACCOUNT = 'account_ID'
FALLBACK
PROTECTIONNO
NO
DUAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE =
database_name.
table_name
NO
DUAL
JOURNAL
BEFORE
LOCAL
NOT LOCAL
PERMANENT
PERM
= n
BYTES
CD
TEMPORARY = n
bytes
1101B436
CREATE ERROR TABLE FOR data_table_name
error_table_name
database_name.user_name.
;database_name.user_name.
Chapter 3: SQL Data Definition LanguageCREATE FUNCTION/ REPLACE FUNCTION
SQL Quick Reference 111
CREATE FUNCTION/REPLACE FUNCTION
1101C323
CREATE
RETURNS
,
data type
FUNCTION function_name
REPLACE
( )
database_name.user_name.
parameter_name
A
B
B
C
SPECIFIC specific_function_name
database_name.
SQL_data_accesslanguage_clauseC D
A
CLASS AGGREGATE
AG ( )interim_size
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
PARAMETER STYLE SQL
DETERMINISTIC
NOT
data type
CAST FROM data type
language_clauseSQL_data_access
SPECIFIC specific_function_name
user_name.database_name.
SQL_data_accesslanguage_clause
CLASS AGGREGATE
AG ( )interim_size
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
PARAMETER STYLE SQLTD_GENERAL
JAVA
DETERMINISTIC
NOT
language_clauseSQL_data_access
TD_GENERALJAVA
Chapter 3: SQL Data Definition LanguageCREATE FUNCTION/ REPLACE FUNCTION
112 SQL Quick Reference
1101A643
E
FD
'
' '
delimiter 'function_entry_name
EXTERNAL
NAME external_function_name
L delimiter library_name
P delimiter package_name
ISC
delimiter name_on_server delimiter include_name
O delimiter
delimiter
name_on_server delimiter object_name
S delimiter
JAR_ID:java_class_name.method_name
( )primitive returns[ ][ ]
name_on_server delimiter source_name
;
F
F
PARAMETER STYLE SQLTD_GENERAL
JAVA
EXTERNAL SECURITY DEFINER
INVOKER
authorization_name
object
,
[ ]
primitive [ ][ ]
object[ ]
ED
USING GLOP SET GLOP_set_name
Chapter 3: SQL Data Definition LanguageCREATE FUNCTION/ REPLACE FUNCTION
SQL Quick Reference 113
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE FUNCTION/ REPLACE FUNCTION
114 SQL Quick Reference
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
Chapter 3: SQL Data Definition LanguageCREATE FUNCTION (Table Form)
SQL Quick Reference 115
CREATE FUNCTION (Table Form)
1101E228
CREATE
RETURNS TABLE
VARYING COLUMNS maximum_output_columns
,
)column_name data_type(
,
data type
FUNCTION function_name
REPLACE
( )
user_name.database_name.
parameter_name
A
B
B
C
SPECIFIC specific_function_name
user_name.database_name.
SQL_data_accesslanguage_clauseC D
A
CALLED ON NULL INPUT
PARAMETER STYLE SQL
JAVA
DETERMINISTICNOT
language_clauseSQL_data_access
SPECIFIC specific_function_name
user_name.database_name.
CALLED ON NULL INPUT
PARAMETER STYLE SQL
JAVA
DETERMINISTIC
NOT
SQL_data_accesslanguage_clause
language_clauseSQL_data_access
( (
Chapter 3: SQL Data Definition LanguageCREATE FUNCTION (Table Form)
116 SQL Quick Reference
1101A644
;F
PARAMETER STYLE SQL
JAVA
EXTERNAL SECURITY DEFINER
INVOKER
authorization_name
E
FD
'
' '
delimiter 'function_entry_name
EXTERNAL
NAME external_function_name
L delimiter library_name
P delimiter package_name
ISC
delimiter name_on_server delimiter include_name
O delimiter
delimiter
name_on_server delimiter object_name
S delimiter
JAR_ID:java_class_name.method_name
( )primitive [ ][ ]
name_on_server delimiter source_name
F
object
,
[ ]
ED
MEMBER
OF GLOP SET
GLOP_set_name
Chapter 3: SQL Data Definition LanguageCREATE FUNCTION (Table Form)
SQL Quick Reference 117
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE GLOBAL TEMPORARY TRACE TABLE
118 SQL Quick Reference
CREATE GLOBAL TEMPORARY TRACE TABLE
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101B534
A
A,
ON COMMIT ;ROWSDELETE
PRESERVE
(
CREATE GLOBAL TEMPORARY TRACE TABLE
( ,
table_namedatabase_name.user_name.
column_name data type data type attributes
Bproc_ID BYTE(2) sequence INTEGER
B
Chapter 3: SQL Data Definition LanguageCREATE GLOBAL TEMPORARY TRACE TABLE
SQL Quick Reference 119
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE GLOBAL TEMPORARY TRACE TABLE
120 SQL Quick Reference
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101B115
NOT NULL
NOT
UPPERCASE
CASESPECIFIC
FORMAT quotestring
TITLE quotestring
NAMED name
Data Type Attributes
UC
CS
CHARACTER SET server_character_set
Chapter 3: SQL Data Definition LanguageCREATE GLOP SET
SQL Quick Reference 121
CREATE GLOP SET
CREATE HASH INDEX
1101A637
CREATE GLOP SET
database_name.
user_name.
GLOP_set_name
;
1101B431
CREATE HASH INDEX A
,
hash_index_name
ONB column_name_1
database_name.user_name.
,
BY
table_name
C
;ORDER BY VALUES
column_name_3 )(
HASH
,
column_name_3 )(
column_name_2 )(
column_name_3 )(
C( )
B
FALLBACK
CHECKSUM = integrity_checking_level
NO
,,
PROTECTION
A
Chapter 3: SQL Data Definition LanguageCREATE INDEX
122 SQL Quick Reference
CREATE INDEX
1101H004
ON
;
join_index_name
CREATE
UNIQUE index_nameA
BA
B table_name
INDEX
ALL
,
TEMPORARY
ORDER BY
( order_column_name )VALUES
HASH
64,
( )index_column_name
database_name.user_name.
database_name.user_name.
Chapter 3: SQL Data Definition LanguageCREATE JOIN INDEX
SQL Quick Reference 123
CREATE JOIN INDEX
CREATE JOIN INDEX join_index_name
1101H294
A
BA column_1_name
,
,
EXTRACT ( YEAR FROM
MONTH
date_expressionAS
expression_alias
column_name_alias
column_name_alias
column_name_alias
column_name_aliascolumn_name
SUM
COUNT
EXTRACT
( numeric_expression )AS
expression_alias
( YEAR )
MONTH
FROM date_expression
,
)
( column_expression )
FALLBACK
CHECKSUM = integrity_checking_level
NO
database_name.user_name. ,
,
PROTECTION
table_name.
table_name.
a
a
column_1_name
table_name.
, 64
)(
ROWID
column_2_name
table_name.
, 64
)(
BAS SELECTA
AS
ROWIDAS
ROWIDAS
ROWID ASdatabase_name.user_name.
database_name.user_name.
database_name.user_name.
database_name.user_name.
1101A646
;
CB table_nameFROM
,
correlation_name
AS
joined_table
DC
GROUP BY
D indexes
WHERE search_condition
,
column_name
column_position
ORDER BY
,
column_name
column_position
database_name.user_name.
Chapter 3: SQL Data Definition LanguageCREATE JOIN INDEX
124 SQL Quick Reference
1101E050
(joined_table)
joined_table
INNER
joined_table
joined_tableJOIN ON search_condition
LEFT
RIGHT OUTER
table_name
correlation_name
AS
1101I051
indexes
64
64
64
,
,
PRIMARY INDEX
PARTITION BY
,
( (
partitioning_expression
partitioning_expression
primary_index_columnindex_name
,
INDEX ( )
ORDER BY ( )VALUES
HASH
index_column_nameindex_name ALL
order_column_name
,
INDEX ( )
( )
UNIQUE index_column_nameindex_name
15
Chapter 3: SQL Data Definition LanguageCREATE MACRO/ REPLACE MACRO
SQL Quick Reference 125
CREATE MACRO/REPLACE MACRO
1101G172
AS
CREATE MACRO macro_namedatabase_name.
parameter_name type declaration
,
;USING modifier
( SQL_statement ; )
)(
type attribute
A
2048
A
LOCKING modifier
CM
REPLACE MACRO
Chapter 3: SQL Data Definition LanguageCREATE METHOD
126 SQL Quick Reference
CREATE METHOD
1101C371
CREATE METHOD method_name
RETURNS FOR UDT_name
,
data type
data type
( )
parameter_name
authorization_name
A
B
B
C
A
EXTERNAL SECURITY DEFINER
INVOKER
;
data type
CAST FROM
E
AS LOCATOR
SYSUDTLIB.INSTANCECONSTRUCTOR
D
FD
' delimiter 'method_entry_name
EXTERNAL
NAME external_function_name
L delimiter library_name
ISC
delimiter name_on_server delimiter include_name
O delimiter
delimiter
name_on_server delimiter object_name
S delimiter name_on_server delimiter source_name
E
DC
USING GLOP SET GLOP_set_name
Chapter 3: SQL Data Definition LanguageCREATE METHOD
SQL Quick Reference 127
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE ORDERING/ REPLACE ORDERING
128 SQL Quick Reference
CREATE ORDERING/REPLACE ORDERING
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
CREATE ORDERING FOR UDT_name ORDER FULL BY
SYSUDTLIB.
MAP WITH
REPLACE
1101B359
A
;
SPECIFIC METHOD
SPECIFIC FUNCTION specific_function_name
FUNCTION function_name
FOR UDT_namespecific_method_name
INSTANCE
METHOD,
(method_name
data_type
UDT_name
A
(
,(
data_type
UDT_name
(
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (External Form)/ REPLACE PROCEDURE (External Form)
SQL Quick Reference 129
CREATE PROCEDURE (External Form)/REPLACE PROCEDURE (External Form)
CREATE
,
data type
PROCEDURE
INOUTINOUT
procedure_name
REPLACE
( )
database_name.user_name.
parameter_name
A
B
SQL_data_accesslanguage_clauseB C
A
PARAMETER STYLE
DYNAMIC RESULT SETS number_of_sets
SQL
TD_GENERALJAVA
language_clauseSQL_data_access
SQL_data_accesslanguage_clausePARAMETER STYLE
language_clauseSQL_data_access
256
D
FD
' delimiter 'function_entry_name
EXTERNAL
' jar_name java_class_name.
java_data_type
java_method_name
( (
package_name.
:
NAME external_function_name
L delimiter library_name
P delimiter package_name
ISC
delimiter name_on_server delimiter include_name
O delimiter
delimiter
',
name_on_server delimiter object_name
S delimiter name_on_server delimiter source_name
E
D
A
A
E
PARAMETER STYLE SQLTD_GENERAL
JAVA
EXTERNAL SECURITY DEFINER
INVOKER
authorization_name
SQL
TD_GENERALJAVA
1101E232
;
C
SQL SECURITY privilege_optionUSING GLOP SET GLOP_set_name
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (External Form)/ REPLACE PROCEDURE (External Form)
130 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE
SQL Quick Reference 131
CREATE PROCEDURE (SQL Form)/REPLACE PROCEDURE
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
CREATE
REPLACE
PROCEDURE
DYNAMIC RESULT SETS number_of_sets
Aprocedure_name
database_name.
user_name.
1101T002
A B
B
( ), 256parameter_name data_type
INOUT
IN
OUT
; statement
SQL SECURITY privilege_option
SQL_data_access
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE
132 SQL Quick Reference
statement
1101C234
ITERATE
BEGIN REQUEST END REQUESTSQL_multistatement_request
label_name
LEAVE label_name
iteration statement
label_name : label_name
SQL_statement
condition statement
diagnostic statement
compound statement
open statement
fetch statement
assignment statement
compound statement
label_name :local_declaration
BEGIN
cursor_declaration
B
1101A383
END
; label_name
B
condition_handler statement
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE
SQL Quick Reference 133
1101C448
local_declaration
DECLARE
condition_name CONDITION
FOR sqlstate_code
variable_name,
DEFAULT
data_type
literal
;
NULL
cursor_declaration
NO SCROLL
DECLARE
WITH RETURN
ONLY
cursor_name
SCROLL
CURSOR C
D
FOR
FOR
statement_name
UPDATE
READ ONLY
cursor_specificationD E
C
TO CALLER
CLIENT
PREPARE FROM 'statement_string'statement_name
statement_string_variable
E ;
1101B384
cursor_specification
SELECT
*
INNER JOIN ON
OUTERLEFT
RIGHT
FULL
column_namealias_name
expression alias_name
A
A FROM table_name
table_name table_name condition
B,
,
AS
AS
B
WHERE clause other SELECT clauses
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE
134 SQL Quick Reference
condition_handler
DECLARE HANDLER
CONDITION
EXIT
CONTINUE D
FOR
1101A562
;D
SQLEXCEPTION
SQLWARNING
NOT FOUND
,
SQLSTATE,
VALUE
sqlstate_code
condition_name
condition_name
handler_action _statement
SQLSTATE,
VALUE
sqlstate_code
1101A652
OPEN
open statement
USING SQL_identifier
SQL_parameter
cursor_name ;,
1101A653
FETCH
fetch statement
,
local_variable_name
parameter_reference
FROM
INTO
NEXT
FIRST
cursor_name
;
A
A
1101A380
assignment statement
SET assignment_target = assignment_source
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE
SQL Quick Reference 135
1101A381
condition statement
CASE operand_1 JWHEN ;
;
;
;
;
;
operand_2 THEN statement
WHEN conditional_expression THEN statement
J
ELSE
END CASE
statement
IF conditional_expression THEN Gstatement
H
ELSE
END IF
statement
ELSEIF conditional_expression THEN
G H
statement
1101A382
iteration statement
WHILE
;
;
;
;conditional_expression DO END WHILEstatement
LOOP END LOOP
DO
statement
EFOR for_loop_variable AS
CURSOR FORcursor_name
E END FORcursor_specification statement
REPEAT UNTIL conditional_expression END REPEATstatement
1101A616
SIGNAL
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
;
;
condition_name
GET DIAGNOSTICS
EXCEPTION =condition_information_itemcondition_number
parameter_name variable_name
parameter_namevariable_name
diagnostic statement
,
,
RESIGNAL ;
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
=statement_information_item
condition_name
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE
136 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE PROCEDURE (SQL Form)/ REPLACE PROCEDURE
SQL Quick Reference 137
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
Chapter 3: SQL Data Definition LanguageCREATE PROFILE
138 SQL Quick Reference
CREATE PROFILE
CREATE PROFILE
AS
,
ACCOUNT = 'account_id',
'account_id'
NULL
DEFAULT DATABASE = database_name
SPOOL = n
BYTES
( )
;
profile_namedatabase_name.
A
1101B466
A
TEMPORARY = n
BYTES
PASSWORD = EXPIRE =
,
n )(
ATTRIBUTESMINCHAR = n
MAXCHAR = n
DIGITS = c
SPECCHAR = c
MAXLOGONATTEMPTS = n
LOCKEDUSEREXPIRE = n
REUSE = n
COST PROFILE = cost_profile_name
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
RESTRICTWORDS = cNULL
Chapter 3: SQL Data Definition LanguageCREATE RECURSIVE VIEW/ REPLACE RECURSIVE VIEW
SQL Quick Reference 139
CREATE RECURSIVE VIEW/REPLACE RECURSIVE VIEW
RECURSIVE VIEWCREATE
REPLACE
A( column_name )
,
view_namedatabase_name.user_name.
LOCKING
Seed
Statem
ent
DATABASE
database_nameuser_name
database_nameuser_name
database_nameuser_name
TABLE
table_name
VIEW
view_name
ROW
LOCK FOR
IN
MODE
ACCESS
EXCLUSIVE
SHARE
READ
WRITE
EXCL
NOWAIT
BA
SELECT
ALLDISTINCT
CSEL
FROM
UNION ALL
table_name
WHERE search_condition
E
a
a
C
E F
HAVING condition QUALIFY condition
correlation_nameAS
joined_table JOIN
joined_table ON search_condition
INNERLEFT
RIGHTFULL
OUTER
D
CROSS JOIN single_table
( subquery ) derived_table_nameAS
( column_name )
,
,
D
DerivedTables
JoinedTables
SingleTables
AS(
B
table_name.*
expression
,*
GROUP BY
,
ordinary_grouping_set
empty_grouping_set
rollup_list
cube_list
grouping_sets_specification
1101D229
F G
recursive statement
seed statement UNION ALL
database_name.user_name.
database_name.user_name.
database_name.user_name.
database_name.user_name.
database_name.user_name.
Chapter 3: SQL Data Definition LanguageCREATE REPLICATION GROUP
140 SQL Quick Reference
CREATE REPLICATION GROUP
SELECTALL
H
J
SEL
FROM table_name
WHERE search_condition
H
I
correlation_nameAS
joined_table JOIN joined_table ON search_conditionINNERLEFT
RIGHTFULL
OUTER
I
CROSS JOIN single_table
,
JoinedTables
SingleTables
G
table_name.*
expression
correlation_name
,
AS
*
Recu
rsive Statem
ent
1101A230
J
;)
CREATE REPLICATION GROUP replication_group_name A
A
;
1101B236
,
( )
database_name.user_name.
table_name
Chapter 3: SQL Data Definition LanguageCREATE REPLICATION RULESET/ REPLACE REPLICATION RULESET
SQL Quick Reference 141
CREATE REPLICATION RULESET/REPLACE REPLICATION RULESET
CREATE ROLE
1101A553
A
;
REPLICATION RULESET
, DEFAULT
CREATE FOR replication_group_name
REPLACE
rule_set_name
A
AS LIKEobject_kind string_literal
ESCAPE character_literal
ESCAPE character_literal
AND NOT LIKE string_literal
CREATE ROLE role_namedatabase_nameEXTERNAL ;
1101B288
Chapter 3: SQL Data Definition LanguageCREATE TABLE
142 SQL Quick Reference
CREATE TABLE
Create Table Syntax
ACREATE table_name
SET
MULTISET
TABLE
CT
GLOBAL TEMPORARY
VOLATILEdatabase_name.
user_name.
B
1101U020
FALLBACK
PROTECTIONNO
FREESPACE = integer
database_name.user_name.
AFTER JOURNAL
LOG
NO
NO
DUAL
LOCAL
NOT LOCAL
PERCENT
CHECKSUM = integrity_checking_level
BYTE
KILOBYTE
KBYTE
DATABLOCKSIZE
MAXIMUM
REPLICATION GROUP replication_group_name
MINIMUM
,
CreateTable
Options
JOURNAL
NO
DUAL
BEFORE
WITH JOURNAL TABLE = table_name
DATABLOCKSIZE=data_block_size
S
A
S
S
Chapter 3: SQL Data Definition LanguageCREATE TABLE
SQL Quick Reference 143
BA
data type attributes
UNIQUE
PRIMARY KEY
CHECK ( boolean condition )
REFERENCES
WITH CHECK OPTIONNO
table_name
column_name
UNIQUE
1101S024
PRIMARY KEY
( column_name )
CONSTRAINT
FOREIGN KEY
CONSTRAINT name
CHECK ( boolean_condition )
( column_name ) REFERENCES
ColumnStorageAttributes
ColumnConstraintAttributes
ColumnDefinition
UniqueDefinition
ReferencesDefinition
CheckDefinition
(
( column_name )
)
,
,
,
Tab
le L
evel
Def
initi
on
CONSTRAINT
GENERATED ALWAYS AS IDENTITY
START WITH(
INCREMENT BY
BY DEFAULT
name
CONSTRAINT name
name
datatype
COMPRESS
NULLNOT
constant
constant
,
( )
255
NULL
REFERENCES ,
( column_name )
table_nameWITH CHECK OPTION
NO
(
NO
MINVALUE
NO
MAXVALUE
NO
CYCLE
b
a
a
b
Chapter 3: SQL Data Definition LanguageCREATE TABLE
144 SQL Quick Reference
1101V021PRESERVE
ON COMMIT
C
DELETE ROWS ;
B C
,IndexDefinition
,
PRIMARY INDEX primary_index_column( )
UNIQUE index_name
INDEX
UNIQUE index_name ALL
INDEXindex_name ALL
ORDER BYVALUES
HASH
order_column_name( )
64,
( )index_column_name
,
( )index_column_name
64
PARTITION BY,
( (
partitioning_expression
partitioning_expression
a
b
b
a
NO PRIMARY INDEX
Chapter 3: SQL Data Definition LanguageCREATE TABLE
SQL Quick Reference 145
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE TABLE
146 SQL Quick Reference
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101G205
NOT
USER
DATE
TIME
NULL
UPPERCASE
CASESPECIFIC
FORMAT quotestring
TITLE quotestring
NAMED name
DEFAULT number
WITH DEFAULT
Data Type Attributes
UC
CS
CHARACTER SET server_character_set
Chapter 3: SQL Data Definition LanguageCREATE TABLE
SQL Quick Reference 147
Copy Table Syntax
A
aCREATE
1101T020
table_name
SET
MULTISET
FALLBACK
PROTECTIONNO
FREESPACE = integer
database_name.
AFTER JOURNAL
LOG
NO
NO
DUAL
LOCAL
NOT LOCAL
PERCENT
CHECKSUM = integrity_checking_level
BYTE
KILOBYTE
KBYTE
DATABLOCKSIZE
MAXIMUM
MINIMUM
TABLE
,
CreateTable
Options
CT
GLOBAL TEMPORARY
VOLATILE user_name.
JOURNAL
NO
DUAL
BEFORE
WITH JOURNAL TABLE = table_name
database_name.
DATABLOCKSIZE=data_block_size
S
S
S
a
Chapter 3: SQL Data Definition LanguageCREATE TABLE
148 SQL Quick Reference
1101A641PRESERVE
ON COMMIT
D
DELETE ROWS ;
C
64
64
64
D
,Index
Definition
,
PRIMARY INDEX
NO PRIMARY INDEX
primary_index_column
UNIQUE index_name
,
INDEX ( )index_column_nameindex_name ALL
,
INDEX ( )
( )
UNIQUE index_column_nameindex_name
BA
data type attributes
UNIQUE
PRIMARY KEY
PRIMARY KEY
CHECK ( boolean condition )
column_name
UNIQUE
AND STATISTICS
STATS
STAT
NO
( column_name )
CONSTRAINT name
CHECK ( boolean_condition )
ColumnStorage
Attributes
ColumnConstraintAttribute
ColumnDefinition
UniqueDefinition
CheckDefinition
( )
,
,
Tab
le L
evel
Def
initi
on
B CAS source_table_namedatabase_name.user_name.
query_expression( )
CONSTRAINT name
CONSTRAINT name
DATAWITH
NO
COMPRESS
NULLNOT
constant
constant
,
( )
255
NULL
NULL
data type
PARTITION BY,
partitioning_expression
ORDER BY ( )VALUES
HASH
order_column_name
( (partitioning_expression
a
a
b
b
Chapter 3: SQL Data Definition LanguageCREATE TABLE
SQL Quick Reference 149
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE TABLE
150 SQL Quick Reference
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101G205
NOT
USER
DATE
TIME
NULL
UPPERCASE
CASESPECIFIC
FORMAT quotestring
TITLE quotestring
NAMED name
DEFAULT number
WITH DEFAULT
Data Type Attributes
UC
CS
CHARACTER SET server_character_set
Chapter 3: SQL Data Definition LanguageCREATE TABLE (Queue Table Form)
SQL Quick Reference 151
CREATE TABLE (Queue Table Form)
ACREATE table_name
SET
MULTISET
TABLE ,
CT
database_name.user_name.
B
1101B221
FALLBACK
PROTECTIONNO
FREESPACE = integer
PERCENT
CHECKSUM = integrity_checking_level
BYTE
KILOBYTE
KBYTE
DATABLOCKSIZE
MAXIMUM
REPLICATION GROUP replication_group_name
MINIMUM
QUEUE
,
CreateTable
Options
NO JOURNAL
DATABLOCKSIZE= integer
S
A
S
S
Chapter 3: SQL Data Definition LanguageCREATE TABLE (Queue Table Form)
152 SQL Quick Reference
ED
data type
UNIQUE
PRIMARY KEY
CHECK ( boolean condition )
column_name
UNIQUE
1101B222
PRIMARY KEY
( column_name )
CONSTRAINT name
CHECK ( boolean_condition )
ColumnStorageAttributes
ColumnConstraintAttributes
ColumnDefinition
UniqueDefinition
CheckDefinition
)
,
,
Tab
le L
evel
Def
initi
on
CONSTRAINT
GENERATED ALWAYS AS IDENTITY
START WITH(
INCREMENT BY
BY DEFAULT
name
CONSTRAINT name
data type
COMPRESS
NULLNOT
constant
constant
,
( )
255
NULL
NULL
(
NO
MINVALUE
NO
MAXVALUE
NO
CYCLE
CB QITS_column_name NOT NULL DEFAULTTIMESTAMP(6)
(
DC
data type
CHECK ( boolean condition )
CURRENT_TIMESTAMP
ColumnConstraintAttributesCONSTRAINT name
(6)
Chapter 3: SQL Data Definition LanguageCREATE TABLE (Queue Table Form)
SQL Quick Reference 153
1101C223
;
E
,Index
Definition
,
PRIMARY INDEX primary_index_column( )
UNIQUE index_name
INDEX
UNIQUE index_name
INDEX ORDER BYVALUES
HASH
index_nameorder_column_name( )
64,
( )index_column_name
64
64,
( )index_column_name
Chapter 3: SQL Data Definition LanguageCREATE TABLE (Queue Table Form)
154 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE TABLE (Queue Table Form)
SQL Quick Reference 155
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
Chapter 3: SQL Data Definition LanguageCREATE TRANSFORM/ REPLACE TRANSFORM
156 SQL Quick Reference
CREATE TRANSFORM/REPLACE TRANSFORM
CREATE TRANSFORM FORSYSUDTLIB.
UDT_name transform_group_name
REPLACE
1101B360
A
SPECIFICTO SQL WITH
SPECIFIC FUNCTION specific_function_name
FUNCTION function_name
FOR UDT_namespecific_method_name
INSTANCE
METHOD,
(
(
method_name
data_type
UDT_name
A B
(
(
,(
data_type
UDT_name
(
;
SPECIFICFROM SQL WITH
SPECIFIC FUNCTION specific_function_name
FUNCTION function_name
UDT_nameFORspecific_method_name
INSTANCE
METHOD,
(method_name
data_type
UDT_name
B
(
,(
data_type
UDT_name
(
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
Chapter 3: SQL Data Definition LanguageCREATE TRIGGER/ REPLACE TRIGGER
SQL Quick Reference 157
CREATE TRIGGER/REPLACE TRIGGER
1101D202
;D
BEGIN ATOMIC ENDSQL_procedure_statement ;
SQL_procedure_statement ;
SQL_procedure_statement ;(
( SQL_procedure_statement ;
)
)
CREATE Atrigger_nameTRIGGER
REPLACE database_name. ENABLED
DISABLED
BEFORE
AFTER
DC
FOR EACH ROW WHEN (search_condition)
STATEMENT
DELETE
UPDATE
A INSERT BON table_name
database_name.
OF
( ),
column_name
,column_name
ORDER integer
old_transition_table_nameOLD_TABLE
ASOLD TABLE
OLD_NEW_TABLE ( )old_new_table_name old_value, new_value
CB REFERENCING old_transition_variable_nameOLD
ASROW
new_transition_table_nameNEW_TABLE
AS
AS
NEW TABLE
new_transition_variable_nameNEW
ASROW
Chapter 3: SQL Data Definition LanguageCREATE TYPE (Distinct Form)
158 SQL Quick Reference
CREATE TYPE (Distinct Form)
CREATE TYPE AS
CHARACTER SET
)
SYSUDTLIB.
SYSUDTLIB.
1101A362
B
a
a
A
C
b
(
UDT_name predefined_data_type
server_character_set
CHARACTER SET server_character_setINSTANCE
B
b
B RETURNS
METHOD method_name predefined_data_type
SYSUDTLIB.
parameter_name
UDT_name
UDT_name
AS LOCATOR
,
specific_method_nameSPECIFIC
SYSUDTLIB.
CHARACTER SET server_character_set
predefined_data_type
predefined_data_type
SYSUDTLIB.
SYSUDTLIB.
UDT_name
AS LOCATOR CAST FROM
AS LOCATOR
A FINAL
;
SELF AS RESULT
language_clause
SQL_data_access
SQL_data_access
SQL_data_access
language_clause
language_clause
SYSUDTLIB.
SPECIFIC specific_method_name
TD_GENERAL
PARAMETER STYLE SQL
NOT
DETERMINISTIC
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
SYSUDTLIB.
SPECIFIC specific_method_name
TD_GENERAL
PARAMETER STYLE SQL
NOT
DETERMINISTIC
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
Chapter 3: SQL Data Definition LanguageCREATE TYPE (Distinct Form)
SQL Quick Reference 159
1101A622
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
, integer
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
Data Type Declaration
WITH TIMEZONE(fractional_seconds_precision)
(fractional_seconds_precision)
,fractional_seconds_precision
TIME
WITH TIMEZONE
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
INTERVAL SECOND
(precision)
(precision)
(precision )
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
BINARY LARGE OBJECT ( (integer
BLOB GKM
CHARACTER LARGE OBJECT ( (integer
CLOB GKM
Chapter 3: SQL Data Definition LanguageCREATE TYPE (Structured Form)
160 SQL Quick Reference
CREATE TYPE (Structured Form)
CREATE TYPE AS
,
CHARACTER SET
SYSUDTLIB.
1101A361
B
A
A (
UDT_name
UDT_name
attribute_name predefined_data_type
server_character_set
(
Chapter 3: SQL Data Definition LanguageCREATE TYPE (Structured Form)
SQL Quick Reference 161
NOT FINAL
,
INSTANTIABLE
)
,
1101A645
D
a
a
b
b
C
;B
(
CHARACTER SET server_character_set
SELF AS RESULT
C
language_clause
SQL_data_access
SQL_data_access
SQL_data_access
language_clause
language_clause
predefined_data_type
SYSUDTLIB.
SPECIFIC specific_method_name
TD_GENERAL
PARAMETER STYLE SQL
SYSUDTLIB.
parameter_name
UDT_name
AS LOCATOR
SYSUDTLIB.INSTANCE
CONSTRUCTOR
METHOD method_name
D
SYSUDTLIB.
SPECIFIC specific_method_name
NOT
DETERMINISTIC
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
SYSUDTLIB.
SPECIFIC specific_method_name
TD_GENERAL
PARAMETER STYLE SQL
NOT
DETERMINISTIC
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
Chapter 3: SQL Data Definition LanguageCREATE TYPE (Structured Form)
162 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageCREATE TYPE (Structured Form)
SQL Quick Reference 163
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
Chapter 3: SQL Data Definition LanguageCREATE USER
164 SQL Quick Reference
CREATE USER
1101C007
CREATE USER user_name
;
AS
FROM database_name
A
A
SPOOL = n
BYTES
B
DEFAULT DATABASE = database_name
FALLBACK
PROTECTIONNO
JOURNAL
BEFORENO
DUAL
DEFAULT JOURNAL TABLE =
database_name.
STARTUP = 'string;'
COLLATION =
'account_ID'
,ACCOUNT = 'account_ID'
AFTER JOURNAL
NO
DUAL
LOCAL
NOT LOCAL
,
TEMPORARY = n
bytes
TIME ZONE = LOCAL
sign
quotestring
NULL
collation_sequence
table_name
( )
DATEFORM = INTEGERDATE
ANSIDATE
NULL
DEFAULT ROLE =
PROFILE = profile_name
role_nameNONENULLALL
NULL
DEFAULT CHARACTER SET server_character_set
PERMANENT
PASSWORD = password
PASSWORD = password= n
BYTES
B
PERM , ,
PERMANENT = n
BYTESPERM,
Chapter 3: SQL Data Definition LanguageCREATE VIEW/ REPLACE VIEW
SQL Quick Reference 165
CREATE VIEW/REPLACE VIEW
CREATE VIEW A
( column_name )database_name.user_name.
,view_name
LOCKING
DATABASE
database_nameuser_name
TABLE
table_name
VIEW
view_name
ROW
LOCK FOR
IN
MODE
ACCESS
EXCLUSIVE
SHARE
READ
WRITE
EXCL
NOWAIT
BA
SELECT
ALLDISTINCT TOP n
m PERCENTWITH TIES
CSEL
(
FROM table_nameview_name
WHERE search_condition
F
D
F G
HAVING condition QUALIFY condition
correlation_nameAS
joined_table JOIN joined_table ON search_conditionINNERLEFT
RIGHTFULL
OUTER
E
CROSS JOIN single_table
( subquery ) derived_table_nameAS
( column_name )
,
,
E
DerivedTables
JoinedTables
SingleTables
WITH CHECK OPTION
AS
B
table_name.*
expression
expression_alias
D,
AS
*C
CV
REPLACE VIEW
GROUP BY
,
ordinary_grouping_set
empty_grouping_set
rollup_list
cube_list
grouping_sets_specification
database_name.user_name.
database_name.user_name.
database_name.user_name.
user_name.database_name.
1101A621
G
;)ORDER BY
,
DESC
ASC
column_positioncolumn_name_alias
column_name
table_name.
database_name.user_name.
expression
Chapter 3: SQL Data Definition LanguageDATABASE
166 SQL Quick Reference
DATABASE
DELETE DATABASEDELETE USER
DROP AUTHORIZATION
DROP CAST
FF07A016
DATABASE database_name
;
FF07D026
DELETE name
DEL ;DATABASE
USER ALL
1101A324
;DROP AUTHORIZATION authorization_name
database_name.
DROP CAST AS( )source_data_type target_data_type
1101A325
;database_name.
Chapter 3: SQL Data Definition LanguageDROP CAST
SQL Quick Reference 167
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageDROP DATABASE
168 SQL Quick Reference
DROP DATABASE
DROP ERROR TABLE
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101A070
DROP DATABASE database_name
;
1101A437
DROP ERROR TABLE FOR data_table_namedatabase_name. ;
1101A438
DROP TABLE error_table_namedatabase_name. ;
Chapter 3: SQL Data Definition LanguageDROP FUNCTION
SQL Quick Reference 169
DROP FUNCTION
1101B096
SPECIFIC FUNCTIONDROP
,
specific_function_name
FUNCTION
data_type( )
function_name
database_name. ;
database_name.
Chapter 3: SQL Data Definition LanguageDROP FUNCTION
170 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageDROP GLOP SET
SQL Quick Reference 171
DROP GLOP SET
DROP HASH INDEX
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101A638
DROP GLOP SET
database_name.
user_name.
GLOP_set_name
;
1101A468
DROP HASH INDEX;
hash_index_namedatabase_name.
Chapter 3: SQL Data Definition LanguageDROP INDEX
172 SQL Quick Reference
DROP INDEX
DROP index_name Syntax
DROP index_definition Syntax
DROP JOIN INDEX
DROP MACRO/DROP PROCEDURE/DROP TABLE/
1101A469
DROP INDEX
join_index_name
index_namedatabase_name.
ON;
table_nameTEMPORARY database_name.
database_name.
1101F003
ON
;
join_index_name
DROP INDEX ( column_name )
,
index_name
A
ORDER BY
BA
( column_name_2 )
B table_name
TEMPORARY
VALUES
HASH
FF07D018
DROP JOIN INDEX
;
join_index_name
database_name.
Chapter 3: SQL Data Definition LanguageDROP ORDERING
SQL Quick Reference 173
DROP TRIGGER/DROP VIEW
DROP ORDERING
DROP PROFILE
1101E093
DROP;
MACRO macro_name
database_name.
TABLE table_name
TRIGGER trigger_name
TEMPORARY
VIEW view_name
ALL
database_name.
PROCEDURE procedure_name
database_name.
database_name.
database_name.
1101A326
;DROP ORDERING FOR user_defined_type_name
database_name.
DROP PROFILE
;
profile_namedatabase_name.
1101A470
Chapter 3: SQL Data Definition LanguageDROP REPLICATION GROUP
174 SQL Quick Reference
DROP REPLICATION GROUP
DROP REPLICATION RULESET
DROP ROLE
1101A240
DROP REPLICATION GROUP replication_group_name
;
1101A554
;DROP REPLICATION RULESET FORrule_set_name replication_group_name
DROP ROLE role_namedatabase_name.EXTERNAL ;
1101B289
Chapter 3: SQL Data Definition LanguageDROP STATISTICS (Optimizer Form)
SQL Quick Reference 175
DROP STATISTICS (Optimizer Form)
Syntax (Alternate)
1101I299
ON ;
join_index_name
A table_name
TEMPORARY
hash_index_name
UNIQUE
COLUMN
index_name
A
ORDER BY (
( )
)
column_name_1
column_name
,
column_name( )
,
INDEX
ALL
,
column_name_2
VALUES
HASH
PARTITION
PARTITION
database_name.user_name.
database_name.
64
64
DROP STATISTICS
DROP STAT
DROP STATS
database_name.user_name.
1101G029
COLUMNPARTITION
PARTITION
column_name_1
INDEX ( column_name_2 )
INDEX name
,
DROP STATISTICS table_name
database_name.
ON
join_index_name
;
A
A
TEMPORARYDROP STATS
DROP STAT
hash_index_name
( column_name_1 )
, 64
64
database_name.
Chapter 3: SQL Data Definition LanguageDROP TRANSFORM
176 SQL Quick Reference
DROP TRANSFORM
DROP TYPE
DROP USER
DROP TRANSFORM FORtransform_group_name
1101A327
;user_defined_type_name
database_name.
A
A
1101A328
;DROP TYPE user_defined_type_name
database_name.
1101A071
DROP USER user_name
;
Chapter 3: SQL Data Definition LanguageEND LOGGING
SQL Quick Reference 177
END LOGGING
END LOGGING
DENIALS WITH TEXT
ON
;
A
ALL
BY
A
B
B,
operation
GRANTdatabase_nameuser_name
,
,
ON AUTHORIZATION authorization_name
object_name
VIEW
DATABASE database_name
USER user_name
TABLETABLE
MACRO
1101I371
database_name.
user_name.
PROCEDURE
FUNCTION
TYPE
Chapter 3: SQL Data Definition LanguageEND QUERY LOGGING
178 SQL Quick Reference
END QUERY LOGGING
LOGGING ONLINE ARCHIVE OFF
LOGGING ONLINE ARCHIVE ON
1101D110
;ONEND QUERY LOGGING ALL
user_name
'account_name'
'application_name'
ACCOUNT =
'account_name'
,
( (
user_name
,
'account_name'ACCOUNT =
RULES
'account_name'
,
( (
APPLNAME=
'application_name'
,
( (
1101A435
LOGGING ONLINE ARCHIVE OFF FOR
,OVERRIDE
database_name
qualifier_database_name. table_name
;
,
,
1101A434
LOGGING ONLINE ARCHIVE ON FOR database_name
qualifier_database_name. table_name
;
,
,
Chapter 3: SQL Data Definition LanguageMODIFY DATABASE
SQL Quick Reference 179
MODIFY DATABASE
1101A471
MODIFY DATABASE database_name;
FALLBACK
PROTECTIONNO
NO
DUAL
JOURNAL
BEFORE
AS PERMANENT
PERM
= number
BYTES
SPOOL = number
BYTES
DEFAULT JOURNAL TABLE = journal_table_name
database_name.DROP DEFAULT JOURNAL TABLE
= journal_table_name
,
NO
DUAL
AFTER JOURNAL
LOCAL
NOT LOCAL
ACCOUNT = 'account_ID'
TEMPORARY =
bytes
number
database_name.
Chapter 3: SQL Data Definition LanguageMODIFY PROFILE
180 SQL Quick Reference
MODIFY PROFILE
MODIFY PROFILE AS
,
ACCOUNT = 'account_id',
'account_id'
NULL
DEFAULT DATABASE = database_name
SPOOL = n
BYTES
( ) ;
profile_name
database_name.
A
1101B467
A
TEMPORARY = n
BYTES
PASSWORD = EXPIRE =
,
n )(
ATTRIBUTESMINCHAR = n
MAXCHAR = n
DIGITS = c
SPECCHAR = c
MAXLOGONATTEMPTS = n
LOCKEDUSEREXPIRE = n
REUSE = n
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
COST PROFILE = cost_profile_name
NULL
RESTRICTWORDS = cNULL
Chapter 3: SQL Data Definition LanguageMODIFY USER
SQL Quick Reference 181
MODIFY USER
1101B008
MODIFY USER username
FALLBACK
NO
AS
= number
BYTES
PERMANENT
PERM
PASSWORD = password
RELEASE PASSWORD LOCK
= number
BYTES
SPOOL
=ACCOUNT 'account_ID'
DEFAULT DATABASE =database_name
DEFAULT JOURNAL TABLE =
database_name
DROP DEFAULT JOURNAL TABLE
= table_name
PROTECTION
,
NO
DUAL
JOURNAL
BEFORE
NO
DUAL
AFTER JOURNAL
LOCAL
NOT LOCAL
( 'account_ID' ,'account_ID' )
STARTUP =
TIME ZONE = LOCAL
sign
quotestring
NULL
DATEFORM = INTEGERDATE
ANSIDATE
NULL
DEFAULT ROLE =
PROFILE = profile_name
role_nameNONENULL
NULL
TEMPORARY = number
BYTES
DEFAULT CHARACTER SET server_character_set
COLLATION = collation_sequence
NULL
quotestring
FOR USER
table_name
ALL
Chapter 3: SQL Data Definition LanguageRENAME FUNCTION
182 SQL Quick Reference
RENAME FUNCTION
1101B105
;
RENAME SPECIFIC FUNCTION
FUNCTIONAS
function_name
TOspecific_function_name
database_name.
new_function_name
new_specific_function_name
,
data_type( )
A
A
database_name.
Chapter 3: SQL Data Definition LanguageRENAME FUNCTION
SQL Quick Reference 183
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageRENAME MACRO/ RENAME PROCEDURE/ RENAME TABLE/ RENAME TRIGGER/ RENAME VIEW
184 SQL Quick Reference
RENAME MACRO/RENAME PROCEDURE/RENAME TABLE/RENAME TRIGGER/RENAME VIEW
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101G159
RENAME
old_name
;
MACRO
TABLE
VIEW
TO new_name
AS
TRIGGER
database_name.
database_name.
PROCEDURE
A
A
Chapter 3: SQL Data Definition LanguageREPLACE METHOD
SQL Quick Reference 185
REPLACE METHOD
1101B370
B
'
EXTERNAL
NAME external_method_name
L delimiter library_name
ISC
delimiter name_on_server delimiter include_name
O delimiter
delimiter
name_on_server delimiter object_name
S delimiter name_on_server delimiter source_name
;
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
SPECIFIC METHODREPLACE
INSTANCECONSTRUCTOR
A
a
A
a
parameter_name
)(
AS LOCATOR
FOR UDT_name
specific_method_name
METHOD
data_typeUDT_name
method_name
B
Chapter 3: SQL Data Definition LanguageREPLACE METHOD
186 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageSET QUERY_BAND
SQL Quick Reference 187
SET QUERY_BAND
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101B433
SET QUERY_BAND = pair_name = pair_value ; FOR SESSION
TRANSACTIONNONE UPDATE
' ';
Chapter 3: SQL Data Definition LanguageSET ROLE
188 SQL Quick Reference
SET ROLE
SET SESSION
SET SESSION ACCOUNT
1101C040
;SET ROLE role_name
database_name.
NONE
NULL
ALL
EXTERNAL
1101U141
;SET SESSION
SS
ACCOUNT= 'account_id' FOR
REQUEST
SESSION
OVERRIDE REPLICATION
OFF
ON
DATEFORM =
CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL isolation_level
DATABASE
FOR TABLE table_name
TRACE
database_name
INTEGERDATE
ANSIDATE
COLLATION collation_sequence
FUNCTION TRACE USING
OFF
mask_string
A
A
B
B
database_name.
FF07D292
;ACCOUNT=SET SESSION
SS
'account_id' FOR
REQUEST
SESSION
Chapter 3: SQL Data Definition LanguageSET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
SQL Quick Reference 189
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
SET SESSION COLLATION
SET SESSION DATABASE
SET SESSION DATEFORM
1101A315
;SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL isolation_level
FF07D291
;COLLATIONSET SESSION
SS
collation_sequence
1101A076
;SET SESSION DATABASE database_name
SS
FF07D293
;DATEFORM=SET SESSION
SS INTEGERDATE
ANSIDATE
Chapter 3: SQL Data Definition LanguageSET SESSION FUNCTION TRACE
190 SQL Quick Reference
SET SESSION FUNCTION TRACE
SET SESSION OVERRIDE REPLICATION
SET SESSION SUBSCRIBER
SET TIME ZONE
1101A099
SET SESSION FUNCTION TRACE USING
TABLE table_name
TRACE
OFF
FORmask_string
;database_name.
A
A
B
B
1101A238
SET SESSION OVERRIDE REPLICATION ON
OFF ;
1101A555
;SET SESSION SUBSCRIBER ON
OFF
FF07D143
;LOCAL
INTERVAL
SET TIME ZONE
USER
sign'time_zone_displacement' HOUR TO MINUTE
Chapter 3: SQL Data Definition LanguageHELP
SQL Quick Reference 191
HELP
1101C387
;
DATABASE database_name
HELP
MACRO macro_name
COLUMN *
COLUMN
COLUMN . *
INDEX table_name
( column_name )
,
CONSTRAINT table_name.name
COLUMN FROM ERROR TABLE FOR data_table_namecolumn_name
.column_name
join_index_name
hash_index_name
JOIN INDEX join_index_name
TEMPORARY
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
COLUMN expression
,
FUNCTION
data_type( )
function_name
HASH INDEX hash_index_name
FROM table_name
join_index_name
hash_index_name
table_name
join_index_name
hash_index_name
table_name
join_index_name
hash_index_name
CAST UDT_name
SOURCE
TARGET
database_name.user_name.
user_name.database_name.
,
METHOD
SYSUDTLIB.
SYSUDTLIB.
method_name
UDT_nameFOR
INSTANCE
CONSTRUCTOR
SPECIFIC METHOD
( )UDT_name
specific_method_name
A
B
X1 X2B
A
COLUMN FROM error_table_namecolumn_name
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP
192 SQL Quick Reference
1101F388
USER user_name
TABLE table_name
VIEW view_name
SESSION
join_index_name
TRIGGER
table_name
trigger_name
VOLATILE TABLEvolatile_table_name
PROCEDURE
SPECIFIC FUNCTIONSYSUDTLIB.
specific_function_name
procedure_name
ATTRIBUTES
ATTR
ATTRS
REPLICATION GROUP replication_group_name
TRANSFORM UDT_name
TYPE UDT_name
ATTRIBUTE
METHOD
X1 X2
TABLE error_table_name
ERROR TABLE FOR data_table_name
SYSUDTLIB.
SYSUDTLIB.
table_nameSTATISTICS
,
COLUMN
PARTITION
column_name
column_name
STATS
STAT
TEMPORARY
join_index_name
hash_index_name
view_name
A
A
(
INDEX index_name
(
,
column_name( (
PARTITION
STATISTICS
STATS
STAT
table_nameview_name
FROM QCD_name
A
FOR QUERY query_ID
B
USING MODIFIEDSAMPLEID statistics_ID
B
,
COLUMN
PARTITION
column_name
column_name
A
(
INDEX index_name
(
,
column_name( (
PARTITION
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP CAST
SQL Quick Reference 193
HELP CAST
HELP COLUMN
Syntax 1
Syntax 2
Syntax 3
Syntax 4
HELP CAST
SYSUDTLIB.
UDT_name
1101B331
;SOURCE
TARGET
1101B473
HELP COLUMN
,
;column_name FROM table_name
,
join_index_name
hash_index_nameuser_name.database_name.
1101G245
HELP COLUMN;
* FROM table_name
,
join_index_name
hash_index_nameuser_name.database_name.
1101G246
HELP COLUMN .;
table_name column_name
,
join_index_name
hash_index_nameuser_name.database_name.
1101H247
HELP COLUMN
,
;table_name. *
join_index_name.
hash_index_name.user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP CONSTRAINT
194 SQL Quick Reference
Syntax 5
Syntax 6
Syntax 7
Syntax 8
HELP CONSTRAINT
1101A462
HELP COLUMN
,
;
expression
1101C463
HELP COLUMN *
* ,
,
;
expression table_name.
join_index_name.
hash_index_name.
table_name. expression
join_index_name.
hash_index_name.user_name.database_name.
user_name.database_name.
1101B441
HELP COLUMN FROM ERROR TABLE FOR data_table_namecolumn_name
;user_name.database_name.
1101B442
HELP COLUMN FROM error_table_namecolumn_name
;user_name.database_name.
1101B516
HELP CONSTRAINT
;
table_name.
view_name.
constraint_name
user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP DATABASE/ HELP USER
SQL Quick Reference 195
HELP DATABASE/HELP USER
HELP ERROR TABLE
Syntax 1
Syntax 2
HELP FUNCTION
FF07D240
HELP
user_name ;
database_name
USER
DATABASE
1101B439
HELP ERROR TABLE FOR data_table_name
;user_name.database_name.
1101B440
HELP TABLE error_table_name
;user_name.database_name.
1101C098
SPECIFIC FUNCTIONHELP
,
specific_function_name
FUNCTION
data_type( )
function_name
;user_name.database_name.
user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP FUNCTION
196 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageHELP HASH INDEX
SQL Quick Reference 197
HELP HASH INDEX
HELP INDEX
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
HELP HASH INDEX hash_index_name;
1101B474user_name.database_name.
1101B477
;
HELP INDEXTEMPORARY ,
( )
table_name
column_namejoin_index_name
hash_index_name
view_name
user_name.database_name.
user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP JOIN INDEX
198 SQL Quick Reference
HELP JOIN INDEX
HELP MACRO/HELP TABLE/HELP VIEW
HELP METHOD
1101A559
HELP JOIN INDEX join_index_name;
user_name.database_name.
1101G241
HELP;
MACRO
TABLE
macro_name
table_name
VIEW view_name
error_table_name
user_name.database_name.
user_name.database_name.
user_name.database_name.
HELP
,
METHOD
1101A330
;
database_name.
method_name
UDT_nameFOR
INSTANCE
CONSTRUCTOR
SPECIFIC METHOD
database_name.
( )user_defined_type_name
specific_method_name
A
B
B
A
Chapter 3: SQL Data Definition LanguageHELP PROCEDURE
SQL Quick Reference 199
HELP PROCEDURE
HELP REPLICATION GROUP
HELP SESSION
HELP STATISTICS (Optimizer Form)
YSHP004
HELP PROCEDURE procedure_name
database_name. ; ATTRIBUTES
ATTRS
ATTR
1101A237
HELP REPLICATION GROUP replication_group_name
;
FF07D242
HELP SESSION
;
1101C375
HELP
;
table_nameSTATISTICS
,
COLUMN
PARTITION
column_name
column_name
STATS
STAT
TEMPORARY
join_index_name
hash_index_name
view_name
A
A
(
INDEX index_name
(
,
column_name( (
PARTITION
user_name.database_name.
user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP STATISTICS (QCD Form)
200 SQL Quick Reference
HELP STATISTICS (QCD Form)
HELP TRANSFORM
HELP TRIGGER
1101J243
HELP STATISTICS
STATS
STAT
table_nameview_name
FROM QCD_name
;
A
FOR QUERY query_ID
B
USING MODIFIEDSAMPLEID statistics_ID
B
,
COLUMN
PARTITION
column_name
column_name
A
(
INDEX index_name
(
,
column_name( (
PARTITION
user_name.database_name.
HELP TRANSFORM UDT_name
1101B332
;user_name.database_name.
1101F038
HELP TRIGGER;table_name
trigger_name
user_name.database_name.
Chapter 3: SQL Data Definition LanguageHELP TYPE
SQL Quick Reference 201
HELP TYPE
HELP VOLATILE TABLE
HELP (Online Form)
HELP TYPE
SYSUDTLIB.
UDT_name
1101B329
;ATTRIBUTE
METHOD
1101B476
HELP VOLATILE TABLE
;volatile_table_name
1101A006
;' HELPHELP '
' SQL
commandname
' ARCHIVE
commandname
' DUMP
commandname
' FASTEXPORT
commandname
' FASTLOAD
commandname
' MULTILOAD
commandname
' PMPC
commandname
' TPCCONS
commandname
' SPL
commandname
Chapter 3: SQL Data Definition LanguageSHOW
202 SQL Quick Reference
SHOW
SHOW CAST/SHOW ERROR TABLE/SHOW FUNCTION/SHOW HASH INDEX/SHOW JOIN INDEX/SHOW MACRO/SHOW METHOD/SHOW PROCEDURE/SHOW REPLICATION GROUP/SHOW TABLE/SHOW TRIGGER/SHOW TYPE/SHOW VIEW
KO01A012
SHOW dml_statement
;QUALIFIED
Chapter 3: SQL Data Definition LanguageSHOW CAST/ SHOW ERROR TABLE/ SHOW FUNCTION/ SHOW HASH INDEX/ SHOW JOIN INDEX/ SHOW MACRO/ SHOW METHOD/
SQL Quick Reference 203
General Syntax
1101I024
SHOW;
HASH INDEX hash_index_name
TABLE table_name
TRIGGER trigger_name
TEMPORARY
VIEW view_name
MACRO macro_name
PROCEDURE procedure_name
JOIN INDEX join_index_name
REPLICATION GROUP replication_group_name
ERROR TABLE FOR data_table_name
TABLE error_table_name
SPECIFIC FUNCTION
,
specific_function_name
FUNCTION
INSTANCE
CONSTRUCTOR
SYSUDTLIB.
SPECIFIC METHOD specific_method_name
data_type
UDT_name
( )
function_name
,
data_type
UDT_name
( )
METHOD
FOR
TYPE UDT_name
UDT_name
method_name
SYSUDTLIB.
SYSUDTLIB.
SYSUDTLIB.
CAST UDT_name
A
A
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
Chapter 3: SQL Data Definition LanguageSHOW CAST/ SHOW ERROR TABLE/ SHOW FUNCTION/ SHOW HASH INDEX/ SHOW JOIN INDEX/ SHOW MACRO/ SHOW METHOD/
204 SQL Quick Reference
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageSHOW CAST/ SHOW ERROR TABLE/ SHOW FUNCTION/ SHOW HASH INDEX/ SHOW JOIN INDEX/ SHOW MACRO/ SHOW METHOD/
SQL Quick Reference 205
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
Chapter 3: SQL Data Definition LanguageSHOW CAST/ SHOW ERROR TABLE/ SHOW FUNCTION/ SHOW HASH INDEX/ SHOW JOIN INDEX/ SHOW MACRO/ SHOW METHOD/
206 SQL Quick Reference
Embedded SQL Syntax
1101I023
host_variable_nameINTO
A
: : host_indicator_name
A
INDICATOR
SHOW HASH INDEX hash_index_name
TABLE table_name
TRIGGER trigger_name
TEMPORARY
VIEW view_name
MACRO macro_name
PROCEDURE procedure_name
JOIN INDEX join_index_name
SPECIFIC FUNCTION
,
specific_function_name
FUNCTION
SYSUDTLIB.
data_type( )
function_name
REPLICATION GROUP replication_group_name
CAST UDT_name
TYPE UDT_name
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
user_name.database_name.
SYSUDTLIB.
Chapter 3: SQL Data Definition LanguageSHOW CAST/ SHOW ERROR TABLE/ SHOW FUNCTION/ SHOW HASH INDEX/ SHOW JOIN INDEX/ SHOW MACRO/ SHOW METHOD/
SQL Quick Reference 207
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 3: SQL Data Definition LanguageSHOW QUERY LOGGING
208 SQL Quick Reference
SHOW QUERY LOGGING
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
ALLSHOW QUERY LOGGING ON
1101A547
;user_name 'account_name'ACCOUNT =
'account_name'
,
( (
APPLNAME = 'application_name',
( ('application_name'
SQL Quick Reference 209
CHAPTER 4 SQL Data Control Language
GIVE
GRANT
Monitor Form
Role Form
FF07A025
GIVE;
database_name TO recipient_name
user_name
FF07A056
GRANT MONITOR TO
,
ALLuser_name
WITH GRANTOPTION
;
PRIVILEGES
BUT NOT
A
A
PUBLIC
,
,
monitor_privilege
monitor_privilege
GRANT TOrole_name
,
;
KZ01a008
user_name
role_name
,
WITH ADMIN OPTION
Chapter 4: SQL Data Control LanguageGRANT
210 SQL Quick Reference
SQL Form
GRANT
1101W055
PRIVILEGES
ALL
,
ON
database_nameuser_namerole_name
object_name
object_name
TO user_name
;WITH GRANT OPTION
B
privilege
, 25
ALL
,
PUBLIC
ALL BUT
privilege
,
PROCEDURE
PUBLIC
procedure_name
FUNCTION
SYSUDTLIB.
function_name
SPECIFIC FUNCTION specific_function_name
role_name
role_privilege
,
profile_privilege
,
,
data type
( )
parameter_name
TYPE UDT_name
BA
A
database_name.user_name.
database_name.user_name.
database_name.user_name.
database_name.user_name.
database_name.user_name.
Chapter 4: SQL Data Control LanguageGRANT
SQL Quick Reference 211
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 4: SQL Data Control LanguageGRANT CONNECT THROUGH
212 SQL Quick Reference
GRANT CONNECT THROUGH
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
A
WITH ROLE;
role_name
GRANT CONNECT THROUGH TO
,
trusted_user_name
application_user_nameA
, 1525
1101A541
PERMANENT permanent_user_name
, 25
WITH ROLE
WITHOUT ROLE
role_name
, 15
Chapter 4: SQL Data Control LanguageGRANT LOGON
SQL Quick Reference 213
GRANT LOGON
REVOKE
Monitor Form
Role Form
1101C027
GRANT LOGON
,
ON
ALL
host_id
WITH NULL PASSWORD ;AS DEFAULT
TO
,
FROM
user_name
GRANT OPTION FOR
1101A214
,
TO
FROM ALLuser_name
PUBLIC
A
REVOKE MONITORPRIVILEGES
BUT NOT
A
monitor_privilege
,
monitor_privilege
,
REVOKE role_name user_nameTO
,
;
KZ01a009
ADMIN OPTION FOR FROM role_name
,
Chapter 4: SQL Data Control LanguageREVOKE
214 SQL Quick Reference
SQL Form
REVOKEGRANT OPTION FOR
ALL
privilege
role_privilege
,
,ALL BUT
PRIVILEGES
,
,
TO
FROM ALLuser_name
PUBLIC
role_name
A
B
1101V061
A ON
object_name
object_name
PROCEDURE
SYSUDTLIB.
procedure_name
B
a
a
FUNCTION function_name
SPECIFIC FUNCTION specific_function_name
profile_privilege
,
,
data type
( )
parameter_name
TYPE UDT_name
database_nameuser_namerole_namePUBLIC
database_name.user_name.
database_name.user_name.
database_name.user_name.
database_name.user_name.
database_name.user_name.
Chapter 4: SQL Data Control LanguageREVOKE
SQL Quick Reference 215
1101A535
INTEGER
SMALLINT
BIGINT
( integer )
FLOAT
NUMERIC
DECIMAL
BYTEINT
DATE
REAL
DOUBLE PRECISION
( integer )
Data Type
WITH TIMEZONE(fractional_seconds_precision)
,fractional_seconds_precision
TIME
TIMESTAMP
TO MONTH(precision)
(precision)
(precision)
INTERVAL YEAR
INTERVAL MONTH
TO HOUR
INTERVAL DAY
MINUTE
SECOND
INTERVAL HOUR
INTERVAL MINUTE
TO SECOND
)
INTERVAL SECOND
(precision)
(precision)
(precision )
(precision)
TO MINUTE
SECOND
( fractional_seconds_precision )
( fractional_seconds_precision )
( fractional_seconds_precision )
PERIOD(TIMESTAMP WITH TIMEZONE
PERIOD(TIME
PERIOD(DATE)
A B
, integer
Chapter 4: SQL Data Control LanguageREVOKE CONNECT THROUGH
216 SQL Quick Reference
REVOKE CONNECT THROUGH
1101A536
BINARY LARGE OBJECT ( (integer
BLOB GKMCHARACTER LARGE OBJECT
CLOB
UDT_name
SYSUDTLIB. ST_Geometry
MBR
A B
CHAR
BYTE
GRAPHIC
LONG VARGRAPHIC
VARCHAR
CHAR VARYING
VARBYTE
VARGRAPHIC
( integer )
( integer )
LONG VARCHAR
1101A542
A
WITH ROLE ;role_name
REVOKE CONNECT THROUGH TO
FROM
,
PERMANENT
trusted_user_name
permanent_user_name
application_user_name
A
,
, 15
25
25
Chapter 4: SQL Data Control LanguageREVOKE LOGON
SQL Quick Reference 217
REVOKE LOGON
1101B036
REVOKE LOGON
,
ON
ALL
host_id
;AS DEFAULT
TO
,
FROM
user_name
Chapter 4: SQL Data Control LanguageREVOKE LOGON
218 SQL Quick Reference
SQL Quick Reference 219
CHAPTER 5 SQL Data Manipulation Language
Chapter 5: SQL Data Manipulation LanguageSELECT
220 SQL Quick Reference
SELECT
SELECT
ALL
.ALL
DISTINCT
table_name.column_name
TOP integer
decimal PERCENT WITH TIES
C
D
F
SEL
FROM
TABLE function_name
derived_table_name
( ( )
)
table_name
D
correlation_nameAS
join_table_name joined_tableINNERLEFT
RIGHTFULL
OUTER
a
b
CROSS JOIN
( subquery ) derived_table_nameAS
AS
( column_name )
,
,
ON search_condition
DerivedTables
JoinedTables
SingleTables
JOIN
WITHNon-recursive
Recursive
AS
*
Seed Statement UNION ALL(
AS ( )query_name
RECURSIVE query_name
select_expression
column_name( )
A
B
B
C
A
,
column_name( )
,
Seed Statement
Recursive Statement
1101B545
expression
,
( column_name )
,
RETURNS table_name
( column_name data_type
database_name.
)
,
table_name.*
expressionexpression_alias_name
,
AS
*
TableFunction
a b
LOCAL ORDER BY
HASH BY
( column_name ),
Chapter 5: SQL Data Manipulation LanguageSELECT
SQL Quick Reference 221
1101D295
H
L
;L
WITH,
expression_1
DESC
ASC
,expression_2BY
F
WITH,
expression_1
DESC
ASC
,expression_2BY
SAMPLE
WITH REPLACEMENT RANDOMIZED ALLOCATION
count_description
fraction_description
count_description
fraction_descriptionTHENconditionWHEN
,
,
count_description
fraction_descriptionELSE
END,
I
I
16
16
16
J
J
K
K
WHERE search_conditionG
HAVINGH
conditional_expressionQUALIFY search_conditionGROUP BY
,
ordinary_grouping_setempty_grouping_set
rollup_list
cube_listgrouping_sets_specification
G
ORDER BY
,
DESC
ASC
column_name_alias
column_position
column_name
expression
Chapter 5: SQL Data Manipulation LanguageSELECT
222 SQL Quick Reference
SELECT
FROM
expression
table_name.*
expression_alias_name
SEL DISTINCT
ALL
AS
A,
*
A
B
B
Seed Statement
1101C293
C
D
table_name
correlation_name
AS
AS
JOIN ONjoined_table search_conditionjoin_table_name
(subquery) derived_table_name
INNER
LEFT
RIGHT OUTER
FULL
CROSS JOIN
WHERE
SingleTables
JoinedTables
DerivedTables
search_condition
ordinary_group_setGROUP BYHAVING conditional_expression
empty_grouping_set
rollup_list
cube_list
grouping_sets_specification
C
column_name( )
,
,
QUALIFY search_condition
ORDER BY
,
DESC
ASC
column_name_alias
column_position
column_name
expression
Chapter 5: SQL Data Manipulation LanguageSELECT AND CONSUME
SQL Quick Reference 223
SELECT AND CONSUME
SELECT
FROM query_name
query_name LEFT JOIN ONjoined_table search_condition
OUTERjoin_table_name
correlation_name
expression
table_name.*
expression_alias_name
SEL
AS
A,*
,
A
B
B
Recursive Statement
1101B292
AS
1
1
1
table_namecorrelation_name
AS
query_nameRIGHT JOINjoin_table_name
OUTER joined_table
query_name INNER JOIN joined_table
join_table_name INNER JOIN query_name
WHERE
ImplicitJoin
ExplicitJoin
search_condition
SELECT AND CONSUME TOP 1 FROM
;
select_list queue_table_name
1101A220
Chapter 5: SQL Data Manipulation LanguageWITH [RECURSIVE] Request Modifier
224 SQL Quick Reference
WITH [RECURSIVE] Request Modifier
WITHNon-recursive
Recursive
AS Seed Statement UNION ALL(
AS ( )query_name
RECURSIVE query_name
select_expression
column_name( )
A
A
,
column_name( )
,
Seed Statement
Recursive Statement
1101A291
Chapter 5: SQL Data Manipulation LanguageWITH [RECURSIVE] Request Modifier
SQL Quick Reference 225
SELECT
FROM
expression
table_name.*
expression_alias_name
SEL DISTINCT
ALL
AS
A,
*
A
B
B
Seed Statement
1101C293
C
D
table_name
correlation_name
AS
AS
JOIN ONjoined_table search_conditionjoin_table_name
(subquery) derived_table_name
INNER
LEFT
RIGHT OUTER
FULL
CROSS JOIN
WHERE
SingleTables
JoinedTables
DerivedTables
search_condition
ordinary_group_setGROUP BYHAVING conditional_expression
empty_grouping_set
rollup_list
cube_list
grouping_sets_specification
C
column_name( )
,
,
QUALIFY search_condition
ORDER BY
,
DESC
ASC
column_name_alias
column_position
column_name
expression
Chapter 5: SQL Data Manipulation LanguageDISTINCT, ALL, and .ALL Options
226 SQL Quick Reference
DISTINCT, ALL, and .ALL Options
SELECT
FROM query_name
query_name LEFT JOIN ONjoined_table search_condition
OUTERjoin_table_name
correlation_name
expression
table_name.*
expression_alias_name
SEL
AS
A,*
,
A
B
B
Recursive Statement
1101B292
AS
1
1
1
table_namecorrelation_name
AS
query_nameRIGHT JOINjoin_table_name
OUTER joined_table
query_name INNER JOIN joined_table
join_table_name INNER JOIN query_name
WHERE
ImplicitJoin
ExplicitJoin
search_condition
1101A357
ALLDISTINCT
table_name.
column_name
.ALL*
Chapter 5: SQL Data Manipulation LanguageTOP n Operator
SQL Quick Reference 227
TOP n Operator
FROM Clause
TOP
PERCENT
integer
decimal
1101A231
WITH TIES
1101D235
FROM table_name
correlation_nameAS
joined_table JOIN joined_table ON search_conditionINNERLEFT
RIGHTFULL
OUTER
CROSS JOIN single_table
( subquery ) derived_table_nameAS ,
,
DerivedTables
JoinedTables
SingleTables
( )column_name
derived_table_nameAS ,
( )column_name
,
( )column_name
table_namedatabase_name.
data type
TABLE
RETURNS
)( (function_name,
TableFunctionDerivedTables
expression
A
BA
C
)B C
LOCAL ORDER BY
HASH BY
( column_name ),
Chapter 5: SQL Data Manipulation LanguageHASH BY Clause
228 SQL Quick Reference
HASH BY Clause
LOCAL ORDER BY Clause
WHERE Clause
Subqueries in Search Conditions
Syntax 1
Syntax 2: Logical Expressions
1101A550
HASH BY
,
column_name
1101A551
LOCAL ORDER BY column_name
,
FF06R011
WHERE search_condition
HH01B064
expression
IN
NOT IN
, ANY
SOME
comparison_operator
ALL
(query )
expression )(
HH01A065
EXISTS (query )
Chapter 5: SQL Data Manipulation LanguageGROUP BY Clause
SQL Quick Reference 229
GROUP BY Clause
CUBE Option
GROUPING SETS Option
ROLLUP Option
HAVING Clause
GROUP BY
,
ordinary_grouping_set
empty_grouping_set
rollup_list
cube_list
grouping_sets_specification1101S015
ordinary_grouping_set
1101A103
CUBE
ordinary_grouping_set( )
GROUPING SETS ordinary_grouping_set
empty_grouping_set
rollup_list
cube_list
grouping_sets_specification1101A104
,ordinary_grouping_set
1101A102
ROLLUP
ordinary_grouping_set( )
FF06R016
HAVING condition
Chapter 5: SQL Data Manipulation LanguageQUALIFY Clause
230 SQL Quick Reference
QUALIFY Clause
SAMPLE Clause
SAMPLEID Expression
FF07D087
QUALIFY search_condition
SAMPLE
WITH REPLACEMENT RANDOMIZED ALLOCATION
1101B065
count_description
fraction_description
count_description
fraction_descriptionTHENconditionWHEN
,
,
count_description
fraction_descriptionELSE
END,
A
A
B
B
C
C
16
16
16
FF07D180
SAMPLEID
Chapter 5: SQL Data Manipulation LanguageORDER BY Clause
SQL Quick Reference 231
ORDER BY Clause
WITH Clause
Outer Join
Null
1101A429
ORDER BY
,
DESC
ASC
column_name_alias
column_position
column_name
expression
FF06B014
WITH
,
expression_1
DESC
ASC
,
expression_2BY
1101A041
RIGHT
LEFT JOIN right_table ONleft_table
OUTER
FULL
join_condition
1101A308
;
pad_character
SQL_comment
Chapter 5: SQL Data Manipulation LanguageABORT
232 SQL Quick Reference
ABORT
BEGIN TRANSACTION
CALL
FF07R068
ABORT
FROM optionabort_message WHERE abort_condition
BEGIN TRANSACTION
GW01A040BT
IN argument
CALL procedure_name
database_name .
user_name .
( (
;
,
IN argument
INOUT argument
OUT argument
value_expression
?
INOUT argument
OUT argument
value_expression
?
1101B042
CAST AS data_type( )
parameter_name
OUT call placeholder
OUT call placeholder
out_call_variable
OUT call placeholder
Chapter 5: SQL Data Manipulation LanguageCHECKPOINT
SQL Quick Reference 233
CHECKPOINT
Interactive Syntax
Embedded SQL and Stored Procedure Syntax
COMMENT (Comment-Retrieving Form)
COMMIT
1101B021
CHECKPOINT table_name
, NAMED checkpoint_name ;
1101B002
CHECKPOINT table_name
database_name. ,NAMED
: label_host_variable
checkpoint_label
A
A INTO:
host_variable_name
B
B
: host_indicator_variable_name
INDICATOR
1101B533
;
COMMENTobject_kind
object_namedatabase_name.
user_name.
ON
COMMIT
GW01A006
WORK
RELEASE
Chapter 5: SQL Data Manipulation LanguageDELETE
234 SQL Quick Reference
DELETE
Basic/Searched Form
Join Condition Form
DELETE FROM table_name
delete_table_name
correlation_name
DEL
WHERE
ALL
condition
AS
correlation_name
1101C074
;
A
A
DELETE table_namedelete_table_name
correlation_nameDEL
WHERE
ALL
condition
AS
correlation_name
1101C073
;
A
A
1101S079
DELETE A
A
WHERE
ALL
condition
delete_table_name
correlation_nameFROM table_name
correlation_name
AS
,
;
DEL
Chapter 5: SQL Data Manipulation LanguageECHO
SQL Quick Reference 235
ECHO
END TRANSACTION
EXECUTE
Macro Form
INSERT/INSERT . . . SELECT
1101S022
ECHO
' string '
' command '
;
;
END TRANSACTION
GW01A041ET
1101B023
EXECUTEEXEC ;
macro_name
( parameter_name = constant_expression )
( constant_expression )
,
,
1101A446
INSERTINS ;
table_name
( column_name )
INTO VALUES,
VALUES
subquery logging errors
( column_name )
,
DEFAULT VALUES
,
( expression )
,
( expression )
Chapter 5: SQL Data Manipulation LanguageLOCKING Request Modifier
236 SQL Quick Reference
LOCKING Request Modifier
1101A447
LOGGING
logging errors
ERRORS
ALL WITH NO LIMIT
LIMIT OF error_limit
1101U031
LOCKING
DATABASE
database_name
TABLE
table_name
VIEW
view_name
ROW
OVERRIDE
LOCK FOR
IN
ACCESS
EXCLUSIVE
SHARE
READ
WRITE
CHECKSUM
EXCL
MODE NOWAIT
B
A
A
;B SQL_request
Chapter 5: SQL Data Manipulation LanguageMERGE
SQL Quick Reference 237
MERGE
MERGE
USING VALUES ( using_expression
source_table_name
target_tableINTO correlation_name
AS
WHEN MATCHED THEN UPDATE
LOGGING ERRORS
SET update_column=update_expression
UPD
match_condition ON
WHEN NOT MATCHED clause
(
( subquery AS(
,
A
A
( column_name
,
(
Bsource_table_name
( column_name
,
(
C
D
E
WHEN NOT MATCHED THEN INSERT
INS VALUES
insert_column VALUES
,
( )
,
C
1101B445
B
WHEN NOT MATCHED clause
WHEN NOT MATCHED clause
D
E
;
insert_expression
,
( )
ALL WITH NO LIMIT
LIMIT OF error_limit
Chapter 5: SQL Data Manipulation LanguageROLLBACK
238 SQL Quick Reference
ROLLBACK
UPDATE
Basic Form, No FROM Clause Syntax
Basic Form, FROM Clause Syntax
ROLLBACK
1101S032
WORK 'abort_message'
FROM_clause
A
A
WHERE_clause ;
UPDATE table_name A
UPDcorrelation_name
AS
1101B210
WHERE condition
ALL
SET column_name = expression
column_name.mutator_method_name
,
A
;
UPDATE table_name_1
1101B209
WHERE condition
ALL
SET column_name = expression
column_name.mutator_method_name
,
A
A
UPD
FROMcorrelation_name
correlation_name
table_name_2
AS
;
Chapter 5: SQL Data Manipulation LanguageUSING Request Modifier
SQL Quick Reference 239
Joined Tables Syntax
Upsert Form
USING Request Modifier
UPDATE table_name_1,
A
UPD FROMcorrelation_name
correlation_name table_name_2
AS
1101B206
WHERE condition
ALL
SET column_name = expression
column_name.mutator_method_name
,
A
;
1101F379
UPDATE Atable_name_1
ELSE BA
(
VALUES ;B
UPD
WHERE condition INSERT table_name_2
INS INTO
)expression
( )column_name
,
( )expression
,
VALUES
DEFAULT VALUES
,
correlation_nameAS
SET column_name = expression
column_name.mutator_method_name
,
1101I201
USING
AS DEFERRED
LOCATORBY NAME
,
( using_variable_name data_type )
SQL_request
data_type_attribute
A
A
Chapter 5: SQL Data Manipulation LanguageCOLLECT DEMOGRAPHICS
240 SQL Quick Reference
COLLECT DEMOGRAPHICS
COLLECT STATISTICS (QCD Form)
1101A472
COLLECT DEMOGRAPHICS FOR table_namedatabase_name.
table_name(
,
;
INTO QCD_name
ALL
WITH NO INDEX
A
A
)database_name.
PERCENT
C
ACOLLECT STATISTICS
STATS
STAT
FOR SAMPLE percentage
1101D004
COLUMN column_name
;
COLUMN ( column_name )
,
INDEX ( column_name )
,
INDEX index_name
INTO
SET QUERY query_ID
QCD_nameA B
table_namedatabase_name.user_name.
B C
UPDATE MODIFIED ON
SAMPLEID statistics_ID
PARTITION
PARTITION
Chapter 5: SQL Data Manipulation LanguageDROP STATISTICS (QCD Form)
SQL Quick Reference 241
DROP STATISTICS (QCD Form)
DUMP EXPLAIN
EXPLAIN Request Modifier
A
ADROP STATISTICS
STATS
STAT
FROM QCD_nameON
table_namedatabase_name.user_name.
1101C373
COLUMN
PARTITION
PARTITION
column_name ;
COLUMN ( column_name )
,
INDEX ( column_name )
,
INDEX index_name
64
64
INTODUMP EXPLAIN QCD_name
A
A
1101H321
;
SQL_request
AS query_plan_name
LIMIT CHECK STATISTICS
SQL
= n
1101C408
EXPLAIN
IN XML
COMPRESS
NODDLTEXT
NODDLTEXT
;
_requestSQL
Chapter 5: SQL Data Manipulation LanguageINITIATE INDEX ANALYSIS
242 SQL Quick Reference
INITIATE INDEX ANALYSIS
INITIATE PARTITION ANALYSIS
INITIATE INDEX ANALYSIS FOR
SET boundary_option = value
workload_name
ON
IN AS index_name_tagQCD_name
,
table_namedatabase_name.
,
,
KEEP INDEX USE MODIFIED STATISTICS
STATS
B
A
A
B C
CHECKPOINT checkpoint_trigger
TIME LIMIT = elapsed_time
NO
WITH INDEX TYPE numberC D
STAT
1101F013
;
D
INITIATE PARTITION ANALYSIS FOR workload_name
ON
IN AS result_name_tagQCD_name
,
table_namedatabase_name.
A
A
1101B443
;TIME LIMIT = elapsed_time
Chapter 5: SQL Data Manipulation LanguageINSERT EXPLAIN
SQL Quick Reference 243
INSERT EXPLAIN
RESTART INDEX ANALYSIS
INTO QCD_name
AS query_plan_name
INSERT EXPLAIN
WITH STATISTICS
AND DEMOGRAPHICS
NO
FOR
,
table_name
database_name.
A
B C
USING SAMPLE percentage
PERCENT
BA
FOR frequency
LIMIT
SQL
= n
1101I322
SQL_requestC
CHECK STATISTICS ;IN XML
COMPRESS
NODDLTEXT
NODDLTEXT
RESTART INDEX ANALYSIS
CHECKPOINT checkpoint_trigger
FOR IN QCD_nameworkload_name
AS index_name_tagA
A
B
1101C111
TIME LIMIT = elapsed_time ;
B
Chapter 5: SQL Data Manipulation LanguageDIAGNOSTIC COSTPRINT
244 SQL Quick Reference
DIAGNOSTIC COSTPRINT
DIAGNOSTIC DUMP COSTS
DIAGNOSTIC HELP COSTS
1101A225
DIAGNOSTIC COSTPRINT
NOT ;
ON FOR REQUEST
SESSION
IFP
SYSTEM
FF07D327
DIAGNOSTIC
;
target_system_nameDUMP COSTS
'comment'
1101A241
DIAGNOSTIC
;
HELP COSTS
Chapter 5: SQL Data Manipulation LanguageDIAGNOSTIC SET COSTS
SQL Quick Reference 245
DIAGNOSTIC SET COSTS
Syntax (Full)
Syntax (Restricted)
DIAGNOSTIC HELP PROFILE
DIAGNOSTIC SET PROFILE
1101D242
DIAGNOSTIC
;
target_system_nameSET COSTS
,PROFILE profile_nameTPA
ON FOR REQUEST
SESSIONNOT
IFP
SYSTEM
A
A
1101A242
DIAGNOSTIC
;
target_system_nameSET COSTS
TPA
ON FOR REQUEST
SESSIONNOT
IFP
SYSTEM
A
A
;DIAGNOSTIC HELP PROFILE
( (
scope_level
report_option
lower_boundary,upper_boundary 1101C283
;DIAGNOSTIC SET PROFILE ON FOR scope_level
profile_nameNOT
1101B282
Chapter 5: SQL Data Manipulation LanguageDIAGNOSTIC DUMP SAMPLES
246 SQL Quick Reference
DIAGNOSTIC DUMP SAMPLES
DIAGNOSTIC HELP SAMPLES
DIAGNOSTIC SET SAMPLES
General Syntax
Disable All Samples Syntax
GO01A001
;
DIAGNOSTIC
TABLE table_name
DUMP SAMPLES target_system_name
database_name.
A
A
GO01A002
;
DIAGNOSTIC HELP SAMPLES
target_system_name
;
DIAGNOSTIC
SESSION TABLE table_name
SET SAMPLES ON FOR
NOT
target_system_name
database_name.SYSTEM
A
A
GO01A004
GO01A003
;
SESSIONDIAGNOSTIC SET ALL SAMPLES NOT ON FOR
SYSTEM
Chapter 5: SQL Data Manipulation LanguageDIAGNOSTIC “Validate Index”
SQL Quick Reference 247
DIAGNOSTIC “Validate Index”
TW01A002
DIAGNOSTIC "validate index" ON FOR SESSION
NOT ;
Chapter 5: SQL Data Manipulation LanguageDIAGNOSTIC “Validate Index”
248 SQL Quick Reference
SQL Quick Reference 249
CHAPTER 6 SQL Cursor Control
CLOSE
DECLARE CURSOR
Dynamic SQL Form
Macro Form
Request Form
CLOSE
GW01A003
cursor_nameCLOSE
GW01A003
cursor_name
DECLARE
1101A307
cursor_name CURSOR FORSCROLL
statement_name
DECLARE
1101B011
cursor_name CURSOR FOR EXEC
database_name.
macroname
( parameter_list )
A
A
DECLARE
1101B301
cursor_name CURSOR FOR 'request_specification'
Chapter 6: SQL Cursor ControlDELETE
250 SQL Quick Reference
Selection Form
Stored Procedures Form
DELETE
Positioned Form
1101A306
DECLARE cursor_name CURSOR FOR
SCROLL
COMMENT
A
A
EXPLAIN
HELP
SHOW
SELECT
SELECT AND CONSUME
1101B496
NO SCROLL
DECLARE
WITHOUT RETURN
WITH RETURNONLY
cursor_name
SCROLL
CURSOR A
B
FOR
FOR
statement_name
UPDATE
READ ONLY
cursor_specification ;B
A
TO CALLER
CLIENT
GW01A046
table_nameFROM WHERE CURRENT OF cursor_nameDELETE
DEL
Chapter 6: SQL Cursor ControlFETCH
SQL Quick Reference 251
FETCH
Embedded SQL Form
Stored Procedures Form
FETCH
1101A297
cursor_name A
A
INTO host_variable_name
:host_indicator_name:
INDICATOR
,
USING DESCRIPTOR descriptor_area
:
ABSOLUTE n
RELATIVE n
LAST
FIRST
PRIOR
NEXT
1101A074
FETCH
,
local_variable_name
parameter_reference
FROM
INTO
NEXT
FIRST
cursor_name
;
A
A
Chapter 6: SQL Cursor ControlOPEN
252 SQL Quick Reference
OPEN
Embedded SQL Form
Stored Procedures Form
POSITION
PREPARE
OPEN
GW01A027
cursor_name A
A
USING host_variable_name
:host_indicator_name:
INDICATOR
,
USING DESCRIPTOR descriptor_area
:
1101B073
OPEN
USING SQL_identifier
SQL_parameter
cursor_name ;,
POSITION
1101A312
cursor_name
TO NEXT
TO
STATEMENT
statement_number
numeric_variable
:
1101A450
PREPARE FROM 'statement_string'statement_name
statement_string_variable
;
Chapter 6: SQL Cursor ControlREWIND
SQL Quick Reference 253
REWIND
SELECT ... INTO
Stored Procedures Only
Embedded SQL Only
REWIND
GW01A030
cursor_name
select_listSELECT
SEL
INT
ALL
DISTINCT
O
local_variable_name
1101B296
,
parameter_name where_clause
A
A
from_clause
with_[recursive]_modifier
select_list A
A
SELECT
SELwith_[recursive]_modifier
INTO
host_variable_name
1101B305
B
:
,
:host_indicator_name
INDICATOR
B
where_clausefrom_clause
Chapter 6: SQL Cursor ControlSELECT AND CONSUME ... INTO
254 SQL Quick Reference
SELECT AND CONSUME ... INTO
Stored Procedures Only
Embedded SQL Only
UPDATE (Positioned Form)
select_list AAND CONSUME TOP 1SELECT
SEL
INTO
FROM queue_table_namelocal_variable_name
1101A310
A
:
parameter_name
:
,
select_list A
A
AND CONSUME TOP 1SELECT
SEL
INTO
FROM queue_table_name
host_variable_name
1101A304
B
:
,
:host_indicator_name
INDICATOR
B
GW01A047
table_name A
A
alias_name
SET
,
WHERE CURRENT OF cursor_name
UPDATE
UPD
;
column_name = expression
SQL Quick Reference 255
CHAPTER 7 SQL Stored Procedures: ControlStatements and Condition Handling
BEGIN - END Statement
CASE
Syntax 1
Syntax 2
A
label_name : local_declaration
BEGIN
cursor_declaration
YS6BE001
A ;
condition_handler statement
END
label_name
CASE operand_1 AWHEN operand_2 THEN statement
1148A001
A
ELSE
;
statement
END CASE
CASE AWHEN conditional_expression THEN statement
1148A002
A
ELSE
;
statement
END CASE
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingCASE
256 SQL Quick Reference
statement
1101C234
ITERATE
BEGIN REQUEST END REQUESTSQL_multistatement_request
label_name
LEAVE label_name
iteration statement
label_name : label_name
SQL_statement
condition statement
diagnostic statement
compound statement
open statement
fetch statement
assignment statement
compound statement
label_name :local_declaration
BEGIN
cursor_declaration
B
1101A383
END
; label_name
B
condition_handler statement
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingCASE
SQL Quick Reference 257
1101C448
local_declaration
DECLARE
condition_name CONDITION
FOR sqlstate_code
variable_name,
DEFAULT
data_type
literal
;
NULL
cursor_declaration
NO SCROLL
DECLARE
WITH RETURN
ONLY
cursor_name
SCROLL
CURSOR C
D
FOR
FOR
statement_name
UPDATE
READ ONLY
cursor_specificationD E
C
TO CALLER
CLIENT
PREPARE FROM 'statement_string'statement_name
statement_string_variable
E ;
1101B384
cursor_specification
SELECT
*
INNER JOIN ON
OUTERLEFT
RIGHT
FULL
column_namealias_name
expression alias_name
A
A FROM table_name
table_name table_name condition
B,
,
AS
AS
B
WHERE clause other SELECT clauses
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingCASE
258 SQL Quick Reference
condition_handler
DECLARE HANDLER
CONDITION
EXIT
CONTINUE D
FOR
1101A562
;D
SQLEXCEPTION
SQLWARNING
NOT FOUND
,
SQLSTATE,
VALUE
sqlstate_code
condition_name
condition_name
handler_action _statement
SQLSTATE,
VALUE
sqlstate_code
1101A652
OPEN
open statement
USING SQL_identifier
SQL_parameter
cursor_name ;,
1101A653
FETCH
fetch statement
,
local_variable_name
parameter_reference
FROM
INTO
NEXT
FIRST
cursor_name
;
A
A
1101A380
assignment statement
SET assignment_target = assignment_source
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingCASE
SQL Quick Reference 259
1101A381
condition statement
CASE operand_1 JWHEN ;
;
;
;
;
;
operand_2 THEN statement
WHEN conditional_expression THEN statement
J
ELSE
END CASE
statement
IF conditional_expression THEN Gstatement
H
ELSE
END IF
statement
ELSEIF conditional_expression THEN
G H
statement
1101A382
iteration statement
WHILE
;
;
;
;conditional_expression DO END WHILEstatement
LOOP END LOOP
DO
statement
EFOR for_loop_variable AS
CURSOR FORcursor_name
E END FORcursor_specification statement
REPEAT UNTIL conditional_expression END REPEATstatement
1101A616
SIGNAL
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
;
;
condition_name
GET DIAGNOSTICS
EXCEPTION =condition_information_itemcondition_number
parameter_name variable_name
parameter_namevariable_name
diagnostic statement
,
,
RESIGNAL ;
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
=statement_information_item
condition_name
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingDECLARE
260 SQL Quick Reference
DECLARE
FOR
DECLARE variable_name
,
predefined_data_type
UDT_name attribute
A
1101A372
;
DEFAULT literal
NULL
NEW constructor_nameSYSUDTLIB.
A
1148A006
AFOR for_loop_variable AS
DO statement END FORcursor_specification
CURSOR FORcursor_namelabel_name :
label_nameA ;
1101B384
cursor_specification
SELECT
*
INNER JOIN ON
OUTERLEFT
RIGHT
FULL
column_namecorrelation_name
expression correlation_name
A
A FROM table_name
table_name table_name condition
B,
,
AS
AS
B
WHERE clause other SELECT clauses
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingFOR
SQL Quick Reference 261
statement
1101C234
ITERATE
BEGIN REQUEST END REQUESTSQL_multistatement_request
label_name
LEAVE label_name
iteration statement
label_name : label_name
SQL_statement
condition statement
diagnostic statement
compound statement
open statement
fetch statement
assignment statement
compound statement
label_name :local_declaration
BEGIN
cursor_declaration
B
1101A383
END
; label_name
B
condition_handler statement
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingFOR
262 SQL Quick Reference
1101C448
local_declaration
DECLARE
condition_name CONDITION
FOR sqlstate_code
variable_name,
DEFAULT
data_type
literal
;
NULL
cursor_declaration
NO SCROLL
DECLARE
WITH RETURN
ONLY
cursor_name
SCROLL
CURSOR C
D
FOR
FOR
statement_name
UPDATE
READ ONLY
cursor_specificationD E
C
TO CALLER
CLIENT
PREPARE FROM 'statement_string'statement_name
statement_string_variable
E ;
1101B384
cursor_specification
SELECT
*
INNER JOIN ON
OUTERLEFT
RIGHT
FULL
column_namealias_name
expression alias_name
A
A FROM table_name
table_name table_name condition
B,
,
AS
AS
B
WHERE clause other SELECT clauses
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingFOR
SQL Quick Reference 263
condition_handler
DECLARE HANDLER
CONDITION
EXIT
CONTINUE D
FOR
1101A562
;D
SQLEXCEPTION
SQLWARNING
NOT FOUND
,
SQLSTATE,
VALUE
sqlstate_code
condition_name
condition_name
handler_action _statement
SQLSTATE,
VALUE
sqlstate_code
1101A652
OPEN
open statement
USING SQL_identifier
SQL_parameter
cursor_name ;,
1101A653
FETCH
fetch statement
,
local_variable_name
parameter_reference
FROM
INTO
NEXT
FIRST
cursor_name
;
A
A
1101A380
assignment statement
SET assignment_target = assignment_source
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingFOR
264 SQL Quick Reference
1101A381
condition statement
CASE operand_1 JWHEN ;
;
;
;
;
;
operand_2 THEN statement
WHEN conditional_expression THEN statement
J
ELSE
END CASE
statement
IF conditional_expression THEN Gstatement
H
ELSE
END IF
statement
ELSEIF conditional_expression THEN
G H
statement
1101A382
iteration statement
WHILE
;
;
;
;conditional_expression DO END WHILEstatement
LOOP END LOOP
DO
statement
EFOR for_loop_variable AS
CURSOR FORcursor_name
E END FORcursor_specification statement
REPEAT UNTIL conditional_expression END REPEATstatement
1101A616
SIGNAL
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
;
;
condition_name
GET DIAGNOSTICS
EXCEPTION =condition_information_itemcondition_number
parameter_name variable_name
parameter_namevariable_name
diagnostic statement
,
,
RESIGNAL ;
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
=statement_information_item
condition_name
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingIF
SQL Quick Reference 265
IF
IF conditional_expression THEN statement A
A
ELSEIF conditional_expression THEN statement
B
B
ELSE statement
END IF
1148A007
;
statement
1101C234
ITERATE
BEGIN REQUEST END REQUESTSQL_multistatement_request
label_name
LEAVE label_name
iteration statement
label_name : label_name
SQL_statement
condition statement
diagnostic statement
compound statement
open statement
fetch statement
assignment statement
compound statement
label_name :local_declaration
BEGIN
cursor_declaration
B
1101A383
END
; label_name
B
condition_handler statement
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingIF
266 SQL Quick Reference
1101C448
local_declaration
DECLARE
condition_name CONDITION
FOR sqlstate_code
variable_name,
DEFAULT
data_type
literal
;
NULL
cursor_declaration
NO SCROLL
DECLARE
WITH RETURN
ONLY
cursor_name
SCROLL
CURSOR C
D
FOR
FOR
statement_name
UPDATE
READ ONLY
cursor_specificationD E
C
TO CALLER
CLIENT
PREPARE FROM 'statement_string'statement_name
statement_string_variable
E ;
1101B384
cursor_specification
SELECT
*
INNER JOIN ON
OUTERLEFT
RIGHT
FULL
column_namealias_name
expression alias_name
A
A FROM table_name
table_name table_name condition
B,
,
AS
AS
B
WHERE clause other SELECT clauses
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingIF
SQL Quick Reference 267
condition_handler
DECLARE HANDLER
CONDITION
EXIT
CONTINUE D
FOR
1101A562
;D
SQLEXCEPTION
SQLWARNING
NOT FOUND
,
SQLSTATE,
VALUE
sqlstate_code
condition_name
condition_name
handler_action _statement
SQLSTATE,
VALUE
sqlstate_code
1101A652
OPEN
open statement
USING SQL_identifier
SQL_parameter
cursor_name ;,
1101A653
FETCH
fetch statement
,
local_variable_name
parameter_reference
FROM
INTO
NEXT
FIRST
cursor_name
;
A
A
1101A380
assignment statement
SET assignment_target = assignment_source
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingIF
268 SQL Quick Reference
1101A381
condition statement
CASE operand_1 JWHEN ;
;
;
;
;
;
operand_2 THEN statement
WHEN conditional_expression THEN statement
J
ELSE
END CASE
statement
IF conditional_expression THEN Gstatement
H
ELSE
END IF
statement
ELSEIF conditional_expression THEN
G H
statement
1101A382
iteration statement
WHILE
;
;
;
;conditional_expression DO END WHILEstatement
LOOP END LOOP
DO
statement
EFOR for_loop_variable AS
CURSOR FORcursor_name
E END FORcursor_specification statement
REPEAT UNTIL conditional_expression END REPEATstatement
1101A616
SIGNAL
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
;
;
condition_name
GET DIAGNOSTICS
EXCEPTION =condition_information_itemcondition_number
parameter_name variable_name
parameter_namevariable_name
diagnostic statement
,
,
RESIGNAL ;
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
=statement_information_item
condition_name
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingITERATE
SQL Quick Reference 269
ITERATE
LEAVE
LOOP
ITERATE label_name ;
YS6ITER01
label_nameLEAVE ;
YS6LEA01
LOOP statement END LOOP
label_name : label_name
;
1148A008
statement
1101C234
ITERATE
BEGIN REQUEST END REQUESTSQL_multistatement_request
label_name
LEAVE label_name
iteration statement
label_name : label_name
SQL_statement
condition statement
diagnostic statement
compound statement
open statement
fetch statement
assignment statement
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingLOOP
270 SQL Quick Reference
compound statement
label_name :local_declaration
BEGIN
cursor_declaration
B
1101A383
END
; label_name
B
condition_handler statement
1101C448
local_declaration
DECLARE
condition_name CONDITION
FOR sqlstate_code
variable_name,
DEFAULT
data_type
literal
;
NULL
cursor_declaration
NO SCROLL
DECLARE
WITH RETURN
ONLY
cursor_name
SCROLL
CURSOR C
D
FOR
FOR
statement_name
UPDATE
READ ONLY
cursor_specificationD E
C
TO CALLER
CLIENT
PREPARE FROM 'statement_string'statement_name
statement_string_variable
E ;
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingLOOP
SQL Quick Reference 271
1101B384
cursor_specification
SELECT
*
INNER JOIN ON
OUTERLEFT
RIGHT
FULL
column_namealias_name
expression alias_name
A
A FROM table_name
table_name table_name condition
B,
,
AS
AS
B
WHERE clause other SELECT clauses
condition_handler
DECLARE HANDLER
CONDITION
EXIT
CONTINUE D
FOR
1101A562
;D
SQLEXCEPTION
SQLWARNING
NOT FOUND
,
SQLSTATE,
VALUE
sqlstate_code
condition_name
condition_name
handler_action _statement
SQLSTATE,
VALUE
sqlstate_code
1101A652
OPEN
open statement
USING SQL_identifier
SQL_parameter
cursor_name ;,
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingLOOP
272 SQL Quick Reference
1101A653
FETCH
fetch statement
,
local_variable_name
parameter_reference
FROM
INTO
NEXT
FIRST
cursor_name
;
A
A
1101A380
assignment statement
SET assignment_target = assignment_source
1101A381
condition statement
CASE operand_1 JWHEN ;
;
;
;
;
;
operand_2 THEN statement
WHEN conditional_expression THEN statement
J
ELSE
END CASE
statement
IF conditional_expression THEN Gstatement
H
ELSE
END IF
statement
ELSEIF conditional_expression THEN
G H
statement
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingREPEAT
SQL Quick Reference 273
REPEAT
1101A382
iteration statement
WHILE
;
;
;
;conditional_expression DO END WHILEstatement
LOOP END LOOP
DO
statement
EFOR for_loop_variable AS
CURSOR FORcursor_name
E END FORcursor_specification statement
REPEAT UNTIL conditional_expression END REPEATstatement
1101A616
SIGNAL
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
;
;
condition_name
GET DIAGNOSTICS
EXCEPTION =condition_information_itemcondition_number
parameter_name variable_name
parameter_namevariable_name
diagnostic statement
,
,
RESIGNAL ;
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
=statement_information_item
condition_name
YS6RPT01
REPEAT UNTILstatement
label_name :
A
conditional_expression ;END REPEAT
label_name
A
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingSET
274 SQL Quick Reference
SET
WHILE
YS6SET01
SET assignment_target = assignment_source ;
1148B009
WHILE conditional_expression DO
statement END WHILE
label_name :
A
A
label_name
;
statement
1101C234
ITERATE
BEGIN REQUEST END REQUESTSQL_multistatement_request
label_name
LEAVE label_name
iteration statement
label_name : label_name
SQL_statement
condition statement
diagnostic statement
compound statement
open statement
fetch statement
assignment statement
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingWHILE
SQL Quick Reference 275
compound statement
label_name :local_declaration
BEGIN
cursor_declaration
B
1101A383
END
; label_name
B
condition_handler statement
1101C448
local_declaration
DECLARE
condition_name CONDITION
FOR sqlstate_code
variable_name,
DEFAULT
data_type
literal
;
NULL
cursor_declaration
NO SCROLL
DECLARE
WITH RETURN
ONLY
cursor_name
SCROLL
CURSOR C
D
FOR
FOR
statement_name
UPDATE
READ ONLY
cursor_specificationD E
C
TO CALLER
CLIENT
PREPARE FROM 'statement_string'statement_name
statement_string_variable
E ;
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingWHILE
276 SQL Quick Reference
1101B384
cursor_specification
SELECT
*
INNER JOIN ON
OUTERLEFT
RIGHT
FULL
column_namealias_name
expression alias_name
A
A FROM table_name
table_name table_name condition
B,
,
AS
AS
B
WHERE clause other SELECT clauses
condition_handler
DECLARE HANDLER
CONDITION
EXIT
CONTINUE D
FOR
1101A562
;D
SQLEXCEPTION
SQLWARNING
NOT FOUND
,
SQLSTATE,
VALUE
sqlstate_code
condition_name
condition_name
handler_action _statement
SQLSTATE,
VALUE
sqlstate_code
1101A652
OPEN
open statement
USING SQL_identifier
SQL_parameter
cursor_name ;,
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingWHILE
SQL Quick Reference 277
1101A653
FETCH
fetch statement
,
local_variable_name
parameter_reference
FROM
INTO
NEXT
FIRST
cursor_name
;
A
A
1101A380
assignment statement
SET assignment_target = assignment_source
1101A381
condition statement
CASE operand_1 JWHEN ;
;
;
;
;
;
operand_2 THEN statement
WHEN conditional_expression THEN statement
J
ELSE
END CASE
statement
IF conditional_expression THEN Gstatement
H
ELSE
END IF
statement
ELSEIF conditional_expression THEN
G H
statement
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingDECLARE CONDITION
278 SQL Quick Reference
DECLARE CONDITION
1101A382
iteration statement
WHILE
;
;
;
;conditional_expression DO END WHILEstatement
LOOP END LOOP
DO
statement
EFOR for_loop_variable AS
CURSOR FORcursor_name
E END FORcursor_specification statement
REPEAT UNTIL conditional_expression END REPEATstatement
1101A616
SIGNAL
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
;
;
condition_name
GET DIAGNOSTICS
EXCEPTION =condition_information_itemcondition_number
parameter_name variable_name
parameter_namevariable_name
diagnostic statement
,
,
RESIGNAL ;
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
=statement_information_item
condition_name
1101A561
;DECLARE CONDITION
FOR SQLSTATE sqlstate_code
VALUE
condition_name
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingDECLARE HANDLER (Basic Syntax)
SQL Quick Reference 279
DECLARE HANDLER (Basic Syntax)
SIGNAL
RESIGNAL
DECLARE HANDLER
EXIT
CONTINUE AFOR
1101A563
;A
SQLEXCEPTION
SQLWARNING
NOT FOUND
,
SQLSTATE,
VALUE
sqlstate_code
condition_name
handler_action _statement
condition_handler
1101A634
SIGNAL
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
;condition_name
1101A635
RESIGNAL ;
SQLSTATE SET condition_information_item=valueSQLSTATE_codeVALUE
condition_name
Chapter 7: SQL Stored Procedures: Control Statements and Condition HandlingGET DIAGNOSTICS
280 SQL Quick Reference
GET DIAGNOSTICS
1101A636
;GET DIAGNOSTICS
EXCEPTION =condition_information_itemcondition_number
parameter_name variable_name
parameter_namevariable_name
,
,
=statement_information_item
SQL Quick Reference 281
CHAPTER 8 Static Embedded SQL Statements
BEGIN DECLARE SECTION
COMMENT
Returning Form
DATABASE
BEGIN DECLARE SECTION
GW01A001
COMMENT object_kind object_reference
ON
INTO
host_variable_name
A
:
A
1101B015
B
B
:host_indicator_name
INDICATOR
DATABASE
1101B016
database_name
:database_name_variable
Chapter 8: Static Embedded SQL StatementsDECLARE STATEMENT
282 SQL Quick Reference
DECLARE STATEMENT
DECLARE TABLE
END DECLARE SECTION
END-EXEC Statement Terminator
DECLARE
GW01A013
STATEMENTstatement_name
,
DECLARE
GW01R014
table_name TABLE A
A column_name
view_name
( )
null_attribute
,
data_type
END DECLARE SECTION
GW01A016
FF07D287
END-EXEC.
Chapter 8: Static Embedded SQL StatementsEXEC
SQL Quick Reference 283
EXEC
EXEC SQL Statement Prefix
INCLUDE
INCLUDE SQLCA
EXEC
1101B043
macro_name
(parameter_list )
1101A396
EXEC SQL
:FOR count_value
sql_statement_terminatorembedded_sql_statement
INCLUDE
1101B044
include_file_name
INCLUDE SQLCA
GW01A021
Chapter 8: Static Embedded SQL StatementsINCLUDE SQLDA
284 SQL Quick Reference
INCLUDE SQLDA
WHENEVER
INCLUDE SQLDA
GW01A022
WHENEVER
GW01R035
actioncondition
SQL Quick Reference 285
CHAPTER 9 Dynamic Embedded SQLStatements
DESCRIBE
EXECUTE
Dynamic SQL Form
DESCRIBE
1101B017
statement_name INTO A
A
:
descriptor_area
USING
B
NAMES
ANY
BOTH
LABELS
FOR STATEMENT statement_number
statement_number_variable
:
B
EXECUTE
GW01A017
statement_name A
A
USING host_variable_name
: :host_indicator_name
INDICATOR
,
USING DESCRIPTOR descriptor_area
:
Chapter 9: Dynamic Embedded SQL StatementsEXECUTE IMMEDIATE
286 SQL Quick Reference
EXECUTE IMMEDIATE
PREPARE
EXECUTE IMMEDIATE
1101B018
statement_string
statement_string_variable
:
PREPARE
1101B029
statement_name
B
USING NAMES
ANY
BOTH
LABELS
FOR STATEMENT statement_number
numeric_variable
:
B
descriptor_areaINTO:
FROM statement_string
statement_string_variable
:
A2
A1
A2
A1
SQL Quick Reference 287
CHAPTER 10 SQL Client-Server ConnectivityStatements
CONNECT
GET CRASH
LOGOFF
CONNECT user_id_variable IDENTIFIED BY password_variable
: :
1101B019
AS
A
A
connection_name
: connection_name_variable
GET CRASH
1101B058
WAIT, TELL wait_variable,INTO
:tell_variable,
:
LOGOFF
1101B020
CURRENT
:connection_name_variable
ALL
connection_name
Chapter 10: SQL Client-Server Connectivity StatementsLOGON
288 SQL Quick Reference
LOGON
SET BUFFERSIZE
SET CHARSET
SET CONNECTION
SET CRASH
LOGON
:
1101B027
AS connection_name
: connection_name_variable
logon_string
SET BUFFERSIZE
GW01A032
size
SET CHARSET
1101B033
character_set_name:character_set_name_variable
SET CONNECTION
1101B034
connection_name
:connection_name_variable
SET CRASH
GW01A057
NOWAIT_TELL
WAIT_NOTELL
Chapter 10: SQL Client-Server Connectivity StatementsSET ENCRYPTION
SQL Quick Reference 289
SET ENCRYPTION
1101A298
SET ENCRYPTION ON
OFF
Chapter 10: SQL Client-Server Connectivity StatementsSET ENCRYPTION
290 SQL Quick Reference
SQL Quick Reference 291
CHAPTER 11 Multisession AsynchronousProgramming With Embedded SQL
ASYNC Statement Modifier
TEST
WAIT
1101C114
ASYNC ( )async_statement_identifier async_SQL_statement
:async_statement_identifier_variable_name
TEST
1101B045
async_statement_identifier COMPLETION
:async_statement_identifier_variable_name
WAIT
1101B009
async_statement_identifier
:async_statement_identifier_variable_name
COMPLETION
ALL
,
ANY COMPLETION INTO
:
statement_variable
:
session_variable
Chapter 11: Multisession Asynchronous Programming With Embedded SQLWAIT
292 SQL Quick Reference
SQL Quick Reference 293
APPENDIX A How to Read Syntax Diagrams
This appendix describes the conventions that apply to reading the syntax diagrams used in this book.
Syntax Diagram Conventions
Notation Conventions
Paths
The main path along the syntax diagram begins at the left with a keyword, and proceeds, left to right, to the vertical bar, which marks the end of the diagram. Paths that do not have an arrow or a vertical bar only show portions of the syntax.
The only part of a path that reads from right to left is a loop.
Item Definition / Comments
Letter An uppercase or lowercase alphabetic character ranging from A through Z.
Number A digit ranging from 0 through 9.
Do not use commas when typing a number with more than 3 digits.
Word Keywords and variables.
• UPPERCASE LETTERS represent a keyword.
Syntax diagrams show all keywords in uppercase, unless operating system restrictions require them to be in lowercase.
• lowercase letters represent a keyword that you must type in lowercase, such as a UNIX command.
• lowercase italic letters represent a variable such as a column or table name.
Substitute the variable with a proper value.
• lowercase bold letters represent an excerpt from the diagram. The excerpt is defined immediately following the diagram that contains it.
• UNDERLINED LETTERS represent the default value.
This applies to both uppercase and lowercase words.
Spaces Use one space between items such as keywords or variables.
Punctuation Type all punctuation exactly as it appears in the diagram.
Appendix A: How to Read Syntax DiagramsSyntax Diagram Conventions
294 SQL Quick Reference
Continuation Links
Paths that are too long for one line use continuation links. Continuation links are circled letters indicating the beginning and end of a link:
When you see a circled letter in a syntax diagram, go to the corresponding circled letter and continue reading.
Required Entries
Required entries appear on the main path:
If you can choose from more than one entry, the choices appear vertically, in a stack. The first entry appears on the main path:
Optional Entries
You may choose to include or disregard optional entries. Optional entries appear below the main path:
If you can optionally choose from more than one entry, all the choices appear below the main path:
FE0CA002
A
A
FE0CA003
SHOW
FE0CA005
SHOW
VERSIONS
CONTROLS
FE0CA004
SHOW
CONTROLS
Appendix A: How to Read Syntax DiagramsSyntax Diagram Conventions
SQL Quick Reference 295
Some commands and statements treat one of the optional choices as a default value. This value is UNDERLINED. It is presumed to be selected if you type the command or statement without specifying one of the options.
Strings
String literals appear in single quotes:
Abbreviations
If a keyword or a reserved word has a valid abbreviation, the unabbreviated form always appears on the main path. The shortest valid abbreviation appears beneath.
In the above syntax, the following formats are valid:
• SHOW CONTROLS
• SHOW CONTROL
Loops
A loop is an entry or a group of entries that you can repeat one or more times. Syntax diagrams show loops as a return path above the main path, over the item or items that you can repeat:
Read loops from right to left.
The following conventions apply to loops:
JC01A010SHARE
READ
ACCESS
JC01A004
'msgtext '
FE0CA042
SHOW
CONTROL
CONTROLS
JC01B012
(
, 4
cname )
, 3
Appendix A: How to Read Syntax DiagramsSyntax Diagram Conventions
296 SQL Quick Reference
Excerpts
Sometimes a piece of a syntax phrase is too large to fit into the diagram. Such a phrase is indicated by a break in the path, marked by (|) terminators on each side of the break. The name for the excerpted piece appears between the terminators in boldface type.
The boldface excerpt name and the excerpted phrase appears immediately after the main diagram. The excerpted phrase starts and ends with a plain horizontal line:
Multiple Legitimate Phrases
In a syntax diagram, it is possible for any number of phrases to be legitimate:
IF... THEN...
there is a maximum number of entries allowed
the number appears in a circle on the return path.
In the example, you may type cname a maximum of 4 times.
there is a minimum number of entries required
the number appears in a square on the return path.
In the example, you must type at least three groups of column names.
a separator character is required between entries
the character appears on the return path.
If the diagram does not show a separator character, use one blank space.
In the example, the separator character is a comma.
a delimiter character is required around entries
the beginning and end characters appear outside the return path.
Generally, a space is not needed between delimiter characters and entries.
In the example, the delimiter characters are the left and right parentheses.
LOCKING excerpt
where_cond
A
cname
excerpt
JC01A014
A
HAVING con
,
col_pos
,
Appendix A: How to Read Syntax DiagramsSyntax Diagram Conventions
SQL Quick Reference 297
In this example, any of the following phrases are legitimate:
• dbname
• DATABASE dbname
• tname
• TABLE tname
• vname
• VIEW vname
Sample Syntax Diagram
JC01A016
DATABASE
dbname
TABLE
tname
VIEW
vname
JC01A018
viewnameCREATE VIEW AS
cname
A
C
CV
,
LOCKING
LOCK
ACCESSA
DATABASE
dbname
TABLE
tname
VIEW
vname
FOR
IN
B
SHARE
READ
WRITE
EXCLUSIVE
EXCL
MODE
FROMB SEL C
.aname
expr
,
tname
,
qual_cond
qual_cond
WHERE cond
cname
,
col_pos
,GROUP BY
HAVING cond ;
Appendix A: How to Read Syntax DiagramsSyntax Diagram Conventions
298 SQL Quick Reference
Diagram Identifier
The alphanumeric string that appears in the lower right corner of every diagram is an internal identifier used to catalog the diagram. The text never refers to this string.