SET SESSION DATEFORM Syntax Elements - Teradata VantageCloud Lake
Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530
Working with SQL
SQL Reference
SQL Request and Transaction Processing
Request Parsing
SQL Parser
Dictionary Cache
Statistics Cache
Object Use Count Cache
Request Cache
Immediate Caching and Non-Immediate Caching
Clearing the Request Cache
Parameterized Requests
Dynamically Parameterized Requests
Syntaxer
Resolver
Security Checking
Query Rewrites
Optimizer
Generator
OptApply
Dispatcher
Query Rewrite, Statistics, and Optimization
Query Rewrite
Examples: Query Rewrites
Converting ANSI Join Syntax to Comma Join Syntax
Projection Pushdown
IN-List Rewrite
Outer Join-to-Inner Join Conversion
View Folding
Predicate Simplification
Predicate Pushdown and Pullup
Eliminating Set Operation Branches
Eliminating Redundant Joins
Pushing Joins into UNION ALL Branches
Other Query Rewrites
Predicate Marshaling
View Materialization and Other Database Object Substitutions
Query Optimizers
Teradata Optimizer Processes
Incremental Planning and Execution
Translation to Internal Representation
Optimizer Statistics and Demographics
How the AMP Software Collects Statistics
Interval Histograms
Sampled Statistics
Dynamic AMP Sampling
Comparing Accuracies of Methods of Collecting Statistics
Optimal Times to Collect or Recollect Statistics
Optimizer Use of Statistical Profiles
Using Interval Histograms to Make Initial Cardinality Estimates
Determining the Reliability of Statistics from History Intervals
Derived Statistics
Statistical Inheritance by Join Indexes
Deriving Column Demographics
Using Join Index Statistics to Estimate Single-Table Expression Cardinalities
Using Unique Join Index in Access Path for Query
Estimating Join Cardinality with Single-Row Unique Index Access to One of the Tables
Stale Statistics
Object Use and UDI Counts
Optimizing the Recollection of Statistics
Using Extrapolation to Replace Stale Statistics
Cost-Based Optimization
Environmental Cost Factors
Row Partitioning
Row Partition Elimination
Static Row Partition Elimination
Examples: Rewrites Using Row Partitioning
Delayed Row Partition Elimination
Dynamic Row Partition Elimination
Single Partition Scans and BEGIN/END Bound Functions
Column Partitioning
Column Partition Elimination
Access Planning for Column-Partitioned Objects
Join Planning and Optimization
Optimizer Join Plans
Join Geography
Determining the Order of Joins
Partial GROUP BY Block Optimization
Join Strategies and Methods
Product Join
Merge Join
Direct Row-partitioned PI Merge Join
Rowkey-Based Merge Join
Single-Window Merge Join
Sliding-Window Merge Join
Hash Join
Nested Join
Local Nested Join
Slow Path Local Nested Join
Fast Path Local Nested Join
Remote Nested Join
Nested Join Examples
Join Plan without Nested Join
Join Plan with Nested Join
Exclusion Join
Exclusion Merge Join
Exclusion Product Join
Inclusion Join
Inclusion and Exclusion Product Joins with Dynamic Row Partition Elimination
RowID Join
Correlated Joins
Self-Join
Join Optimizations
Large Table/Small Table Joins
Star and Snowflake Join Optimization
LT/ST-J1 Indexed Joins
LT-ST-J2 Unindexed Joins
n-Way Join Optimization
Miscellaneous Considerations for Star Join Optimization
Selecting Indexes for Star Joins
Star Join Examples
Cardinality and Uniqueness Statistics for the Reasonable Indexed Join Examples
Reasonable Indexed Join Plan without Star Join Optimization
Reasonable Indexed Join Plan with Star Join Optimization
Reasonable Indexed Join Plan with Star Join Optimization and a Fact Table USI
Reasonable Indexed Join Plan with Star Join Optimization and a Fact Table NUSI
Join Plan with Star Join Optimization and Fact Table Subquery Join
Cardinality and Uniqueness Statistics for the Reasonable Unindexed Join Examples
Reasonable Unindexed Join without Join Optimization
Reasonable Unindexed Join with Join Optimization
n-Way Join Example
Optimization Using Join Indexes
Join Indexes
Maintaining a Join Index for DELETE, INSERT, and UPDATE Operations
General Method of Maintaining a Join Index during Simple DELETE Operations
General Methods of Maintaining a Join Index during Joined DELETE Operations
Optimized Method of Maintaining a Join Index during DELETE Operations
General Method of Maintaining a Join Index during INSERT Operations
General Method of Maintaining a Join Index during UPDATE Operations
Optimized Method of Maintaining a Join Index during UPDATE Operations
Interpreting EXPLAIN Output
EXPLAIN Request Modifier
EXPLAIN Confidence Levels
EXPLAIN Request Modifier Phrase Terminology
EXPLAIN Request Modifier: Examples
EXPLAIN Request Modifier and Standard Indexed Access
EXPLAIN Request Modifier and Join Processing
EXPLAIN Request Modifier and Mapping Join Indexes
EXPLAIN Request Modifier and Parallel Steps
EXPLAIN Request Modifier and Pipelined Steps
EXPLAIN Request Modifier and Partitioned Primary Index Access
EXPLAIN Request Modifier and Column-Partition Access
EXPLAIN Request Modifier and MERGE Conditional Steps
EXPLAIN and UPDATE (Upsert Form) Conditional Steps
EXPLAIN Request Modifier and Triggers
EXPLAIN Request Modifier and Recursion
STATIC EXPLAIN
DYNAMIC EXPLAIN
EXPLAIN Output for Compute Clusters
Accessing Objects in External Object Storage Using Foreign Table
Accessing Objects in Object File System Table
Terminology in EXPLAIN Text for Compute Clusters
Using Explain to Access a NOS Table in Primary Cluster
Using Explain to Access a NOS Table in a Compute Cluster
Using Explain to Access an Object File System Table from the Compute Cluster
Terminology in EXPLAIN Text for the Object File System
Transaction Processing
Database Transactions
Transactions, Requests, and Statements
Transaction Semantics Differences in ANSI and Teradata Session Modes
Comparison of Transactions in ANSI and Teradata Session Modes
ANSI Session Mode Transaction Processing Case Studies
Teradata Session Mode Transaction Processing Case Studies
Rollback Processing
Database Locks, Two-Phase Locking, and Serializability
Lock Manager
Database Locking Levels and Severities
Client Utility Locks
Default Lock Assignments and Lock Upgradeability
Blocked Requests
Proxy Locks
Pseudo Table Locks
Deadlock
Minimizing Deadlock
Example: Transaction without Deadlock
Example: Transaction with Deadlock
Example: Two Serial Transactions
DDL and DCL Requests, Dictionary Access, and Locks
DML Requests and Locks
Locking Issues with Consume Mode SELECT Queries on a Queue Table
Cursor Locking Modes
Locking Issues with Tactical Queries
Query Capture Facility
Quick Functional Overview of the Query Capture Facility
QCD Table Definitions
AnalysisLog
AnalysisStmts
DataDemographics
Field
Index_Field
IndexColumns
IndexMaintenance
IndexRecommendations
IndexTable
JoinIndexColumns
Partition Recommendations
Predicate
Predicate_Field
QryRelX
Query
QueryBandTbl
QuerySteps
RangePartExpr
Relation
SeqNumber
SingleRowRelation
StatsRecs
TableStatistics
User_Database
ViewTable
Workload
WorkloadQueries
WorkloadStatus
XMLQCD
XML Documents Produced by the Query Logging XMLPLAN Option
XML Schema Used for the XMLPLAN Option
Example XML Optimizer Query Plan Document Produced by the XMLPLAN Option
Examples: Processing XML Documents Produced by the XMLPLAN Option
SQL Fundamentals
Database Objects
Databases and Users
Tables
Defining Tables
Defining Indexes for a Table
Duplicate Rows in Tables
Temporary Tables
Global Temporary Tables
Volatile Tables
Queue Tables
Error Logging Tables
No Primary Index (NoPI) Tables
Temporal Tables
Tables Related Information
Columns
Defining Columns
System-Derived and System-Generated Columns
Data Types
Keys
Keys and Referential Integrity
Effect on Row Distribution
Primary Keys and Primary Indexes
Indexes
Classic Indexes and Teradata Indexes
Selectivity of Indexes
Row Hash and RowID
Index Hash Mapping
Advantages of Indexes
Disadvantages of Indexes
Teradata Index Types
Unique Indexes
Nonunique Indexes
Partitioned and Nonpartitioned Primary Indexes
Join Indexes
Creating Indexes for a Table
Determining the Usefulness of Indexes
Primary Indexes
Primary Index Assignment
Uniform Distribution of Data and Optimal Access Considerations
Unique and Nonunique Primary Index Considerations
Partitioning Considerations
Primary Index Restrictions
Primary Index Properties
Primary Indexes Related Information
Secondary Indexes
Unique and Nonunique Secondary Indexes
NUSIs and Covering
Value-Ordered NUSIs
Multiple Secondary Indexes and Composites
NUSI Bit Mapping
Secondary Index Properties
USI and NUSI Properties
Join Indexes
Defining Join Indexes
Multitable Join Indexes
Single-Table Join Indexes
Aggregate Join Indexes
Sparse Join Indexes
Effects of Join Indexes
Join Indexes and Base Tables
Mapping Join Indexes
Referential Integrity
Referential Integrity Enforcement
Referencing (Child) Table
Referenced (Parent) Table
Terminology
Why Referential Integrity Is Important
Rules for Assigning Columns as FOREIGN KEYS
Circular References
References to the Table Itself
CREATE TABLE and ALTER TABLE Syntax
Maintaining Foreign Keys
Referential Integrity and the FastLoad and MultiLoad Utilities
Views
Triggers
Process Flow
Trigger Restrictions
Archiving Triggers
Macros
Single-User and Multiuser Macros
Contents of a Macro
Running a Macro
Ways to Run SQL Macros in Embedded SQL
Static SQL Macro Execution in Embedded SQL
Dynamic SQL Macro Execution in Embedded SQL
Dropping, Replacing, Renaming, and Retrieving Macro Information
Stored Procedures
Elements of Stored Procedures
Creating Stored Procedures
Modifying Stored Procedures
Running Stored Procedures
Output from Stored Procedures
Recompiling Stored Procedures
Deleting, Renaming, and Retrieving Stored Procedure Information
Archiving Procedures
External Stored Procedures
Running SQL from External Stored Procedures
Differences between Stored Procedures and External Stored Procedures
User-Defined Functions
SQL UDFs
External UDFs
UDFs Usage Notes
Profiles
Advantages of Using Profiles
Profiles Usage Notes
Roles
Advantages of Using Roles
Roles Usage Notes
Basic SQL Syntax
SQL Statement Structure
Keywords
Character Sets
Object Names
Working with Unicode Delimited Identifiers
Hexadecimal Representation of Object Names
Hexadecimal Name Literals
Referencing Object Names in a Request
Fully Qualified Object Names
Unqualified Object Names
Using a Column Alias
Referencing All Columns in a Table
Expressions
Literals
Functions
Operators
Separators
Delimiters
Comments
Terminators
Default Database
Null Statements
NULL Keyword as a Literal
SQL Data Definition, Control, and Manipulation
SQL Functional Families and Binding Styles
Data Definition Language
Altering Table Structure and Definition
Dropping and Renaming Objects
Data Control Language
Data Manipulation Language
Subqueries
Recursive Queries
Query and Workload Analysis Statements
Help and Database Object Definition Tools
SQL Data Handling
Invoking SQL Statements
Transactions
Transaction Processing in Teradata Session Mode
Transaction Processing in ANSI Session Mode
Multiple-Statement Requests
Iterated Requests
Aborting SQL Requests
Dynamic and Static SQL
Dynamic SQL in Stored Procedures
Using SELECT with Dynamic SQL
Event Processing Using Queue Tables
Manipulating Nulls
Session Parameters
Session Management
Return Codes
Statement Responses
Success Response
Warning Response (ANSI Session Mode Only)
Error Response (ANSI Session Mode Only)
Failure Response (ANSI Session Mode Only)
Query Processing
Queries and AMPs
Table Access
Full-Table Scans
Collecting Statistics
Restricted Words
SQLRestrictedWords View
SQLRestrictedWords_TBF Function
ANSI/ISO SQL Compliance
Terminology Differences between ANSI/ISO SQL and Teradata SQL
Using the SQL Flagger
SQL Statements and SQL Requests
Performance Considerations
Using 2PC Protocol
System Validated Object Names
Names Subject to Object Naming Rules
Names Not Subject to Object Naming Rules
SQL Stored Procedures and Embedded SQL
Stored Procedure and Embedded SQL Overview
Stored Procedure Overview
Embedded SQL Overview
SQL Cursors
Why Cursors Are Necessary
Types of Cursors
Cursor States and Positions
How Cursors Are Incremented
Cursors and Stored Procedures
Cursors and Embedded SQL
Transactions and Cursors
Positioned Cursors
SQL Cursor Control and DML Statements
ALLOCATE
CLOSE
DEALLOCATE PREPARE
DECLARE CURSOR
DECLARE CURSOR Usage Notes (All Forms)
DECLARE CURSOR (Dynamic SQL Form)
DECLARE CURSOR (Macro Form)
DECLARE CURSOR (Request Form)
DECLARE CURSOR (Selection Form)
DECLARE CURSOR (Stored Procedures Form)
DELETE (Positioned Form)
EXECUTE
EXECUTE IMMEDIATE for Statement in Stored Procedure
FETCH (Embedded SQL Form)
FETCH (Stored Procedures Form)
OPEN (Embedded SQL Form)
OPEN (Stored Procedures Form)
POSITION
PREPARE
REWIND
SELECT AND CONSUME ... INTO
SELECT ... INTO
UPDATE (Positioned Form)
Result Code Variables
SQLSTATE Result Code Variable
SQLCODE Result Code Variable
ACTIVITY_COUNT Result Code Variable
Result Code Variables in Stored Procedures
SQL Stored Procedures
Granting Privileges on Stored Procedures
Checking Privileges for Stored Procedures
Rules for Using SQL Statements in Stored Procedures
Running a Stored Procedure
Recompiling a Stored Procedure
Restrictions on Stored Procedures
Stored Procedure Lexicon
DDL Statements in Stored Procedures
DML Statements in Stored Procedures
DCL Statements in Stored Procedures
Diagnostics Statements in Stored Procedures
SQL Operations on Stored Procedures
Control Statements in Stored Procedures
Completion, Exception, and User-defined Condition Handlers
Cursor Declarations
Using Dynamic SQL in Stored Procedures
Recursive Stored Procedures
Stored Procedures and Tactical Queries
Debugging Stored Procedures
Sample Stored Procedure
Condition Handling
Benefits of Condition Handling
Condition Handling Terms
Role of SQLSTATE in Condition Handling
Role of Diagnostics Area in Condition Handling
Conditions and Condition Handlers
Condition Handler Types
Raising Conditions
Condition Handler Rules
Rules for Condition Handlers in Nested Compound Statements
Status Variable Values
Precedence of Specific Condition Handlers
Exception Condition Transaction Semantics
Conditions Raised by a Handler Action
Rules for Reporting Handler Action-Raised Conditions
DECLARE CONDITION
DECLARE HANDLER (Basic Syntax)
DECLARE HANDLER (CONTINUE Type)
DECLARE HANDLER (EXIT Type)
DECLARE HANDLER (SQLEXCEPTION Type)
DECLARE HANDLER (SQLWARNING Type)
DECLARE HANDLER (NOT FOUND Type)
Diagnostics Area
Diagnostic Statements
SIGNAL
RESIGNAL
GET DIAGNOSTICS
Host Variables and Multiple-Statement Requests
Host Structures
Host Variables
Input Host Variables
Output Host Variables
SQL Character Strings as Host Variables
Indicator Variables
Multiple-Statement Requests with Embedded SQL
SQL Control Statements
BEGIN END
BEGIN END Syntax
BEGIN END Usage Notes
BEGIN END Related Information
CASE
Simple CASE Syntax
Searched CASE Syntax
CASE Syntax Elements
CASE Usage Notes
Simple CASE Statement
Searched CASE Statement
Semantic Differences between CASE Statement and CASE Expression
CASE Statement or IF-THEN-ELSEIF-ELSE Statement
Exception Handling in CASE Statements
DECLARE
DECLARE Syntax
DECLARE Usage Notes
DECLARE Examples
FOR
FOR Syntax
FOR Syntax Elements
FOR Usage Notes
DECLARE CURSOR and FOR Statements
LEAVE and ITERATE
Using a Correlation Name for a Cursor Specification
Updatable and Read-Only Cursors
Rules for SQL Statements in a FOR Loop
Rules for FOR Cursors
Rules for FOR-Loop Variables
Rules for FOR-Loop Correlation Names
Rules for FOR-Loop Cursor Names
FOR Examples
Example: FOR-Loop Insert
Example: FOR-Loop Delete
Example: FOR-Loop Update
IF
IF Syntax
IF Syntax Elements
IF Usage Notes
ELSEIF Rule
Valid Forms of the IF Statement
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSEIF-END Behavior
IF-THEN-ELSEIF-ELSE-END Behavior
ITERATE
ITERATE Syntax
ITERATE Usage Notes
ITERATE Examples
Example: Using ITERATE to Iterate a WHILE Statement
Example: Using ITERATE to Iterate an Outer Loop
Example: Using ITERATE to Iterate outside a FOR Loop
LEAVE
LEAVE Syntax
LEAVE Usage Notes
LEAVE Example
Example: Using LEAVE with an Iteration Statement
LOOP
LOOP Syntax
LOOP Usage Notes
Example: The LOOP Statement
REPEAT
REPEAT Syntax
REPEAT Usage Notes
SET
SET Syntax
SET Usage Notes
Example: Using the SET Statement to Assign Values
WHILE
WHILE Syntax
WHILE Usage Notes
WHILE Examples
Static Embedded SQL Statements
Statements for Positioned Cursors
BEGIN DECLARE SECTION
BEGIN DECLARE SECTION Syntax
BEGIN DECLARE SECTION Usage Notes
COMMENT (Returning Form)
COMMENT (Returning Form) Syntax
COMMENT (Returning Form) Usage Notes
DATABASE
DATABASE Syntax
DATABASE Usage Notes
DECLARE STATEMENT
DECLARE STATEMENT Syntax
DECLARE STATEMENT Usage Notes
DECLARE TABLE
DECLARE TABLE Syntax
DECLARE TABLE Usage Notes
END DECLARE SECTION
END DECLARE SECTION Syntax
END DECLARE SECTION Usage Notes
END-EXEC Statement Terminator
END-EXEC Statement Terminator Syntax
END-EXEC Statement Terminator Usage Notes
EXEC
EXEC Syntax
EXEC Usage Notes
EXEC SQL Statement Prefix
EXEC SQL Statement Prefix Syntax
EXEC SQL Statement Prefix Usage Notes
EXEC SQL Statement Prefix Examples
WHENEVER
WHENEVER Syntax
WHENEVER Usage Notes
INCLUDE
INCLUDE Syntax
INCLUDE Usage Notes
INCLUDE SQLCA
INCLUDE SQLCA Syntax
INCLUDE SQLCA Usage Notes
INCLUDE SQLDA
INCLUDE SQLDA Syntax
INCLUDE SQLDA Usage Notes
Dynamic Embedded SQL Statements
Using Dynamic SQL
Performing SQL Statements Dynamically
Dynamic SQL Statement Syntax
DESCRIBE
DESCRIBE Syntax
DESCRIBE Usage Notes
EXECUTE (Dynamic SQL Form)
EXECUTE (Dynamic SQL Form) Syntax
EXECUTE (Dynamic SQL Form) Usage Notes
EXECUTE IMMEDIATE for Dynamic SQL Statement
EXECUTE IMMEDIATE for Dynamic SQL Statement Syntax
EXECUTE IMMEDIATE for Dynamic SQL Statement Usage Notes
PREPARE (Dynamic)
PREPARE Syntax
PREPARE Syntax Elements
PREPARE Usage Notes
Preparing an SQL Statement
PREPARE Rules
Client-Server Connectivity Statements
Connecting a Client Application to Vantage
CONNECT
CONNECT Syntax
CONNECT Syntax Elements
CONNECT Usage Notes
Difference between CONNECT and LOGON
SQL CONNECT and Preprocessor Connection to Vantage
Difference between Implicit and Explicit Connection
General Rules
Rules for AS (connection_name:connection_name_variable) Clause
GET CRASH
GET CRASH Syntax
GET CRASH Usage Notes
LOGOFF
LOGOFF Syntax
LOGOFF Usage Notes
LOGOFF Examples
Example: Disconnect Using an Explicit Connection Name
Example: Disconnect Using a Connection Name Supplied Using the VARCHAR Host Variable connamev
Example: Disconnect Using a ConnectionName Supplied Using the Fixed-Length Host Variable connamef
Example: Disconnect All Connections
Example: LOGOFF CURRENT
LOGON
LOGON Syntax
LOGON Usage Notes
LOGON Examples
Example: Logging On Using a Host Variable
Example: Logging On Using a Host Variable to Communicate the Connection Name
Example: Logging On Using a Fixed-Length Character Connection Name Passed to Using a Host Variable
SET BUFFERSIZE
SET BUFFERSIZE Syntax
SET BUFFERSIZE Usage Notes
SET CHARSET
SET CHARSET Syntax
SET CHARSET Usage Notes
SET CONNECTION
SET CONNECTION Syntax
SET CONNECTION Usage Notes
SET CONNECTION Examples
Example: Establishing a Session Connection
Example: Establishing Session Connection Using VARCHAR Connection Name Passed to SET CONNECTION Using Host Variable
Example: Establishing Session Connection Using CHAR Connection Name Passed to SET CONNECTION Using Host Variable
SET CRASH
SET CRASH Syntax
SET CRASH Syntax Elements
SET CRASH Usage Notes
Platform
When Crash Options Apply
Preprocessor Behavior When a Node Resets
Application Behavior When a Node Resets
Application Behavior When SET CRASH = WAIT_NOTELL
Application Behavior When SET CRASH = NOWAIT_TELL
SET ENCRYPTION
SET ENCRYPTION Syntax
SET ENCRYPTION Usage Notes
Multisession Asynchronous Programming with Embedded SQL
Multisession Programming with Embedded SQL
Multisession Asynchronous Request Programming Support
ASYNC Statement Modifier
TEST
TEST Syntax
TEST Usage Notes
TEST Examples
Example: Explicitly Specifying an Async Statement Identifier
Example: Getting the Async Statement Identifier Using a Host Variable
Example: Passing the Async Statement Identifier as a Fixed-Length Character String Using a Host Variable
Example: Testing the Statement Identified by the Name req_1
Example: Testing the Statement Identified by the Host Variable :reqid_var
Example: Using TEST with WAIT
WAIT
WAIT Syntax
WAIT Usage Notes
WAIT Examples
Example: Passing Fixed Length Character Values to the Host Variables
Example: Passing Varying Length Character Values to Host Variables in ANY COMPLETION INTO Clause
Example: Using the ALL COMPLETION Option to Wait until All Active Asynchronous Statements Have Completed
Example: Using Multiple Explicit Asynchronous Statements
Example: Basic WAIT Statement
Example: More Complicated WAIT Statement
Example: Outstanding Asynchronous SQL Statements
Example: Completing Outstanding Asynchronous SQL Statements
SQL Descriptor Area (SQLDA)
SQL Statements that Use SQLDA
How SQL Statements Use SQLDA
Defining the SQLDA for an Application
SQLDA Structure
SQLDA Data Type Codes
SQL Communications Area (SQLCA)
Defining the SQLCA for an Application
Checking Status Variables
Result Reporting
SQLCA Fields
Mapping SQLCODE Values to SQLSTATE Values
Mapping CLI Codes to SQLCODE Values
SQLSTATE Mappings
SQLSTATE Codes
Mapping Database Error Messages to SQLSTATE Values
Mapping CLI Codes to SQLSTATE Values
SQL Stored Procedure Command Function Codes
Performance Considerations
Using Updatable Cursors to Optimize Query Design
PIVOT Examples
SQL Commands
SQL Command Reference (alphabetical)
SQL Data Control Language
Database Privileges
Privilege Levels
System-Level Privileges
Object-Level Privileges
Row-Level Privileges
Where Information about Row-Level Security Elements is Stored
User Privilege Types
Implicit Privileges
Explicit Privileges
Checking User Privileges
Non-User Privileges
Statement Syntax
GIVE
GIVE Syntax
GIVE Syntax Elements
GIVE Usage Notes
Transfer of Space Allocation
GIVE Example
GRANT
GRANT (Monitor Form)
GRANT Syntax (Monitor Form)
GRANT Syntax Elements (Monitor Form)
GRANT (Monitor Form): Usage Notes
GRANT (Role Form)
GRANT Syntax (Role Form)
GRANT Syntax Elements (Role Form)
GRANT (Role Form) Usage Notes
GRANT (Role Form) Examples
GRANT (Role Form) Related Information
GRANT (SQL Form)
GRANT Syntax (SQL Form)
GRANT Syntax Elements (SQL Form)
GRANT Usage Notes (SQL Form)
Granting Multiple Privileges with a Single Keyword
Data Dictionary Privilege Abbreviations
GRANT Privilege Rules (SQL Form)
Differences between GRANT and GIVE
REFERENCES Privilege
INDEX Privilege
Column-Level Privileges
Privileges Required for Modifying Data
Security Considerations with the CREATE MACRO Privilege
Logging Access Attempts
Restrictions on Granted Privileges
Granting Privileges on Global Temporary and Volatile Tables
Verifying Privileges on Views, Macros, and Procedures
CTCONTROL Privilege
SHOW Privilege
Granting Privileges on Queue Tables
GLOP Privileges
Privileges and Procedures
Granting Privileges on UDFs
CREATE FUNCTION Privilege
EXECUTE FUNCTION Privilege
DROP FUNCTION Privilege
Teradata Row Level Security Privileges
System-Level Privileges for Row-Level Security
Object-Level Privileges for Row-Level Security
Example: Granting Row-Level Security OVERRIDE Privileges to Users and Roles
Granting Privileges to Roles
Case Study: Granting SELECT on a View
Case Study: Granting INSERT and DELETE on a View
Case Study: SQL Procedures
Examples: Granting Privileges with GRANT (SQL Form)
GRANT (SQL Form) Related Information
GRANT CONNECT THROUGH
GRANT CONNECT THROUGH Syntax
GRANT CONNECT THROUGH Syntax Elements
CONNECT THROUGH Usage Notes
GRANT CONNECT THROUGH, Trusted Sessions, and User Types
Application Proxy Users
Permanent Proxy Users
Software Enforcement of Trusted Sessions
Teradata Client Software Enforcement of Trusted Sessions
Teradata Server Software Enforcement of Trusted Sessions
Dictionary Storage of CONNECT THROUGH Metadata
GRANT CONNECT THROUGH Examples
GRANT CONNECT THROUGH Related Information
GRANT LOGON
GRANT LOGON Syntax
GRANT LOGON Syntax Elements
GRANT LOGON Usage Notes
System Privilege Checks
GRANT LOGON for One or More User Names
Logging On with No Logon Control Record for a User Name
Logging On as User DBC
Logging On without a Password
Logging On Using External Authentication
GRANT LOGON Related Information
GRANT MAP
GRANT MAP Syntax
GRANT MAP Syntax Elements
REVOKE
REVOKE (Monitor Form)
REVOKE Syntax (Monitor Form)
REVOKE Syntax Elements (Monitor Form)
REVOKE (Monitor Form) Usage Notes
REVOKE (Monitor Form) Example
REVOKE (Role Form)
REVOKE Syntax (Role Form)
REVOKE Syntax Elements (Role Form)
REVOKE (Role Form) Example
REVOKE (Role Form) Related Information
REVOKE (SQL Form)
REVOKE Syntax (SQL Form)
REVOKE Syntax Elements (SQL Form)
REVOKE (SQL Form) Usage Notes
REVOKE Privilege Rules (SQL Form)
Privileges Level for a Revoke
Rules for Revoking Privileges
Revoking Privileges on Global Temporary and Volatile Tables
GRANT/REVOKE Order and Duration of Privileges
Revoking Privileges on a Function Mapping
Revoking Privileges on Procedures
Examples: Revoking Privileges On SQL Procedures
Revoking Privileges from Roles
Revoking the CTCONTROL Privilege
REVOKE (SQL Form) Examples
REVOKE (SQL Form) Related Information
REVOKE CONNECT THROUGH
REVOKE CONNECT THROUGH Syntax
REVOKE CONNECT THROUGH Syntax Elements
REVOKE CONNECT THROUGH Usage Notes
REVOKE CONNECT THROUGH Example
REVOKE CONNECT THROUGH Related Information
REVOKE LOGON
REVOKE LOGON Syntax
REVOKE LOGON Syntax Elements
REVOKE LOGON Usage Notes
REVOKE MAP
REVOKE MAP Syntax
REVOKE MAP Syntax Elements
Compute Group and Compute Cluster Object Privileges
GRANT CREATE COMPUTE GROUP
GRANT CREATE COMPUTE GROUP Syntax
GRANT CREATE COMPUTE GROUP Syntax Elements
GRANT CREATE/DROP COMPUTE GROUP Privileges Usage Rules
GRANT CREATE COMPUTE GROUP Privilege Example
GRANT CREATE/DROP COMPUTE GROUP WITH GRANT OPTION Privileges Examples
GRANT DROP COMPUTE GROUP
GRANT DROP COMPUTE GROUP Syntax
GRANT DROP COMPUTE GROUP Syntax Elements
GRANT DROP COMPUTE GROUP Privilege Example
GRANT CREATE/DROP COMPUTE GROUP WITH GRANT OPTION Privileges Examples
GRANT COMPUTE GROUP
GRANT COMPUTE GROUP Syntax
GRANT COMPUTE GROUP Syntax Elements
GRANT COMPUTE GROUP Example
GRANT CREATE/DROP COMPUTE GROUP Access Privilege Examples
GRANT CREATE/DROP COMPUTE GROUP Access Privilege WITH GRANT OPTION Examples
GRANT (SQL Form) CREATE and DROP COMPUTE PROFILE Privileges
GRANT CREATE COMPUTE PROFILE
GRANT CREATE COMPUTE PROFILE Syntax
GRANT CREATE COMPUTE PROFILE Syntax Elements
GRANT DROP COMPUTE PROFILE
GRANT DROP COMPUTE PROFILE Syntax
GRANT DROP COMPUTE PROFILE Syntax Elements
GRANT CREATE/DROP COMPUTE PROFILE Privileges Usage Rules
GRANT CREATE/DROP COMPUTE PROFILE WITH GRANT OPTION Privileges Examples
GRANT CREATE/DROP COMPUTE PROFILE Privileges Examples
Revoking Privileges to Users/Roles to Access a Compute Cluster Group
REVOKE (SQL Form) CREATE/DROP COMPUTE GROUP Privilege
REVOKE CREATE/DROP COMPUTE GROUP Privilege Usage Rules
REVOKE CREATE COMPUTE GROUP
REVOKE CREATE COMPUTE GROUP Syntax
REVOKE CREATE COMPUTE GROUP Syntax Elements
REVOKE CREATE COMPUTE GROUP and GRANT OPTION from Compute Group Examples
REVOKE DROP COMPUTE GROUP
REVOKE DROP COMPUTE GROUP Syntax
REVOKE DROP COMPUTE GROUP Syntax Elements
REVOKE DROP COMPUTE GROUP and GRANT OPTION from Compute Group Examples
REVOKE (SQL Form) CREATE/DROP COMPUTE PROFILE Privilege
REVOKE CREATE COMPUTE PROFILE
REVOKE CREATE COMPUTE PROFILE Syntax
REVOKE CREATE COMPUTE PROFILE Syntax Elements
REVOKE COMPUTE PROFILE Privilege Usage Rules
REVOKE CREATE COMPUTE PROFILE Examples
REVOKE DROP COMPUTE PROFILE
REVOKE DROP COMPUTE PROFILE Syntax
REVOKE DROP COMPUTE PROFILE Syntax Elements
REVOKE DROP COMPUTE PROFILE Examples
Privilege Dictionary
Privileges
Multiple Privileges with a Single Keyword
Required DBC Privileges
Default PUBLIC Privileges
Privileges Needed for Database Administration
Compute Clusters
Databases
DATASET SCHEMA
Geospatial Data Types
GLOP
Join Index
Macros
Profiles
Proxy Users and Trusted Users
Roles and External Roles
Security Constraints
Statistics
SQL and External Procedures
Tables
Triggers
Users
User-Defined Functions (UDFs)
Views
Determining Privileges for a User
Sample Macro for Determining User Privileges
Running the Privilege Check Macro
SQL Data Definition Language
How the Statements are Organized
List of SQL Statements and Purposes
Table Statements
CREATE TABLE and CREATE TABLE AS
CREATE TABLE Syntax
CREATE TABLE AS Syntax
CREATE TABLE and CREATE TABLE AS Syntax Elements
CREATE TABLE Usage Notes
MAP Option
Block-Level Compression and Tables
AUTODBA Option
CREATE TABLE and CREATE TABLE AS Examples
Examples: MAP Option
Example: Specifying MERGEBLOCKRATIO Settings
Example: Specifying DATABLOCKSIZE, CHECKSUM, and FREESPACE
Example: CHARACTER Partitioning Using a CASE_N Function
Example: CHARACTER Partitioning Defined Using a RANGE_N Function
Example: Character Partitioning Defined Using RANGE_N Functions
Example: Character Partitioning, Session Mode, and Case Specificity
Example: Character Partitioning and System-Derived PARTITION[#Ln] Columns
Example: RANGE_N Partitioning Expression Using the END Bound Function
Example: CASE_N Partitioning Expression Using the END Bound Function
Example: 15 Levels of Multilevel Partitioning
Example: Volatile Table with Single-Level Partitioning
Example: Global Temporary Table with Single-Level Partitioning
Example: Specifying a Column-Level Primary Key
Example: Default Primary Index When Neither Primary Index nor Primary Key Is Specified
Example: Local Journaling
Example: CREATE TABLE AS … WITH NO DATA with Named Expressions
Example: CREATE TABLE AS ... WITH NO DATA with Nonunique Secondary Index
Example: CREATE TABLE AS … WITH DATA with Nonunique Secondary Index
Example: Statistics Not Copied
Example: Statistics Are Not Copied for Conditions in the Subquery
Example: Statistics Are Not Copied When Set Or Aggregation Operators Are Specified in the Query
Example: Statistics Are Not Copied If an OLAP Operation Is Specified in the Subquery
Example: Statistics Are Not Copied If a Join Operation Is Specified in the Subquery
Example: Statistics Are Not Copied If Functions or Expressions Are Specified in the Subquery
Example: CREATE TABLE AS Requests that Create Table with Primary Index
Example: CREATE Request with Presence or Absence of PRIMARY KEY or UNIQUE Constraint and Setting of PrimaryIndexDefault
Example: NoPI Table CREATE Request that Converts PRIMARY KEY or UNIQUE Constraints to USIs
Example: CREATE Request that Produces a NoPI Table
Example: CREATE TABLE … AS Requests that Create NoPI Table
Example: Current and Historical Partitioning Using CURRENT_DATE in a CASE_N Expression
Example: CURRENT_DATE Built-In Function in a CASE_N Expression
Example: Quarterly Partitioning Using CURRENT_DATE in a RANGE_N Expression
Example: Current and Historical Partitioning Using CURRENT_DATE in a CASE_N Expression
Example: Partitioning Expression with TIMESTAMP Data Type
Example: Partitioning Expression that Specifies AT LOCAL Date
Example: Grouping Columns in the Column List of a Column-Partitioned Table Rather than in the Partitioning Expression
Example: Column-Partitioned Table with Default Autocompression
Example: Columns Defined Using Different Types of NUMBER Types
Example: Create Tables with ST_GEOMETRY Data Type Columns
Example: Create Tables with XML Data Type Columns
Example: Create Tables with JSON Data Type Columns
Example: Creating a Table with Row-Level Security Constraints
Example: Specifying Block-Level Compression Set to ALWAYS
Example: Specifying Block-Level Compression Set to AUTOTEMP
Examples: Creating a Table with a JSON Auto Column
Examples: Compression Attribute
Examples: Constraint Attribute
Example: Creating a Table with a Derived Period Column
Example: NORMALIZE
FOREIGN KEY Constraint Examples
Examples: PRIMARY INDEX
Examples: PRIMARY AMP
Example: Multilevel-Partitioned NUPI with a USI Defined on the Primary Index
Examples: PARTITION BY
Examples: AS Clause
Example: CREATE TABLE AS, CT AS, and Table Kind
Example: Copy Table with Data and Default Column Names
Example: Copy Table without Data and Default Column Names
Example: Changing Column Grouping for Column-Partitioned Table Using CREATE TABLE AS Syntax
Examples: Copying Statistics
Examples: Copying Statistics for All Columns
Example: Copying Zeroed Statistics
Examples: Copying Zeroed Multicolumn and Composite Index Statistics
Example: Copying PARTITION Statistics
Examples: AS Clause WITH DATA
Examples: AS Clause WITH NO DATA
Examples: AUTODBA Option
CREATE TABLE (Table Kind Clause)
Global Temporary Tables
Volatile Tables
Volatile and Global Temporary Tables, Teradata Session Mode, and DML Performance
Resolving Table References
Table Kinds and Period, and Geospatial Data Types
CREATE TABLE (Table Options Clause)
Specifying Data Protection
Specifying FALLBACK
Transient Journaling Supported by CREATE TABLE (Table Options Clause)
LOG and NO LOG
Permanent Journaling
FREESPACE PERCENT with CREATE TABLE (Table Options Clause)
MERGEBLOCKRATIO with CREATE TABLE (Table Options Clause)
DATABLOCKSIZE
BLOCKCOMPRESSION (CREATE TABLE)
Performance Aspects of Merging Data Blocks
CREATE TABLE (Column Definition Clause)
Table Column Elements
Data Type, Column Storage, and Constraint Attributes
Table Size and Maximum Number of Columns Per Table
Identity Columns
Process for Generating Identity Column Numbers
Rules for Specifying Identity Columns
Identity Columns, Duplicate Column Values, and Duplicate Rows
Identity Columns and Bulk Inserts
Identity Column Inserts and Transaction-Bound Table Locks on DBC.IdCol
Compressing Column Values Using Only Multivalue Compression
Compressing Column Values Using Only Algorithmic Compression
Common Rules for Multivalue and Algorithmic Compression
Other Forms of Compression Used by Vantage
Combining Multivalue, Algorithmic, and Block-Level Compression
Integrity Constraint Levels
PRIMARY KEY Constraints and UNIQUE Constraints
Surrogate Keys
CHECK Constraints
FOREIGN KEY Constraints
Referential Integrity Constraints
Standard Referential Integrity Constraints
Batch Referential Integrity Constraints
Referential Constraints
Example: How the Optimizer Takes Advantage of Referential Constraints
Validating the Integrity of Base Tables in a Referential Constraint Relationship
Scenario for Data Corruption with Referential Constraints
Normalizing Database Tables
Rules and Restrictions for the NORMALIZE Option
CREATE TABLE (Column Definition Clause) Related Information
2-Referential Constraints
CREATE TABLE (Index Definition Clause)
Primary-Indexing, Row-Partitioning, Column-Partitioning, NoPI Tables, and Secondary Indexes
Index Types
Primary Indexes
Nonpartitioned NoPI Tables
Column-Partitioned Tables
Usage Guidelines for Column-Partitioned Tables
Rules and Restrictions for Column-Partitioned Tables
General Rules for Column-Partitioned Tables
Rules for Column Partitions in a Column-Partitioned Table
Rules for Partition Formats in a Column-Partitioned Table
Rules for Primary Indexes and Their Defaults for a Column-Partitioned Table
Rules for Specifying Column Grouping in a Column-Partitioned Table
Rules for Using the ADD Option for the Partitioning Levels of a Column-Partitioned Table
Rules for the Maximum Partition Numbers and Partitioning Levels of a Column-Partitioned Table
Rules for Using Client Load Utilities with a Column-Partitioned Table
Performance Issues for Column-Partitioned Tables
Comparing Nonpartitioned NoPI Tables with Column-Partitioned Tables with and without Autocompression
Partitioned and Nonpartitioned Primary Indexes
ADD Option
Partitioning Expressions Using a CASE_N or RANGE_N Character Column
Partitioning Expressions Using DATE or TIMESTAMP Built-In Functions
Partitioning Expression Based on Updatable Current Date and Timestamp Expressions
Optimal Reconciliation of CASE_N Partitioning Expressions Based on Updatable Current Date and Timestamp
Optimal Reconciliation of RANGE_N PPI Expressions Based on Updatable Current Date and Timestamp
Optimal Reconciliation of CASE Partitioning Expressions Based on Updatable Current Date and Timestamp
Optimal Reconciliation of Partitioning Expressions Based on Updatable Current Date and Timestamp
Partitioning Expression Columns and the Primary Index
Query Conditions and Static Row Partition Elimination
Join Conditions and Partitioned Primary Indexes
Effects of Dynamic Row Partition Elimination on Joins
Rules and Usage Notes for Partitioned Tables
Partitioning CHECK Constraints for Partitioned Tables with 2-Byte Partitioning
Partitioning CHECK Constraint for Partitioned Tables with 8-Byte Partitioning
Restrictions for Multilevel Partitioning
Combined Partitioning Expressions
Rules and Usage Notes for Multilevel Partitioning
Purpose and Behavior of the NO RANGE and UNKNOWN Partitions
Determining the Partitioning Granularity
Memory Limitations Related to Partitioned Tables
Restrictions and Limitations for Load Utilities and Partitioned Tables
Restrictions and Limitations for Archive/Recovery and Partitioned Tables
Secondary Indexes
PRIMARY KEY and UNIQUE Constraints or Primary Indexes
Nullable Columns Are Valid for Unique Indexes
CREATE TABLE (Index Definition Clause) Related Information
CREATE TABLE (Global Temporary/Volatile Table Preservation Clause)
Deleting or Preserving Global Temporary and Volatile Table Contents
ON COMMIT DELETE/PRESERVE ROWS
CREATE TABLE (Global Temporary/Volatile Table Preservation Clause) Related Information
CREATE TABLE (AS Clause)
CREATE TABLE AS Required Privileges
Copying Table Definitions
Target Table Kind Defaults
Exclusions from CREATE TABLE … AS Syntax
CREATE TABLE (AS Clause) General Usage Rules
Usage Rules for Normalized Tables
Attributes Not Copied to The Target Table
Using Subqueries to Customize an AS Clause
Exclusive AS … WITH DATA Clause Rules
Comparing PARTITION Statistics Copied with COLLECT STATISTICS … FROM source_table to PARTITION Statistics Copied with CREATE TABLE AS … WITH DATA AND STATISTICS
General Rules for CREATE TABLE AS … WITH DATA AND STATISTICS
Rules for CREATE TABLE AS … WITH DATA AND STATISTICS without Subquery to Define Source Table Column and Index Set
Rules for AS … WITH DATA AND STATISTICS that Uses a Subquery to Define the Source Table Column and Index Set
General Rules for CREATE TABLE AS … WITH NO DATA
Rules for CREATE TABLE AS … WITH NO DATA that Does Not Use a Subquery to Define the Source
Rules for AS … WITH NO DATA that Defines Source with Subquery
Misleading Similarities of WITH DATA and WITH NO DATA Clauses
CREATE TABLE AS Related Information
CREATE TABLE and CREATE TABLE AS (Object File System Table Form)
CREATE TABLE Syntax (Object File System Table Form)
CREATE TABLE AS Syntax (Object File System Table Form)
CREATE TABLE and CREATE TABLE AS Syntax Elements (Object File System Table Form)
CREATE TABLE and CREATE TABLE AS Usage Notes (Object File System Table Form)
Examples: CREATE TABLE with ORDER BY Expression List
Examples: Create OFS Tables with PRIMARY INDEX
Examples: Create OFS Tables with Table-Level Retention
CREATE TABLE (Queue Table Form)
CREATE TABLE Syntax (Queue Table Form)
CREATE TABLE Syntax Elements (Queue Table Form)
CREATE TABLE Usage Notes (Queue Table Form)
MAP Option (Queue Table Form)
QUEUE Keyword and Column Identifier
Function of Queue Tables
Queue Table Cache
Identifying Queue Tables
SQL Operations on Queue Tables
Consume Mode Transactions
Performance Issues for Queue Tables
Restrictions on Queue Tables
Queue Table-Related Restrictions on Other SQL Statements
Browsing a Queue Table
Rules for Consuming Rows from a Queue
Ordering Queue Table Rows
Populating a Queue Table
Considerations for Populating an Unpopulated Queue Table in a Delayed State
CREATE TABLE Examples (Queue Table Form)
Example: Creating a Simple Queue Table
Example: Creating a Multiset Queue Table
Example: CREATE TABLE (Queue Table Form) Map Option
Example: Using SELECT AND CONSUME on Queue Tables with JSON, ST_GEOMETRY, or XML Columns
Example: Creating a Queue Table Defined with an Identity Column
Example: Creating a Queue Table with Constraints and a UPI
Example: Creating a Queue with a UPI and a NUSI
CREATE TABLE (Alias Table Form)
CREATE TABLE Syntax (Alias Table Form)
CREATE TABLE Syntax Elements (Alias Table Form)
CREATE TABLE Usage (Alias Table Form)
Examples: CREATE TABLE (Alias Table Form)
CREATE TABLE (Datalake Form)
CREATE TABLE Syntax (Datalake Form)
CREATE TABLE Syntax Elements (Datalake Form)
Open Table Format Data Types
Example: CREATE TABLE (Datalake Form)
CREATE TABLE AS (Datalake Form)
CREATE TABLE AS Syntax (Datalake Form)
CREATE TABLE AS Syntax Elements (Datalake Form)
Example: CREATE TABLE AS (Datalake Form)
CREATE GLOBAL TEMPORARY TRACE TABLE
CREATE GLOBAL TEMPORARY TRACE TABLE Syntax
CREATE GLOBAL TEMPORARY TRACE TABLE Syntax Elements
CREATE GLOBAL TEMPORARY TRACE TABLE Usage Notes
CREATE GLOBAL TEMPORARY TRACE TABLE Examples
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE Syntax
CREATE FOREIGN TABLE Syntax Elements
CREATE FOREIGN TABLE Usage Notes
External File Types
External File Object Rules
JSON External Files
CSV External Files
Parquet External Files
Foreign Table Locking
Queries on External Data are Nondeterministic
LOCATION Key Prefix Best Practices
Indexes and Foreign Tables
Table Types and Foreign Tables
Copying Foreign Tables
Column Level Compression and Foreign Tables
Disable HASH BY RANDOM
Native Object Store Limitations
CREATE FOREIGN TABLE Examples
Examples: Creating Foreign Tables
Creating Foreign JSON File
Creating Foreign CSV File
Creating Foreign Parquet File
Creating Volatile Foreign Table
Example: Creating Delta Lake Table
Example: Creating Foreign Table Using DNS Style
Example: Creating Foreign Table Using Simple Style
Example: Creating Foreign Table Using Path Style
Example: Creating Foreign Table Using ENDPOINT with Simple Style
Example: Creating Foreign Table with Manifest File using Simple Style
Example: Creating Foreign Table for Azure Using Simple Style
Example: Creating Foreign Table for Google Cloud Storage Using Simple Style and ENDPOINT Statement
Example: Creating Foreign Table with PATHPATTERN
Example: Creating Foreign Table with Payload Column with Latin Characters
Set Up External Object Storage for River Flow Data
Example: Copying Foreign Table without Data
Example: Copying Data from Foreign Table into Permanent Table
Example: Creating Foreign Table to Access Parquet Data
Example: Importing External Data from Foreign Table into Permanent Table
1. Create Foreign Table
2. Create View of Foreign Table
3. Create Permanent Table
4. Insert Foreign Table Rows into Permanent Table
5. Display Permanent Table
Example: SHOW TABLE for Foreign Table
CREATE FOREIGN TABLE Statement
Default Format
XML Format
Example: NOS Detects Columns
Example: Foreign Table with Location Column Excluded
Example: CREATE FOREIGN TABLE Statement Specifies Columns
Example: NOS and Nondefault Field Delimiter, Header
Example: STRIP_EXTERIOR_SPACES and STRIP_ENCLOSING_CHAR
Example: Object Paths with Variable Names
Example: Object Paths without Variable Names
Examples: Schema Evolution Scenarios
Schema Setup
Example: Re-Ordering of Columns Using Schema Evolution
Example: Resolving User-Defined Columns Using Schema Evolution
Examples: New Datatype Conversions with Schema Evolution
Examples: Volatile Foreign Tables
Displaying a Volatile Foreign Table Using SHOW TABLE
Retrieving Data From a Volatile Foreign Table Using SELECT
Automatic Removal of Volatile Foreign Table Once Session Ends
Creating Volatile Foreign Tables Using Macros
Using Views with NOS Volatile Tables
Performing Operations Using Triggers
Creating NOS Volatile Tables Using Stored Procedures
Performing Operations Using Virtual Columns (Path Variables)
Example: GENERIC_DATATYPE, STRING_SIZE, and BINARY_SIZE
Examples: Using SNAPSHOT_LOCATION with FOREIGN TABLE
CREATE ERROR TABLE
CREATE ERROR TABLE Syntax
CREATE ERROR TABLE Syntax Elements
error_table_name_specification
data_table_name_specification
NO RLS
CREATE ERROR TABLE Usage Notes
Function of Error Tables
Rules and Restrictions for Error Tables
Dictionary Support for Error Tables
System-Defined Attributes for Error Table-Specific Columns
Usage Considerations for the Error Table Columns
CREATE ERROR TABLE Examples
CREATE ERROR TABLE Related Information
CREATE ERROR TABLE (OFS)
CREATE ERROR TABLE (OFS) Syntax
CREATE ERROR TABLE (OFS) Syntax Elements
CREATE ERROR TABLE (OFS) Usage Notes
Storage Specifications
Data Table Specification
System Defined Attributes
Handling Errors
ALTER TABLE
ALTER TABLE Syntax (Basic)
ALTER TABLE Syntax (Join Index)
ALTER TABLE Syntax (Revalidation)
ALTER TABLE Syntax Elements
ALTER TABLE Syntax Elements (Basic)
ALTER TABLE Basic Options
NORMALIZE
MODIFY PRIMARY
MODIFY NO PRIMARY
MODIFY partitioning
FROM TIME ZONE
DOWN
ALTER TABLE Syntax Elements (Join Index)
ALTER TABLE Syntax Elements (Revalidation)
Alter Options
ADD column_name
ADD COLUMN (column_name)
ADD ROW (column_name)
ADD SYSTEM (column_name )
ADD (column_name ) AUTO COMPRESS
ADD PERIOD FOR
ADD CONSTRAINT name
ADD row_level_security_constraint_name CONSTRAINT
ADD FOREIGN KEY REFERENCES
ADD CHECK (boolean_condition)
ADD UNIQUE (column_name)
ADD PRIMARY KEY (column_name)
MODIFY CHECK (boolean_condition )
RENAME
DROP PERIOD FOR
DROP column_name
DROP CONSTRAINT name
DROP row_level_security_constraint_name CONSTRAINT
DROP FOREIGN KEY REFERENCES
DROP CHECK
DROP INCONSISTENT REFERENCES
Table Options
FALLBACK
WITH JOURNAL TABLE
[NO] AUTODBA [WITH CHECK]
RETENTIONDAYS
JOURNAL
ON COMMIT
LOG
AFTER JOURNAL
CHECKSUM
DEFAULT FREESPACE
FREESPACE
MERGEBLOCKRATIO
DATABLOCKSIZE
BLOCKCOMPRESSION
USING FAST MODE
Column Attributes
UPPERCASE
CASESPECIFIC
TITLE quotestring
NAMED name
DEFAULT
WITH DEFAULT
CHARACTER SET server_character_set
NULL
AUTO COLUMN
NO COMPRESS
COMPRESS
COMPRESS USING
DECOMPRESS USING
UNIQUE
PRIMARY KEY
CHECK (boolean_condition)
REFERENCES
Alter Partitioning
ALTER TABLE Examples
Examples: ALTER TABLE NORMALIZE
Examples: ALTER TABLE MODIFY PRIMARY
Examples: ALTER TABLE MODIFY NO PRIMARY
Examples: ALTER TABLE MODIFY partitioning
Example: Migrating Data to a New Time Zone
Example: Using SET DOWN
Example: Using RESET DOWN
Examples: ALTER TABLE Join Index
Examples: ALTER TABLE ADD column_name
Examples: ALTER TABLE ADD COLUMN (column_name)
Examples: ALTER TABLE ADD ROW (column_name)
Example: Adding a Single-Column Partition to a Table
Example: Adding a Single-Column Partition
Example: Adding a Column to a Single-Column Partition
Example: Trying to Add and Drop Constraints in the Same Statement
Example: Trying to Add a Constraint with a Duplicate Name
Example: Adding a Row-Level Security Constraint
Example: Adding a FOREIGN KEY Constraint
Example: Adding or Dropping a Batch Referential Constraint
Example: Adding a Table-Level Referential Constraint
Example: Adding a NORMALIZE Constraint to a Table
Example: Adding a Foreign Key to a Column-Partitioned Table
Example: Adding a Named CHECK Constraint to a Table
Example: Adding CHECK Constraint to Table
Example: Adding a Column with an Unnamed CHECK Constraint
Example: Adding a Named UNIQUE Constraint to a Table
Example: Adding a Multicolumn Unnamed UNIQUE Constraint
Example: Adding a PRIMARY KEY Constraint to a Table
Example: Modifying a Named CHECK Constraint
Example: Trying to Modify a Nonexistent Constraint
Example: Adding a Derived Period Column to a Table
Example: Dropping the IDENTITY Attribute from a Column without Dropping the Column
Example: Dropping a Row-Level Security Constraint with ALTER TABLE
Example: Dropping a Column from a Column Partition and Making the Partition a Two-Column Partition
Example: Dropping a Column from a Column Partition and Making the Partition a Single-Column Partition
Example: Dropping a Column from a Column Partition Which Also Drops the Partition
Example: Dropping 2 Columns from a Column Partition Which Also Drops the Partition
Example: Dropping All but One Column from a Column Partition
Example: Dropping a Named Constraint from a Table
Example: Dropping a Named CHECK Constraint from a Table
Example: Dropping a UNIQUE Constraint from a Table
Example: Dropping an Unnamed Column-Level CHECK Constraint
Example: Adding CHECK Constraint to Column with MODIFY
Example: Trying to Drop a Nonexistent Constraint
Example: Adding a Named FOREIGN KEY Constraint to a Table
Example: Dropping a FOREIGN KEY Constraint
Example: Dropping All Unnamed Table-Level CHECK Constraints
Example: Adding Fallback to a Table
Example: Adding a Single Before-Image Journal and Dual After-Image Journal to a Table
Example: Changing a Single Before-Image Journal to a Dual Before-Image Journal and Dropping Two Columns
Example: Changing CHECKSUM to the Default
Example: Changing FREESPACE
Example: Modifying MERGEBLOCKRATIO
Example: Changing DATABLOCKSIZE
Example: Adding a TITLE Phrase to a Column
Example: Adding the Null Attribute to Columns
Example: Making Composite Changes to a Column: NULL to NOT NULL
Example: Making Composite Changes to a Column: NOT NULL to NULL
Examples: Adding and Removing the JSON Auto Column Option
Example: Changing NO COMPRESS for an Existing Column
Example: Changing the Value Compression for a Compressed Column
Example: Adding a New Column with Multivalue Compression
Example: Adding and Changing Algorithmic Compression
Table Definitions for Examples
Example: Modifying Partition Ranges
Example: Non-Valid MODIFY PRIMARY INDEX Statements
Example: Changing a CASE_N-Based Row Partitioning Expression
Example: Adding a Name to a Previously Unnamed Primary Index
Example: Modifying Partitioning for Multilevel Partitioned Tables
Example: Modifying Row Partitioning for Multilevel Column-Partitioned Tables
Example: Modifying Character Partitioning for a Row-Partitioned Table
Example: Modifying Character Partitioning for a Column-Partitioned Table
Example: Dropping and Adding Partition Ranges
Example: Drop and Add Partition Ranges and Delete Rows outside the Defined Ranges
Example: Dropping Row Range Partition When Partitioning is Defined with RANGE_N Function and NO RANGE Partition
Example: Dropping Ranges without an EACH Clause
Example: Using MODIFY to Repartition a Table and Saving Resulting Nonvalid Rows in a Save Table
Example: Revalidating the Partitioning for a Table
Examples: USING FAST MODE
Examples: ALTER OFS Tables with Table-Level Retention
ALTER TABLE Usage Notes (Basic Table Parameters)
Rules and Restrictions for ALTER TABLE (Basic Table Parameters)
Referential Integrity Error Tables for Column-Partitioned Tables
Working with Columns and Statistics
ALTER TABLE and FALLBACK
Types of Journaling Supported by ALTER TABLE (Basic Table Parameters)
ALTER TABLE and AUTODBA
FREESPACE PERCENT with ALTER TABLE (Basic Table Parameters)
MERGEBLOCKRATIO with ALTER TABLE (Basic Table Parameters)
BLOCKCOMPRESSION (ALTER TABLE)
Adding Columns to a Table
Adding Columns to a Column-Partitioned Table
Adding Columns to a Column Partition in a Column-Partitioned Table
Adding Columns to a Normalized Table
Working with Column and Data Type Attributes
Modifying Column Data Types or Multivalue Compression
Using Compression Methods with Table Columns
Adding and Dropping CHECK Constraints
Dropping Columns from a Table
Renaming Table Columns
Adding or Dropping PRIMARY KEY and UNIQUE Constraints
Rules for Adding or Dropping Constraints
Maintaining Foreign Key Integrity
Referential Integrity When a Table Is Inconsistent
Rules for Changing Column Data Types
Procedure to Change Column Data Types
OFS Table-Level Policy
ALTER TABLE Usage Notes (MODIFY Option)
Redefining the Primary, Primary AMP, or Partitioning
Dropping or Adding New Ranges or Partitions to a Row-Partitioned Table
General Rules for the MODIFY PRIMARY Clause
MODIFY PRIMARY Option
MODIFY NO PRIMARY Option
Rules for Modifying Populated and Unpopulated Tables and Join Indexes
Modifying the Partitioning of a Table or Join Index
WITH INSERT and WITH DELETE Null Partition Handling Clauses
RANGE_N and CASE_N Functions
Modifying Partitioning Using the ADD RANGE and DROP RANGE Options
Best Practices for Adding and Dropping Partitioning Ranges from a Partitioning Expression
Working with Partitioning
Rules for Altering a Partitioning for a Table
Modifying Row Partitioning Defined with an Updatable DATE, CURRENT_DATE, or CURRENT_TIMESTAMP
Rules for Adding and Dropping Ranges in a Character Partitioning Expression
Rules for Altering the Row Partitioning for Character Partitioning
Rules for Altering the Partitioning Expression for Multilevel Partitioning
Rules and Restrictions for Modifying Column-Partitioned Tables
Rules for Retaining Eligibility for Restoring or Copying Selected Partitions
ALTER TABLE Usage Notes (REVALIDATE Option)
General Rules and Restrictions for the REVALIDATE Option
Revalidating the Partitioning for Tables and Join Indexes with a HASH Function Partitioning Expression
ALTER TABLE Usage Notes (SET DOWN and RESET DOWN Options)
ALTER TABLE Related Information
ALTER TABLE (Map and Colocation Form)
ALTER TABLE Syntax (Map and Colocation Form)
ALTER TABLE Syntax Elements (Map and Colocation Form)
ALTER TABLE Usage Notes (Map and Colocation Form)
ALTER TABLE Examples (Map and Colocation Form)
ALTER TABLE (Storage Conversion Form)
ALTER TABLE Syntax (Storage Conversion Form)
Example: Convert Row-Format Table to Column-Format Table
Example: Convert Row-Format Table to Row-Format Table
ALTER TABLE (Datalake Form)
ALTER TABLE Syntax (Datalake Form)
ALTER TABLE Syntax Elements (Datalake Form)
Examples: ALTER TABLE (Datalake Form)
Examples: Column Updates Using ALTER TABLE (Datalake Form)
Examples: Add or Modify Table Properties using ALTER TABLE (Datalake Form)
Examples: Partitioning Updates using ALTER TABLE (Datalake Form)
Examples: Sort Order Updates using ALTER TABLE (Datalake Form)
ALTER FOREIGN TABLE
ALTER FOREIGN TABLE Syntax
ALTER FOREIGN TABLE Syntax Elements
ALTER FOREIGN TABLE Usage Notes
ALTER FOREIGN TABLE Examples
Examples: Column-Level Alterations
Examples: Table-Level Alterations
Example: Altering LOCATION Alters PARTITION BY
Example: Altering PATHPATTERN Alters PARTITION BY
Example: Altering ENDPOINT
Example: Altering Location From Path to Simple Style Format
Example: Altering Location from Simple Style to Path Style Format
Example: Addition of Column to DDL Using Schema Evolution
Example: Dropping Column From Middle Position Using Schema Evolution
Example: Update Table with New Manifest File Version
ALTER TABLE TO CURRENT
ALTER TABLE TO CURRENT Syntax
ALTER TABLE TO CURRENT Syntax Elements
ALTER TABLE TO CURRENT Usage Notes
Comparing ALTER TABLE TO CURRENT and ALTER TABLE … REVALIDATE
General Usage Guidelines and Rules for ALTER TABLE TO CURRENT Requests
Guideline for Refreshing Partition Statistics after an ALTER TABLE TO CURRENT Request
Using ALTER TABLE TO CURRENT Requests to Reconcile Join Indexes
ALTER TABLE TO CURRENT Examples
ALTER TABLE REORGANIZE
ALTER TABLE REORGANIZE Syntax
Example: Reorganizing an Object File System Table
ALTER TABLE SAVE | UNSAVE
ALTER TABLE SAVE | UNSAVE Syntax
ALTER TABLE SAVE | UNSAVE Syntax Elements
ALTER TABLE SAVE | UNSAVE Examples
RENAME TABLE
RENAME TABLE Syntax
RENAME TABLE Syntax Elements
RENAME TABLE Usage Notes
RENAME TABLE Example
DROP TABLE
DROP TABLE Syntax
DROP TABLE Syntax Elements
DROP TABLE Usage Notes
DROP TABLE Examples
DROP TABLE (Datalake Form)
DROP TABLE Syntax (Datalake Form)
DROP TABLE Syntax Elements (Datalake Form)
DROP ERROR TABLE
DROP ERROR TABLE Syntax
DROP ERROR TABLE Syntax Elements
DROP ERROR TABLE Example
HELP COLUMN
HELP COLUMN Syntax
HELP COLUMN Syntax Elements
HELP COLUMN Usage Notes
HELP COLUMN Examples
Example: HELP COLUMN Sample Output
Example: HELP COLUMN column_name
Example: HELP COLUMN for All Columns
Example: HELP COLUMN for Fully Qualified Columns
Example: HELP COLUMN for a Table
Example: HELP COLUMN on Non-Column Expressions Only
Example: HELP COLUMN on Multiple table_name.* Expressions
Example: HELP COLUMN on a Mixture of Non-Column Expressions and table_name .* Expressions
Example: Partitioned Primary Index Table
Example: HELP COLUMN with Derived Period Column
Example: HELP COLUMN for Temporal Columns
Example: HELP COLUMN for JSON Column with Auto Column Option
HELP COLUMN Attributes
HELP CONSTRAINT
HELP CONSTRAINT Syntax
HELP CONSTRAINT Syntax Elements
HELP CONSTRAINT Usage Notes
HELP CONSTRAINT Examples
CHECK Constraint Attributes
Referential Constraint Attributes
UNIQUE Constraint Attributes
HELP ERROR TABLE
HELP ERROR TABLE Syntax
HELP ERROR TABLE Syntax Elements
HELP ERROR TABLE Usage Notes
HELP TABLE
HELP TABLE Syntax
HELP TABLE Syntax Elements
HELP TABLE Usage Notes
HELP TABLE Examples
Example: HELP TABLE
Example: HELP TABLE with Temporal Columns
Example: HELP TABLE with a JSON Auto Column
Example: HELP TABLE with Foreign Table
Example: HELP TABLE for Table in Datalake
HELP VOLATILE TABLE
HELP VOLATILE TABLE Syntax
HELP VOLATILE TABLE Syntax Elements
HELP VOLATILE TABLE Usage Notes
HELP VOLATILE TABLE Example
Volatile Table Attributes
SHOW TABLE
SHOW TABLE Syntax
SHOW TABLE Syntax Elements
SHOW TABLE Usage Notes
SHOW TABLE Examples
Example: SHOW TABLE
Example: SHOW TABLE with Column-Level Named Constraints
Example: SHOW TABLE with REFERENCES Constraint
Example: SHOW TABLE for a Table Created without Specifying a MERGEBLOCKRATIO Option
Example: SHOW TABLE for a User with a Default Character Type of Latin
Example: SHOW TABLE for Algorithmic Compression
Example: SHOW TABLE for a Column-Partitioned Table or Join Index
Examples: SHOW TABLE in XML Format
Example: SHOW TABLE with Derived Period Column
Example: SHOW TABLE with Auto Column
View Statements
CREATE VIEW and REPLACE VIEW
CREATE VIEW and REPLACE VIEW Syntax
CREATE VIEW and REPLACE VIEW Syntax Elements
locking_clause
as_of_clause
nonrecursive_with_modifier
recursive_with_modifier
seed
union_specification
recursive
implicit_join
explicit_join
selection_in_view
source
search_condition
group_specification
having_condition
qualify_condition
WITH CHECK OPTION
order_by_specification
CREATE VIEW and REPLACE VIEW Usage Notes
General Usage Guidelines for CREATE VIEW and REPLACE VIEW
Rules and Restrictions for Creating, Replacing, and Using Views
Support for Global Temporary, Global Temporary Trace, and Volatile Tables
Updatable Views
Non-Updatable Views
Views and the LOCKING Request Modifier
Using Views to Control Access to Partitions of a PPI Table
View Definitions and Character Sets
CREATE VIEW and REPLACE VIEW Examples
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW Syntax
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW Syntax Elements
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW Usage Notes
The Concept of Recursion
General Usage Guidelines for Recursive Views
All Recursive View Definitions Must Be Linear
Building a Recursive View
Components Supported in a View Definition
Recursive View Definition Restrictions
Step Building Logic for Recursive Queries
Preventing Infinite Recursion Due to Cyclic Data
Preventing Infinite Recursion with Acyclic Data
Breadth-First and Depth-First Searches
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW Examples
Example: A Simple Recursive View
Example: Simple REPLACE RECURSIVE VIEW
Example: Controlling Infinite Recursion
Example: Aggregate and Ordered Analytic Function Usage
Example: Left Outer Join Usage
Example: Right Outer Join Usage
Example: Full Outer Join Usage
Example: Mutual Recursion
Example: RECURSIVE Specified for a Non-Recursive View
Example: GROUP BY Clause Usage
RENAME VIEW
RENAME VIEW Syntax
RENAME VIEW Syntax Elements
RENAME VIEW Usage Notes
RENAME VIEW Example
DROP VIEW
DROP VIEW Syntax
DROP VIEW Syntax Elements
Example: Dropping a View
HELP VIEW
HELP VIEW Syntax
HELP VIEW Syntax Elements
View Attributes
View Columns
Index Statements
CREATE INDEX
CREATE INDEX Syntax
CREATE INDEX Syntax Elements
CREATE INDEX Usage Notes
CREATE INDEX Locks and Concurrency
General Usage Guidelines with CREATE INDEX
Why Consecutive Indexes Are Important for Value-Ordered NUSIs
Relationships between Secondary Indexes and Primary Indexes or Primary AMP Indexes
Restrictions and Limitations with CREATE INDEX
CREATE INDEX Examples
Example: Creating Named Indexes
Example: Creating a Named Unique Secondary Index
Example: Creating a Named Nonunique Secondary Index
Example: Creating an Unnamed Unique Secondary Index
Example: Creating an Unnamed Nonunique Secondary Index
Example: Creating a Secondary Index on a Join Index
CREATE JOIN INDEX
CREATE JOIN INDEX Syntax
CREATE JOIN INDEX Syntax Elements
table_option (CREATE JOIN INDEX)
select_clause
selection
aggregation_clause
SUM(numeric_expression )
COUNT(value_expression)
MIN (value_expression)
MAX(value_expression)
EXTRACT FROM
AS expression_alias
Row Compression
Repeating Column Set for Join Index Row Compression
Fixed Column Set for Join Index Row Compression
Grouping Column-Partitioned Join Index Column Data
FROM source
WHERE search_condition
GROUP BY grouping_or_ordering_specification
ORDER BY grouping_or_ordering_specification
index (CREATE JOIN INDEX)
CREATE JOIN INDEX Usage Notes
Map Option Usage with CREATE JOIN INDEX
CREATE JOIN INDEX Locks and Concurrency
Working with Join Indexes
Partitioned Tables and Join Indexes
Distribution of Join Index Rows
Guidelines for Row-Partitioned PI Join Indexes
Choosing between a Row-Partitioned Join Index and a Value-Ordered NUSI for Covering Range Queries
Column-Partitioned Join Indexes
Simple Join Indexes
Aggregate Join Indexes
Sparse Join Indexes
Join Indexes, Expressions, and Built-In Functions
Collecting Statistics on Join Indexes
Collecting Statistics on a Single-Table Join Index
Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns
Guidelines for Collecting Statistics on Single-Table Join Index Columns
Collecting Statistics on Multitable Join Indexes
Compression of Join Index Column Values
Row Compression of Join Indexes
Physical Join Index Row Compression
CREATE JOIN INDEX Restrictions and Limitations
Rules and Restrictions for Join Indexes
Query Coverage by Join Indexes
Rules and Restrictions for Query Coverage in Join Indexes
CREATE JOIN INDEX Examples
Example: Creating and Using a Simple Join Index
Example: Creating and Using a Simple Row-Compressed Join Index
Example: Defining and Using a Simple Join Index with an n -way Join Result
Example: Using the EXTRACT Function with a Join Index Definition
Example: Creating and Using an Aggregate Join Index
Example: Creating an Aggregate Join Index Using the MIN and MAX Functions
Example: Creating a Join Index with a Single-Level Partitioned Primary Index
Example: SLPPI Join Index and Performance
Example: Join Index Coverage
Example: Exceptions to Join Index Coverage
Example: Creating a Join Index with a Partitioned PPI Using a CURRENT_DATE Function
Example: Creating a Join Index with a Period Column in the Select List
Example: Creating a Join Index with a BEGIN Bound Function in the Select List
Example: Creating a Join Index with a Multicolumn CASE Expression in the Select List
Example: Creating a Join Index with a P_INTERSECT Expression in the Select List
Example: Creating an Aggregate Join Index with a Nested Expression in the Select List
Example: Creating a Unique Join Index
Example: Creating a Join Index Using an Updatable CURRENT_DATE Function
Example: Altering the Partitioning of a Join Index Using ALTER TABLE TO CURRENT
Example: Creating a Join Index Defined on a Period Column
Example: Creating a Join Index with a BEGIN Expression in the Select List
Example: Creating a Join Index with a Character Partitioning Expression
Example: Partitioning Expression that Specifies an AT LOCAL Date
Example: Creating a Join Index for a Table with JSON Columns
Example: Creating Column-Partitioned Join Indexes
Example: Creating Column-Partitioned Sparse Join Index with Autocompression Based on the Default
Example: Creating a Join Index with a Sparse Map
CREATE JOIN INDEX Related Information
ALTER JOIN INDEX
ALTER JOIN INDEX Syntax
ALTER JOIN INDEX Syntax Elements
ALTER JOIN INDEX Usage Notes
DROP INDEX
DROP INDEX Syntax
DROP INDEX Syntax Elements
DROP INDEX Usage Notes
DROP INDEX Examples
DROP JOIN INDEX
DROP JOIN INDEX Syntax
DROP JOIN INDEX Syntax Elements
DROP JOIN INDEX Usage Notes
Example: Dropping a Join Index
HELP INDEX
HELP INDEX Syntax
HELP INDEX Syntax Elements
HELP INDEX Usage Notes
HELP INDEX Examples
HELP INDEX Attributes
HELP JOIN INDEX
HELP JOIN INDEX Syntax
HELP JOIN INDEX Syntax Elements
Example: HELP JOIN INDEX on a Join Index
Join Index Attributes
Authorization Statements for External Routines
CREATE AUTHORIZATION and REPLACE AUTHORIZATION
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Syntax
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Syntax Elements
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Usage Notes
Providing Security for User-Written External Routines
DEFINER, INVOKER, and System-wide Authorizations
Authorization Rules
Function of CREATE AUTHORIZATION Requests
Azure Service Principal Authorization
Assume Role Authorization
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Examples
Example: Creating a DEFINER Authorization
Example: Creating a DEFINER Authorization for an External Security Clause
Example: Creating an INVOKER Authorization by Default
Example: Creating an INVOKER Authorization Explicitly
Example: Using System-wide Authorizations
Example: Creating a Google Cloud Authorization
Example: Creating Authorization for Azure Key Vault
Example: Creating an AUTHORIZATION OBJECT for Use with NOS
Example: Creating an Azure Service Principal Authorization Object and Using It with READ_NOS
Example: Creating a Simplified Azure Service Principal Authorization Object and Use It with a Foreign Table
Example: Creating an Azure Service Principal INVOKER TRUSTED Authorization
Example: Creating a Azure Service Principal Authorization Object and Use It with WRITE_NOS
Example: Using AWS Assume Role Authorization with READ_NOS
Example: Using AWS Assume Role Authorization with WRITE_NOS
Example: Using AWS Assume Role Authorization with CREATE FOREIGN TABLE
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Related Information
DROP AUTHORIZATION
DROP AUTHORIZATION Syntax
DROP AUTHORIZATION Syntax Elements
Example: Dropping an Authorization Object
DROP AUTHORIZATION Related Information
Global and Persistent (GLOP) Data Statements
CREATE GLOP SET
CREATE GLOP SET Syntax
CREATE GLOP SET Syntax Elements
Example: Creating a GLOP Set
DROP GLOP SET
DROP GLOP SET Syntax
DROP GLOP SET Syntax Elements
Example: Dropping a GLOP Set
Procedure Statements
CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax (External Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax Elements (External Form)
CREATE PROCEDURE and REPLACE PROCEDURE Usage Notes (External Form)
General Usage Guidelines: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Usage Restrictions for External Procedures: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
When to Run External Procedures in Unprotected Mode: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Options and Clauses: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
SQL DATA ACCESS Options: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
LANGUAGE Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External Data Access Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
SQL SECURITY Privilege Options: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
PARAMETER STYLE Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Include Name Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Library Name Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Function of Object File Name Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Function of Package Name Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Specifying the CLI Option for the EXTERNAL NAME Package Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Source File Name Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
EXTERNAL SECURITY Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External Body Reference Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External SQL Procedures and SQL: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Using Java External Procedures: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Data Type Mapping between SQL and Java: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Dictionary Tables with Java External Routines and Procedures: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
SQLJ Database: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External Java Reference Strings: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External Body Reference Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
EXTERNAL NAME Clause: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External String Literal: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External String Literal Examples: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Client-Server External Procedure Code Specification: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External Procedure Default Location Paths: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
External Procedure and UDF .so Linkage Information: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
CREATE PROCEDURE and REPLACE PROCEDURE Examples (External Form)
Example: Creating a Procedure Using PARAMETER STYLE TD_GENERAL
Example: Creating a Procedure Using PARAMETER STYLE SQL
Example: Creating an External Procedure that Can Modify SQL Data Using CLIv2
Example: Creating a Java External Stored Procedure Using a Period Data Type
Example: Creating a Java External Stored Procedure Using a Data Type of ST_Geometry
Example: Creating a Java External Stored Procedure Using a DATASET Data Type
Example: Creating Java External SQL Procedures
CREATE PROCEDURE and REPLACE PROCEDURE Related Information (External Form)
CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax (SQL Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax Elements (SQL Form)
parameter_specification
statement
SQL_statement
SQL_multiple_statement_request
compound statement
open_statement
fetch_statement
assignment_statement
condition_statement
iteration_statement
diagnostic_statement
ITERATE label_name
LEAVE label_name
SQL_data_access
DYNAMIC RESULT SETS
privilege_option
local_declaration
cursor_declaration
cursor_specification
condition_handler
CREATE PROCEDURE and REPLACE PROCEDURE Usage Notes (SQL Form)
General Usage Guidelines with CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)
Rules for SQL Procedure Privileges
Privilege Violations during Procedure Compilation
Privilege Violations during Procedure Execution
SQL SECURITY Privilege Options: CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)
SQL Data Access Options Supported by CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)
Special Condition Handling for SIGNAL and RESIGNAL Statements
CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form) Dynamic Result Sets
Rules and Limitations for Dynamic Result Sets
DDL Statements in SQL Procedures
DML Statements in SQL Procedures
DCL Statements in SQL Procedures
SQL Multiple-Statement Request Support in SQL Procedures
SQL Procedure Options
Guidelines for Manipulating LOBs in an SQL Procedure
Procedure for Creating an SQL Procedure
Working with CREATE PROCEDURE and REPLACE PROCEDURE Requests
CREATE PROCEDURE and REPLACE PROCEDURE Examples (SQL Form)
Example: Creating a Procedure with Parameters of Different Data Types
Example: Specifying a SQL SECURITY—CREATOR
Example: Specifying a SQL SECURITY—DEFINER
Example: Specifying SQL SECURITY—INVOKER
Example: Specifying a SQL SECURITY—OWNER
Example: Passing the Value for a TOP n Operator to an SQL Procedure
Example: Creating a Procedure with Local Variable, Cursor, and Conditional Handler Declarations
Example: Creating SQL Procedures with Single Statement
Example: Using Nested Compound Statements in a Procedure
Example: Splitting a CLOB Value in Half and Inserting the Pieces into Two Different Tables
Example: Converting a CLOB Containing XML Data into Rows for Insertion into a Table
Example: Creating an SQL Procedure with a Multiple-Statement Request
Example: Creating a Procedure with Multiple-Statement Request Using a Dynamic SQL Call Statement
Example: Setting the Transaction Query Band Using a Parameter
Example: Creating a Procedure with Dynamic SQL Using the SQL PREPARE Statement
Example: Creating an SQL Procedure that Specifies Recursion
Example: Invoking an SQL UDF from an SQL Procedure
Example: Creating a Procedure with Parameters and Local Variables
Example: Creating a Procedure that Performs a Consume Mode SELECT
Example: Creating a Procedure that Consumes a Queue Table
CREATE PROCEDURE and REPLACE PROCEDURE Related Information (SQL Form)
ALTER PROCEDURE (External Form)
ALTER PROCEDURE Syntax (External Form)
ALTER PROCEDURE Syntax Elements (External Form)
procedure_name
LANGUAGE
COMPILE
AT TIME ZONE
EXECUTE PROTECTED
ALTER PROCEDURE Usage Notes (External Form)
General Usage Guidelines for ALTER PROCEDURE (External Form)
When to Specify Unprotected Mode (ALTER PROCEDURE External Form)
Protected Mode External Procedure Servers
ALTER PROCEDURE Examples (External Form)
ALTER PROCEDURE (SQL Form)
ALTER PROCEDURE Syntax (SQL Form)
ALTER PROCEDURE Syntax Elements (SQL Form)
ALTER PROCEDURE Usage Notes (SQL Form)
Examples: ALTER PROCEDURE (SQL Form)
RENAME PROCEDURE
RENAME PROCEDURE Syntax
RENAME PROCEDURE Syntax Elements
RENAME PROCEDURE Usage Notes
Example: Renaming a Procedure
DROP PROCEDURE
DROP PROCEDURE Syntax
DROP PROCEDURE Syntax Elements
Example: Dropping a Procedure
HELP PROCEDURE
HELP PROCEDURE Syntax
HELP PROCEDURE Syntax Elements
HELP PROCEDURE Examples
Example: Reporting ARRAY Parameters for a Procedure
Example: HELP PROCEDURE Attributes Report
Example: HELP PROCEDURE with UNICODE PASS THROUGH Set
Procedure Parameter Attributes
Procedure Attributes
Compute Cluster Statements
Compute Group Statements
CREATE COMPUTE GROUP
CREATE COMPUTE GROUP Syntax
CREATE COMPUTE GROUP Syntax Elements
CREATE COMPUTE GROUP Usage
CREATE COMPUTE GROUP Examples
MODIFY COMPUTE GROUP
MODIFY COMPUTE GROUP Syntax
MODIFY COMPUTE GROUP Syntax Elements
MODIFY COMPUTE GROUP Usage
MODIFY COMPUTE GROUP Example
COMMENT ON COMPUTE GROUP
COMMENT ON COMPUTE GROUP Syntax
COMMENT ON COMPUTE GROUP Syntax Elements
COMMENT ON COMPUTE GROUP Examples
DROP COMPUTE GROUP
DROP COMPUTE GROUP Syntax
DROP COMPUTE GROUP Syntax Elements
DROP COMPUTE GROUP Usage
DROP COMPUTE GROUP Examples
SHOW COMPUTE GROUP
SHOW COMPUTE GROUP Syntax
SHOW COMPUTE GROUP Syntax Elements
SHOW COMPUTE GROUP Usage
SHOW COMPUTE GROUP Examples
Compute Profile Statements
CREATE COMPUTE PROFILE
CREATE COMPUTE PROFILE Syntax
CREATE COMPUTE PROFILE Syntax Elements
CREATE COMPUTE PROFILE Usage
CREATE COMPUTE PROFILE Examples
MODIFY COMPUTE PROFILE
MODIFY COMPUTE PROFILE Syntax
MODIFY COMPUTE PROFILE Syntax Elements
MODIFY COMPUTE PROFILE Usage
MODIFY COMPUTE PROFILE Examples
COMMENT ON COMPUTE PROFILE
COMMENT ON COMPUTE PROFILE Syntax
COMMENT ON COMPUTE PROFILE Syntax Elements
COMMENT ON COMPUTE PROFILE Usage
COMMENT ON COMPUTE PROFILE Examples
DROP COMPUTE PROFILE
DROP COMPUTE PROFILE Syntax
DROP COMPUTE PROFILE Syntax Elements
DROP COMPUTE PROFILE Usage
DROP COMPUTE PROFILE Examples
SHOW COMPUTE PROFILE
SHOW COMPUTE PROFILE Syntax
SHOW COMPUTE PROFILE Syntax Elements
SHOW COMPUTE PROFILE Usage
SHOW COMPUTE PROFILE Examples
COMPUTE GROUP Built-in Function
COMPUTE GROUP and COMPUTE PROFILE Status Views
Datalake Statements
Authorization Objects
CREATE DATALAKE and REPLACE DATALAKE
CREATE DATALAKE and REPLACE DATALAKE Syntax
CREATE DATALAKE and REPLACE DATALAKE Syntax Elements
Example: Create Authorizations and DATALAKE Object
Example: Create DATALAKE Object with HIVE Catalog
Example: Replace DATALAKE Object
Example: Create DATALAKE Object with GLUE Catalog
Example: Create DATALAKE Object with UNITY Catalog
Example: Create DATALAKE Object in Delta Lake Format
Example: Create DATALAKE object for Glue Catalog with catalog_endpoint and storage_endpoint
Example: Create DATALAKE object for Hive Catalog with storage_endpoint clause
Example: Create AUTHORIZATION and DATALAKE Statement for Iceberg read and write on GCP with Hive Catalog
Example: Create AUTHORIZATION and DATALAKE statement for DeltaLake read and write on
Example: Create AUTHORIZATION and DATALAKE statement for Iceberg read and write on GCP with Unity Catalog
ALTER DATALAKE
ALTER DATALAKE Syntax
ALTER DATALAKE Syntax Elements
Example: ALTER DATALAKE
COMMENT ON DATALAKE
COMMENT ON DATALAKE Syntax
COMMENT ON DATALAKE Syntax Elements
Example: COMMENT ON DATALAKE
DROP DATALAKE
DROP DATALAKE Syntax
DROP DATALAKE Syntax Elements
Example: DROP DATALAKE
SHOW DATALAKE
SHOW DATALAKE Syntax
SHOW DATALAKE Syntax Elements
Example: SHOW DATALAKE
HELP DATALAKE
HELP DATALAKE Syntax
HELP DATALAKE Syntax Elements
Example: HELP DATALAKE
Datalake Table References
Macro Statements
CREATE MACRO and REPLACE MACRO
CREATE MACRO and REPLACE MACRO Syntax
CREATE MACRO and REPLACE MACRO Syntax Elements
CREATE MACRO and REPLACE MACRO Usage Notes
General Usage Guidelines with CREATE MACRO and REPLACE MACRO
Running a Macro
Resolution of Non-Qualified Names
Semantic Errors May Not Reported When a Macro Is Created
Macro Definitions and Server Character Sets
Types of Macro Support
CREATE MACRO and REPLACE MACRO Examples
CREATE MACRO and REPLACE MACRO Related Information
RENAME MACRO
RENAME MACRO Syntax
RENAME MACRO Syntax Elements
RENAME MACRO Example
DROP MACRO
DROP MACRO Syntax
DROP MACRO Syntax Elements
DROP MACRO Usage Notes
HELP MACRO
HELP MACRO Syntax
HELP MACRO Syntax Elements
Example: HELP MACRO
Macro Attributes
User-Defined Function Statements
CREATE FUNCTION and REPLACE FUNCTION (SQL Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax (SQL Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (SQL Form)
RETURN Statement
RETURN Statement Syntax
RETURN Statement Syntax Elements
RETURN Statement Usage Notes
RETURN Statement Examples
CREATE/REPLACE FUNCTION Usage Notes (SQL Form)
General Usage Guidelines for CREATE/REPLACE FUNCTION (SQL Form)
Rules for Using SQL UDFs
Using Functions with CREATE/REPLACE FUNCTION (SQL Form)
Parameter Names and Data Types in CREATE FUNCTION and REPLACE FUNCTION (SQL Form)
Using Clauses with UDTs or UDFs
CREATE FUNCTION and REPLACE FUNCTION Examples (SQL Form)
Example: Defining an SQL UDF with a LOB Parameter
Example: Defining an SQL UDF with a Function in the RETURN Statement
Example: Defining an SQL UDF with a DateTime Expression in the RETURN Statement
Example: Defining an SQL UDF with a Period Function in the RETURN Statement
Example: Defining an SQL UDF with an External UDF in the RETURN Statement
Example: Defining an SQL UDF with a Geospatial Data Type in the RETURNS Clause and RETURN Statement
Example: Creating a Function with Geospatial Input Arguments
Example: Invoking an SQL UDF as an Argument to an External UDF
CREATE FUNCTION and REPLACE FUNCTION (Table Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax (Table Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (Table Form)
CREATE FUNCTION and REPLACE FUNCTION Usage Notes (Table Form)
Table Operators and Table Functions
General Usage Guidelines for CREATE FUNCTION and REPLACE FUNCTION (Table Form)
Use of Table Functions
Using Clauses with Table UDFs
CREATE FUNCTION Examples
Example: Creating a Function from Input Arguments
Example: Creating and Using an XML Function
Example: Constant Reference Table Function
Example: Variable Reference Table Function
Example: Variable Reference Table Function Called from a Derived Table
Example: Different LOB Specifications for Parameter and RETURNS TABLE Clauses
Example: Dynamic Result Row Specification
Example: Java Table UDF
Example: Table Operator Function Written in SAS
Example: Ordering Input Parameters to a Table Function
Example: Using a Table Function to Pass Data between Two Vantage Systems
CREATE FUNCTION Related Information (Table Form)
CREATE FUNCTION and REPLACE FUNCTION (External Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax (External Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (External Form)
CREATE FUNCTION and REPLACE FUNCTION Usage Notes (External Form)
Avoiding Name Clashes among UDFs
UDF Default Location Paths
General Usage Guidelines: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Using Compression Methods with CREATE FUNCTION
Function Identifiers: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Function Name: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Usage Restrictions for User-Defined Functions: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Function Calling Argument: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Function Overloading: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Parameter Names and Data Types: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Row-Level Security UDF Enforcement of the Security Policy for a Row
General Rules for Creating a Row-Level Security UDF
General Rules for Row-Level Security Function Parameter Styles
Rules for INSERT and UPDATE Row-Level Security Policy Functions
Rules for SELECT and DELETE Row-Level Security Policy Functions
Optimizing the Aggregate Cache
Using Clauses: CREATE FUNCTION and REPLACE FUNCTION (External Form)
RETURNS Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
SQL Data Access Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Optional Function Characteristics: CREATE FUNCTION and REPLACE FUNCTION (External Form)
SPECIFIC Function Name Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Function Class Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Parameter Style Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Deterministic Characteristics Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Null Call Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
External Data Access Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
External Body Reference Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Function Entry Name Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Include Name Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Library Name Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Object File Name Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Package Name Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
Source File Name Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
EXTERNAL NAME Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
External Security Clause: CREATE FUNCTION and REPLACE FUNCTION (External Form)
External String Literal: CREATE FUNCTION and REPLACE FUNCTION (External Form)
External String Literal Examples: CREATE FUNCTION and REPLACE FUNCTION (External Form)
CREATE FUNCTION and REPLACE FUNCTION Examples (External Form)
Example: Creating a UDF C Function
Example: Parsing an XML Document Using a Scalar UDF
Example: Using a UDF to Write a Message to an External Queue
Example: Retrieving Rows from the Data Dictionary and Writing Them to an External Queue on a Client System
Example: Creating and Using an EXTERNAL SECURITY Clause in a UDF
Example: Creating Functions that Algorithmically Compress LOB-Related Data
Example: Creating Functions that Decompress Algorithmically Compressed LOB-Related Data
Example: Using the TD_ANYTYPE Parameter and RETURNS Clause Data Type in a UDF Definition
Example: Creating a Java Scalar Function
Example: Creating a Java Aggregate Function
Example: Using a One-Dimensional ARRAY in a Parameter Definition
Example: Using a Multidimensional ARRAY in a Parameter Definition
Example: Creating a Row-Level Security Function
Example: Creating a Simple Constraint UDF to Enforce No Read Up Row-Level Security
CREATE FUNCTION and REPLACE FUNCTION Related Information (External Form)
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING Syntax
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING Syntax Elements
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING Examples
Example: Defining a Function Mapping
Example: Replacing a Function Mapping Definition
Example: Function Mapping Definition for a Function in the Database
Example: Function Mapping Definition with IN TABLE, OUT TABLE, and Parameters Options
Example: Function Mapping Definition with IN TABLE and Parameter Options
Example: Function Mapping Definition that Includes a Variable with a Default Value
Example: Function Mapping Definition with Multiple Variable Substitutions
Example: Function Mapping with the ANY IN TABLE Option
Example: Function Mapping with Multiple ANY IN TABLE Clauses
Example: Function Mapping with (ANY) IN TABLE Clause Substitution
Example: Function Mapping Definition with IN TABLE Options
Example: Function Mapping Definition with IN TABLE Clause Substitution
Example: Function Mapping Definition with a Substitution in the OUT TABLE Clause
Example: Function Mapping Definition with Scalar Subquery (SSQ) Substitution
Example: Function Mapping with Scalar Subquery (SSQ) Substitution that Includes a System Variable
Example: Function Mapping with Concatenated Variable Substitution
Example: Function Mapping for WRITE_NOS
ALTER FUNCTION
ALTER FUNCTION Syntax
ALTER FUNCTION Syntax Elements
ALTER FUNCTION Usage Notes
When to Specify Unprotected Mode (ALTER FUNCTION External Form)
Special Considerations for Java UDFs
UDF Servers
Recompiling and Redistributing an Existing Function
ALTER FUNCTION Examples
DROP FUNCTION
DROP FUNCTION Syntax
DROP FUNCTION Syntax Elements
DROP FUNCTION Usage Notes
DROP FUNCTION Examples
DROP FUNCTION Related Information
DROP FUNCTION MAPPING
DROP FUNCTION MAPPING Syntax
DROP FUNCTION MAPPING Syntax Elements
Example: Dropping a Function Mapping
HELP FUNCTION
HELP FUNCTION Syntax
HELP FUNCTION Syntax Elements
HELP FUNCTION Examples
Example: HELP FUNCTION Report
Example: HELP FUNCTION Report for a UDF
Example: TD_ANYTYPE Input and Output Parameter Data Type
User-Defined Function Attributes
RENAME FUNCTION (SQL Form)
RENAME FUNCTION Syntax (SQL Form)
RENAME FUNCTION Syntax Elements (SQL Form)
RENAME FUNCTION Usage Notes (SQL Form)
RENAME FUNCTION Examples (SQL Form)
RENAME FUNCTION (External Form)
RENAME FUNCTION Syntax (External Form)
RENAME FUNCTION Syntax Elements (External Form)
RENAME FUNCTION Usage Notes (External Form)
RENAME FUNCTION Examples (External Form)
Example: Renaming the Specific Name for a Function
Example: Renaming the Overloaded Calling Name for a Function
SHOW FUNCTION MAPPING
SHOW FUNCTION MAPPING Syntax
SHOW FUNCTION MAPPING Syntax Elements
SHOW FUNCTION MAPPING Examples
Example: Showing a Function Mapping Definition
Example: Showing the Definition of a Function Mapping in XML Format
Database Statements
CREATE DATABASE
CREATE DATABASE Syntax
CREATE DATABASE Syntax Elements
CREATE DATABASE Usage Notes
CREATE DATABASE Examples
Example: Creating a New Database
Example: Using a Constant Expression to Specify the PERM, SPOOL, and TEMPORARY Space for a Database
Example: Creating a Database with a Permanent Space Skew Limit
Example: Creating a Database with a Spool Space Skew Limit
Example: Creating a Database with a Temporary Space Skew Limit
Example: Creating a Database with Fallback and Journaling
Example: Creating a Database with Default Storage
Example: Marking a Database as Being Under AUTODBA Control
CREATE DATABASE (Datalake Form)
CREATE DATABASE Syntax (Datalake Form)
CREATE DATABASE Syntax Elements (Datalake Form)
Example: CREATE DATABASE (Datalake Form)
DELETE DATABASE
DELETE DATABASE Syntax
DELETE DATABASE Syntax Elements
DELETE DATABASE Usage Notes
Example: Deleting a Database
MODIFY DATABASE
MODIFY DATABASE Syntax
MODIFY DATABASE Syntax Elements
MODIFY DATABASE Usage Notes
MODIFY DATABASE Examples
Example: Change Permanent Space Allocation
Example: Use Constant Expression to Modify PERM, SPOOL, and TEMPORARY Database Spaces
Example: Add Permanent Space Skew Limit to Database
Example: Add Temporary Space Skew Limit to Database
Example: Add Spool Space Skew Limit to Database
Example: Change Fallback Protection and Space Allocation
Example: Change Default Journal Table
Example: Drop Default Journal Table
Example: Define New Default Journal Table
Example: Removing a Database Override Option
DATABASE
DATABASE Syntax
DATABASE Syntax Elements
DATABASE Usage Notes
Example: Defining Default Database for Current Session
DROP DATABASE
DROP DATABASE Syntax
DROP DATABASE Syntax Elements
DROP DATABASE Usage Notes
Example: Dropping Empty Database
DROP DATABASE (Datalake Form)
DROP DATABASE Syntax (Datalake Form)
DROP DATABASE Syntax Elements (Datalake Form)
Example: DROP DATABASE (Datalake Form)
HELP DATABASE
HELP DATABASE Syntax
HELP DATABASE Syntax Elements
HELP DATABASE Examples
Example: HELP DATABASE
Example: Output with a UIF
Example: HELP DATABASE for Database in Datalake
HELP DATABASE Attributes
LOGGING INCREMENTAL ARCHIVE ON FOR object_list
LOGGING INCREMENTAL ARCHIVE ON FOR object_list Syntax
LOGGING INCREMENTAL ARCHIVE ON FOR object_list Syntax Elements
Examples: Enable Incremental Restore
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list Syntax
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list Syntax Elements
Examples: Disabling Incremental Restore
INCREMENTAL RESTORE ALLOW WRITE FOR object_list
INCREMENTAL RESTORE ALLOW WRITE FOR object_list Syntax
INCREMENTAL RESTORE ALLOW WRITE FOR object_list Syntax Elements
Examples: Enable Read and Write Access after an Incremental Restore
User, Profile, and Role Statements
CREATE PROFILE
CREATE PROFILE Syntax
CREATE PROFILE Syntax Elements
CREATE PROFILE Usage Notes
Effects of Profile-Based Password Controls
Working with the SPECCHAR Password Control
Specifying the Single-Letter Code to Define SpecChar Rules
Default Value
Effects of Object Naming on SPECCHAR Controls
AUTODBA Option
CREATE PROFILE Examples
Example: Creating Profile that Defines Default Database
Example: Using Constant Expression to Specify SPOOL Space for Profile
Example: Modify Profile Using Constant Expression to Specify TEMPORARY Space
Example: Creating Profile that Defines User-Level Password Security Attribute
Example: Set Profile Query Band Pairs as Default
Example: Profile Query Band Pairs Are Not Default
Example: Creating Profile that Ignores pair-name with Any pair-value
Example: Assigning Row-Level Security Constraint Classifications to Profile
Example: Creating a Profile with Default Storage
MODIFY PROFILE
MODIFY PROFILE Syntax
MODIFY PROFILE Syntax Elements
MODIFY PROFILE Usage Notes
MODIFY PROFILE Examples
Example: Modify Profile Spool Space
Example: Using a Constant Expression to Specify the SPOOL Space for a Profile
Example: Create Profile Using Constant Expression to Specify TEMPORARY Space
Examples: QUERY_BAND
Example: Removing a Profile Override Option
CREATE ROLE
CREATE ROLE Syntax
CREATE ROLE Syntax Elements
Example: Creating an External Role
CREATE USER
CREATE USER Syntax
CREATE USER Syntax Elements
CREATE USER Usage Notes
How Users and Databases Differ
General Usage Guidelines for CREATE USER
Using Privileges with CREATE USER
PERMANENT, SPOOL, and TEMPORARY Space Supported by CREATE USER
STARTUP String
CREATE USER, Journal Tables, and Default Map
Local Journaling in CREATE USER
Activating Permanent Journaling (CREATE USER)
Using Collation with CREATE USER
Valid Export Width Specifications
Compatibility Mode
Expected Mode
Maximum Mode
DEFAULT Mode
Effects of Shift-Out and Shift-In Characters on Export Widths in CREATE USER
CREATE USER Examples
Example: Creating an Administrative User
Example: Using Constant Expression to Specify PERM, SPOOL, and TEMPORARY Space for User
Example: Creating User with Permanent Space Skew Limit
Example: Creating User with Temporary Space Skew Limit
Example: Creating User with Spool Space Skew Limit
Example: Creating User Using TIME ZONE='time_zone_string'
Example: Creating Users with Row-Level Security Constraints
Example: Creating New User with EXPECTED Export Width Table Name
Example: Creating a User with Default Storage
MODIFY USER
MODIFY USER Syntax
MODIFY USER Syntax Elements
MODIFY USER Usage Notes
General Usage Guidelines for MODIFY USER
Using Options with MODIFY USER
MODIFY USER, Journal Tables, and Default Map
Local Journaling in MODIFY USER
Activating Permanent Journaling (MODIFY USER)
Valid Export Width Specifications
Compatibility Mode
Expected Mode
Maximum Mode
DEFAULT Mode
Effects of Shift-Out and Shift-In Characters on Export Widths in MODIFY USER
MODIFY USER Examples
Example: Modifying Permanent Space Allocation for User
Example: Modifying PERM, SPOOL, and TEMPORARY Space Using Constant Expression
Example: Modifying User to Add Permanent Space Skew Limit
Example: Modifying Password, Spool Allocation, Startup String, and Default Database
Example: Modifying User to Add Temporary Space Skew Limit
Example: Modifying User to Add Spool Space Skew Limit
Example: Modifying Default Database and Password for User
Example: Modifying Default Journal Table for User
Example: Changing Default Journal Table
Example: Modifying Time Zone Displacement with TIME ZONE LOCAL Option
Example: Modifying Time Zone Displacement with TIME ZONE time_zone_string Option
Example: Assigning Profile to User with PROFILE Option
Example: Adding or Dropping Security Constraint Assignments in MODIFY USER Statement
Example: Removing a User Override Option
SET ROLE
SET ROLE Syntax
SET ROLE Syntax Elements
SET ROLE Examples
DELETE USER
DELETE USER Syntax
DELETE USER Syntax Elements
DELETE USER Usage Notes
Example: Deleting Database Objects from User
DROP PROFILE
DROP PROFILE Syntax
DROP PROFILE Usage Notes
Example: Dropping a Profile
DROP ROLE
DROP ROLE Syntax
DROP ROLE Syntax Elements
DROP ROLE Examples
DROP USER
DROP USER Syntax
DROP USER Syntax Elements
DROP USER Usage Notes
DROP USER Examples
HELP USER
HELP USER Syntax
HELP USER Syntax Elements
HELP USER Example
HELP USER Attributes
Map Statements
CREATE MAP
CREATE MAP Syntax
CREATE MAP Syntax Elements
CREATE MAP Usage Notes
CREATE MAP Example
DROP MAP
DROP MAP Syntax
DROP MAP Syntax Elements
DROP MAP Example
SHOW MAP
SHOW MAP Syntax
SHOW MAP Syntax Elements
SHOW MAP Examples
Session Statements
SET SESSION
SET SESSION Syntax
SET SESSION Usage Notes
SET SESSION ACCOUNT
SET SESSION ACCOUNT Syntax
SET SESSION ACCOUNT Syntax Elements
Examples: SET SESSION ACCOUNT
SET SESSION CALENDAR
SET SESSION CALENDAR Syntax
SET SESSION CALENDAR Syntax Elements
SET SESSION CALENDAR Usage Notes
SET SESSION CALENDAR Examples
SET SESSION CHARACTER SET UNICODE PASS THROUGH
SET SESSION CHARACTER SET UNICODE PASS THROUGH Syntax
SET SESSION CHARACTER SET UNICODE PASS THROUGH Syntax Elements
Example: Enabling UNICODE PASS THROUGH for the Session
SET SESSION COLLATION
SET SESSION COLLATION Syntax
SET SESSION COLLATION Syntax Elements
SET SESSION COLLATION Usage Notes
SET SESSION COLLATION Examples
Example: Setting a Session to Use the ASCII Collation Sequence
Example: Effect of the ASCII Collation Sequence on SQL Report Output
Example: Setting a Session to Use the EBCDIC Collation Sequence
Example: Effect of the EBCDIC Collation Sequence on SQL Report Output
SET SESSION COMPUTE GROUP
SET SESSION COMPUTE GROUP Syntax
SET SESSION COMPUTE GROUP Syntax Elements
Example: Show Session Compute Group
SET SESSION CONSTRAINT
SET SESSION CONSTRAINT Syntax
SET SESSION CONSTRAINT Syntax Elements
SET SESSION CONSTRAINT Usage Notes
Rules for Determining Whether Profile Defaults or User Defaults Are Assigned as Session Row-Level Security Defaults
Using SET SESSION CONSTRAINT to Modify the Constraints Assigned to You for the Current Session
Using the NULL Option for SET SESSION CONSTRAINT
Determining the Session Constraint Values
Session Constraint Values for Permanent Database Users
Session Constraint Values for Directory Users
Session Constraint Values for Application Pooled Users
Session Constraint Values for Trusted User Applications and Proxy Users
Session Constraint Values in OVERRIDE Sessions
Specifying Non-Hierarchical Constraint Values when Loading Tables
Using SET SESSION to Change the Session Security Constraint Value
Using HELP SESSION to Investigate Session Constraint Values
SET SESSION CONSTRAINT Examples
Constraint Definitions for the Examples
Example: Changing the Security Level and Category for a Session
Example: Changing the Row-Level Security Level for a Session
Example: Changing the Row-Level Security Category for a Session
Example: Loading Tables without User OVERRIDE Privileges
Example: Loading Tables with User OVERRIDE Privileges
SET SESSION DATABASE
SET SESSION DATABASE Syntax
SET SESSION DATABASE Syntax Elements
SET SESSION DATABASE Usage Notes
Example: SET SESSION DATABASE
SET SESSION DATEFORM
SET SESSION DATEFORM Syntax
SET SESSION DATEFORM Syntax Elements
SET SESSION DATEFORM Usage Notes
SET SESSION DATEFORM Examples
SET SESSION DOT NOTATION
SET SESSION DOT NOTATION Syntax
SET SESSION DOT NOTATION Syntax Elements
Example: Default Response for 15.0 and 15.10 Dot Notation
SET SESSION FUNCTION TRACE
SET SESSION FUNCTION TRACE Syntax
SET SESSION FUNCTION TRACE Syntax Elements
SET SESSION FUNCTION TRACE Usage Notes
SET SESSION FUNCTION TRACE Example
SET SESSION JSON IGNORE ERRORS
SET SESSION JSON IGNORE ERRORS Syntax
SET SESSION JSON IGNORE ERRORS Syntax Elements
Example: Disabling JSON Data Validation
SET SESSION SEARCHUIFDBPATH
SET SESSION SEARCHUIFDBPATH Syntax
SET SESSION SEARCHUIFDBPATH Syntax Elements
Example: Setting the Database UIF Search Path
SET SESSION TRANSACTION ISOLATION LEVEL
SET SESSION TRANSACTION ISOLATION LEVEL Syntax
SET SESSION TRANSACTION ISOLATION LEVEL Syntax Elements
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL Usage Notes
Definition of Isolation Level
Default Session Transaction Isolation Level
Why Default to ACCESS Locks for All SELECT Operations Embedded in DELETE, INSERT, and UPDATE Requests in a Session?
Correspondence between ANSI SQL Transaction Isolation Levels and Database Locks
SET SESSION TRANSACTION ISOLATION LEVEL Examples
SET QUERY_BAND
SET QUERY_BAND Syntax
SET QUERY_BAND Syntax Elements
SET QUERY_BAND Usage Notes
Function of Query Bands
General Usage Guidelines with SET QUERY_BAND
Query Bands and Session-Level Performance
Query Bands and Trusted Sessions
Query Bands, Trusted Sessions, and Row-Level Security
Query Bands, Trusted Sessions, and Connection Pooling
Using Trusted Sessions with Requests & Roles
Query Bands, Trusted Sessions, and Roles
Query Bands and External Routines
Query Bands and Database Query Log
Query Bands and Load Utilities
Load Utility Reserved Query Bands
BlockCompression Reserved Storage Management Query Bands
Reserved Storage Management Query Bands
Reserved Redrive Query Band
Incremental Planning and Execution (IPE) Query Bands
Query Banding and Middle Tier Applications
Query Banding for Pooled Sessions
Query Banding for Trusted Sessions
Using SET QUERY_BAND Requests to Make Proxy User Connections
Discretionary Access Control Security Issues with Trusted Sessions
SET QUERY_BAND Examples
Example: Setting a Query Band for a Session
Example: Making a Proxy User Connection
Example: Setting the Role for a Trusted Session Using a PROXYROLE name:value Pair
Example: Using the BLOCKCOMPRESSION Reserved Query Band
Example: Setting a Query Band that Includes the EQUALS SIGN Character in the Value
Example: Removing a Query Band from a Session
Example: Query Band UPDATE Examples
Example: Setting a Query Band Using FOR SESSION VOLATILE
Example: Using UPDATE with a FOR SESSION VOLATILE Query Band
Example: Setting a Query Band for the Current Transaction
Example: Removing a Query Band from the Current Transaction
Example: SET QUERY_BAND FOR TRANSACTION as Parameterized JDBC Request
Example: Preventing an Injected SET QUERY_BAND from Changing the Proxy User
Example: Initiating a Trusted Session Using a Transaction Query Band
Example: Setting a Query Band for the Current Transaction for a Multiple-Statement Request in a Trusted Session
SET QUERY_BAND Related Information
SET TIME ZONE
SET TIME ZONE Syntax
SET TIME ZONE Syntax Elements
SET TIME ZONE Usage Notes
General Usage Guidelines for SET TIME ZONE
Rules and Restrictions for Specifying Simple Expressions for Time Zones
Daylight Saving Time and Time Zone Strings Specified as Time Zone Strings
Time Zone Strings
SET TIME ZONE Examples
SET SESSION UDFSEARCHPATH
SET SESSION UDFSEARCHPATH Syntax
SET SESSION UDFSEARCHPATH Syntax Elements
Example: SET SESSION UDFSEARCHPATH
HELP SESSION
HELP SESSION Syntax
HELP SESSION Syntax Elements
HELP SESSION Usage Notes
HELP SESSION Examples
Example: HELP SESSION for the Current User
Example: HELP SESSION for a KanjiEBCDIC User
Example: HELP SESSION for Session Attributes
Example: Row-Level Security Constraints Only
Example: Additional Object Name and Title Fields Returned with HELP SESSION
Example: Show SEARCHUIFDBPATH
Example: HELP SESSION with UNICODE PASS THROUGH Set
HELP SESSION Request Attributes
HELP SESSION CONSTRAINT
Logging Statements
BEGIN LOGGING
BEGIN LOGGING Syntax
BEGIN LOGGING Syntax Elements
BEGIN LOGGING Usage Notes
Logging
Logging of External Users
Determining Object Level for BEGIN LOGGING Request
Requirements for Logging Certain Operations and Objects in Combination
Logging MODIFY Statements
Logging Self-Referent MODIFY USER Statements
BEGIN LOGGING Examples
Example: Using BEGIN LOGGING
Example: Column-Level Discretionary Access Control and Row-Level Security Audit Logging
Example: Logging Denials for a Row-Level Security Constraint
Example: Logging the Denied First Insert into a Row-Level Security-Secured Table
Example: Logging Denial of Attempt to Archive Row-Level Security-Secured Table by Unauthorized User
BEGIN QUERY CAPTURE
BEGIN QUERY CAPTURE Syntax
BEGIN QUERY CAPTURE Syntax Elements
BEGIN QUERY CAPTURE Usage Notes
BEGIN QUERY CAPTURE Examples
Example: Using the Default TDQCD Database to Capture and Store a Query Plan in XML Format
Example: Capturing a Mix of DML and DDL Requests in XML Format Using BEGIN QUERY CAPTURE
BEGIN QUERY LOGGING
BEGIN QUERY LOGGING Syntax
BEGIN QUERY LOGGING Syntax Elements
BEGIN QUERY LOGGING Usage Notes
General Usage Guidelines for BEGIN QUERY LOGGING
Logging Options for BEGIN QUERY LOGGING
Limit Options for BEGIN QUERY LOGGING
Hierarchy of Applying Database Query Logging Rules
Database Query Log Tables and Views
Query Logging User Rules
Using BEGIN QUERY LOGGING to Log Query Plan Information
Using SUMMARY Query Logging for Tactical Queries
Diagnostic and Extended Logging Capabilities
BEGIN QUERY LOGGING Examples
Example: Logging All Users for All Accounts
Example: Logging Up to 1,000 SQL Text Characters in DBQLogTbl
Example: Logging Specific Users
Example: Handling Short Queries
Example: Logging All Users Who Are Logged On under Specific Accounts
Example: Logging Requests by Summary Completion Time in Full-Second Intervals
Example: Logging Requests by Summary Completion Time in Subsecond Intervals
Example: Short Requests Based on CPU Time
Example: Short Requests Based on I/O Counts
Example: Logging Requests by Threshold-Normalized CPU Times
Example: Logging Requests on All Users for the Default Table
Example: Disabling Query Logging for MultiLoad Jobs
Example: Logging Requests except for a Single User and Account
Example: Invoking and Disabling Query Logging for a Rule Set
Example: Fine-Tuning Query Logging Rules
Example: Logging Plans as XML Text
Example: Logging Plans as XML Text for Detailed Statistics
Example: Logging Verbose EXPLAIN as XML Text
Example: Logging Statistics Usage for All Requests by a User
Example: Logging an XML Plan with VERBOSE EXPLAIN and DETAILED STATSUSAGE Collections
Example: Logging an XML Plan with VERBOSE EXPLAIN and DETAILED STATSUSAGE Collections in the Same Request
Example: Logging Object Use Counts on a Specified Database
Example: Logging Object Use Counts on a Mix of Databases and Users
BEGIN QUERY LOGGING Related Information
FLUSH QUERY LOGGING
FLUSH QUERY LOGGING Syntax
FLUSH QUERY LOGGING Syntax Elements
FLUSH QUERY LOGGING Usage Notes
FLUSH QUERY LOGGING Examples
REPLACE QUERY LOGGING
REPLACE QUERY LOGGING Syntax
REPLACE QUERY LOGGING Syntax Elements
REPLACE QUERY LOGGING Usage Notes
END LOGGING
END LOGGING Syntax
END LOGGING Syntax Elements
END QUERY CAPTURE
END QUERY CAPTURE Syntax
END QUERY CAPTURE Usage Notes
END QUERY LOGGING
END QUERY LOGGING Syntax
END QUERY LOGGING Syntax Elements
END QUERY LOGGING Usage Notes
END QUERY LOGGING Examples
Example: Removing the ALL Rule from the Rule Cache and DBC.DBQLRulesTbl
Example: Remove All DBQL Rules in Effect from DBC.DBQLRulesTbl
Example: Specify Account Explicitly to Discontinue Request Logging
Example: Removing the Rule for user_1 from the Rule Cache and DBC.DBQLRulesTbl
Example: Removing the Rule for user_1 and user_2 from the Rule Cache and DBC.DBQLRulesTbl
SHOW QUERY LOGGING
SHOW QUERY LOGGING Syntax
SHOW QUERY LOGGING Syntax Elements
SHOW QUERY LOGGING Usage Notes
SHOW QUERY LOGGING Examples
Example: SHOW QUERY LOGGING for Simple Conditions
Example: Report DBQL Rule that Logs All Users for Any Account
Example: Report DBQL Rule that Logs Specific User for Account 'hr'
Example: Report DBQL Rule that Logs Specific User for Account 'marketing'
Example: Report DBQL Rule that Logs Specific User for Account 'finance'
Example: Report DBQL Rule that Logs user1 for All Accounts
Example: Report the DBQL Logging Rule for the MultiLoad Utility
Example: Searching Multiple Levels of the DBQL Rules Hierarchy
Example: Report DBQL Rule that Logs All Users for All Accounts Using Default Logging
Example: Report DBQL Rule that Logs user2 for All Accounts
Example: Report DBQL Rule that Logs Specific User for Any Account
Example: Report DBQL Rule that Logs All Queries for MultiLoad Utility
Example: Report DBQL Rule that Shows SQL, StepInfo, and FeatureInfo Options Enabled
Examples: Report Detailed Logging Data
Example: Report Options for a User when System-Level Logging is Enabled and User-Level Rule is in Place
Statistics Statements
COLLECT STATISTICS (Optimizer Form)
COLLECT STATISTICS Syntax (Optimizer Form)
COLLECT STATISTICS Syntax Elements (Optimizer Form)
COLLECT STATISTICS Usage Notes (Optimizer Form)
General Usage Guidelines for COLLECT STATISTICS (Optimizer Form)
Keep Statistics Current
Reducing the Cost of Collecting Statistics by Sampling
Comparison of Full and Sampled Statistics
Using the THRESHOLD Options to Collect and Recollect Statistics
USING Options
Rules and Guidelines for COLLECT STATISTICS (Optimizer Form)
Rules and Guidelines for the COLLECT STATISTICS (Optimizer Form) FROM source_table Clause
COLUMN and INDEX Options to Collect Statistics
Collecting Statistics on Multiple Columns
Collecting Statistics on the PARTITION Column and the Row Partitioning Column Set
COLLECT STATISTICS Examples (Optimizer Form)
Example: Collecting Full Statistics
Example: Collecting Sampled Statistics
Example: Collecting Statistics on 10% of Orders
Example: Collecting SUMMARY Statistics
Example: Recollecting Statistics without Specifying Thresholds
Example: Simple Recollect Statistics Request Using the THRESHOLD Option
Example: Using the THRESHOLD Option to Recollect Statistics
Example: Collecting Statistics on Multiple Columns
Example: Collecting Single-Column PARTITION Statistics
Example: Collecting Multicolumn PARTITION Statistics
Example: Collecting Statistics on a Single-Column with an Expression
Example: Collecting Statistics on Multiple Columns with an Expression
Example: Collecting Statistics on a Constant Expression
Example: Collecting Statistics on Expressions Using Statistics Names
Example: Collecting Statistics on Built-In Function Expressions
Example: Collecting Statistics on a Geospatial Column
Example: Collecting Statistics on a Geospatial Index
Example: Collecting Statistics on Named Indexes
Example: Collecting Statistics on an Unnamed Index
Example: Copying Statistics from a Source Table to an Identical Target Table
Example: Recollecting Statistics When Statistics Were First Collected Specifying a USING Clause
Example: Overriding USING Options with the FOR CURRENT Option
Example: Collecting Statistics on a Foreign Table Column
COLLECT STATISTICS Related Information (Optimizer Form)
SHOW STATISTICS
SHOW STATISTICS Syntax
SHOW STATISTICS Syntax Elements
SHOW STATISTICS Usage Notes
General Usage Guidelines for SHOW STATISTICS
Response Sequences for Detailed Statistics (Single Record and Indicator Modes)
Response Sequences for Detailed Statistics (Multiple Records and Indicator Modes)
SHOW STATISTICS Examples
Example: SHOW STATISTICS for COLLECT STATISTICS SQL Text Only
Example: SHOW STATISTICS SEQUENCED Option
Example: SHOW STATISTICS with a VALUES Clause for a Table with Statistics Collected on a Geospatial Column
Example: SHOW SUMMARY STATISTICS with VALUES Clause
Example: Reporting Detailed QCD Statistics
DROP STATISTICS (Optimizer Form)
DROP STATISTICS Syntax (Optimizer Form)
DROP STATISTICS Syntax Elements (Optimizer Form)
DROP STATISTICS Usage Notes (Optimizer Form)
DROP STATISTICS Examples (Optimizer Form)
HELP STATISTICS (Optimizer Form)
HELP STATISTICS Syntax (Optimizer Form)
HELP STATISTICS Syntax Elements (Optimizer Form)
HELP STATISTICS Usage Notes (Optimizer Form)
HELP STATISTICS Examples (Optimizer Form)
Example: Summary Statistics on a Database
Example: HELP STATISTICS on Multiple Individual Columns
Example: Summary Multicolumn Statistics
Example: HELP STATISTICS for a Geospatial Column
Summary Optimizer Statistics Attributes
HELP STATISTICS (QCD Form)
HELP STATISTICS Syntax (QCD Form)
HELP STATISTICS Syntax Elements (QCD Form)
HELP STATISTICS Usage Notes (QCD Form)
Example: Summary Statistics
Summary QCD Statistics Attributes
Statistics Attribute Definitions
Row-Level Security Constraint Statements
CREATE CONSTRAINT
CREATE CONSTRAINT Syntax
CREATE CONSTRAINT Syntax Elements
CREATE CONSTRAINT Examples
ALTER CONSTRAINT
ALTER CONSTRAINT Syntax
ALTER CONSTRAINT Syntax Elements
ALTER CONSTRAINT Examples
DROP CONSTRAINT
DROP CONSTRAINT Syntax
DROP CONSTRAINT Syntax Elements
DROP CONSTRAINT Usage Notes
Example: Dropping a Row-Level Security Constraint with DROP CONSTRAINT
Trigger Statements
ALTER TRIGGER
ALTER TRIGGER Syntax
ALTER TRIGGER Syntax Elements
CREATE TRIGGER and REPLACE TRIGGER
CREATE TRIGGER and REPLACE TRIGGER Syntax
CREATE TRIGGER and REPLACE TRIGGER Syntax Elements
CREATE TRIGGER and REPLACE TRIGGER Usage Notes
Creating and Using Triggers
Trigger Terminology
General Usage Guidelines for CREATE TRIGGER and REPLACE TRIGGER
Transition Tables
Rules for Using the REFERENCING Clause
Row and Statement Triggers
Using Row or Statement Triggers
Triggered Action Statements
Triggers and Referential Integrity
Triggers and Stored Procedures
Triggers and External Stored Procedures that Make SQL Calls
Triggers and MERGE Requests
CREATE TRIGGER and REPLACE TRIGGER Examples
Example: Ensuring that Parent Table Updates Propagate to the Child
Example: Audit Log for Large Pay Raises
Example: Using a SET Clause
Example: Cascaded Triggers
Example: Valid WHEN Clause
Example: Invalid WHEN Clause
Example: Valid Use of Subquery in WHEN Clause
Example: Invalid Use of Subquery in WHEN Clause
Example: OLD_NEW_TABLE AFTER UPDATE Trigger
Example: Calling an SQL Procedure from within a Trigger
Example: Calling a UDF from a Trigger
Example: Using a Trigger to Update a NoPI Table
Example: Invoking an SQL UDF in a Trigger Definition
DROP TRIGGER
DROP TRIGGER Syntax
DROP TRIGGER Syntax Elements
Example: Dropping a Trigger
HELP TRIGGER
HELP TRIGGER Syntax
HELP TRIGGER Syntax Elements
HELP TRIGGER Examples
Trigger Attributes
RENAME TRIGGER
RENAME TRIGGER Syntax
RENAME TRIGGER Syntax Elements
Example: Rename a Trigger
Comment, Help, and Show Statements
COMMENT (Comment-Placing Form)
COMMENT Syntax (Comment-Placing Form)
COMMENT Syntax Elements (Comment-Placing Form)
COMMENT Usage Notes (Comment-Placing Form)
COMMENT Examples (Comment-Placing Form)
Example: Placing a Comment
Example: Commenting on a UDF
Example: Commenting on an SQL Function
Examples: Commenting on a Map
HELP Statements And HELP Reports
SQL HELP Statements
HELP FUNCTION
HELP ONLINE
HELP ONLINE Syntax
HELP ONLINE Syntax Elements
HELP Usage Notes (Online Form)
Examples: HELP ONLINE
HELP Reports
Object Name and Title Data in HELP Reports
Translating HELP Report Data into the Session Character Set
Rules for HELP Report Names and Titles
Formatting HELP Report Results
Determining the SQL Name or SQL Title Delimiter Character
Using an SQL Name or SQL Title Value in an SQL Request
Data Type Codes
TVM Kind Codes
SHOW Statements
SQL SHOW Statements
SHOW object
SHOW object Syntax
SHOW object Syntax Elements
SHOW object Usage Notes
Supported SHOW Statements
SHOW TABLE Usage
General Usage Guidelines for SHOW object
IN XML Option for SHOW Requests
SHOW object Examples
Example: SHOW JOIN INDEX
Examples: SHOW JOIN INDEX in XML Format
Example: SHOW MACRO
Example: SHOW VIEW
Example: SHOW VIEW in XML Format
Example: SHOW PROCEDURE
Example: Showing a Java External Stored Procedure with an XML Data Type
Example: SHOW FUNCTION (External Form)
Example: SHOW FUNCTION (SQL Form)
Example: Show User-Installed File (UIF)
Example: SHOW Avro SCHEMA
Example: SHOW CONSTRAINT
SHOW request
SHOW request Syntax
SHOW request Syntax Elements
SHOW request Usage Notes
SHOW request Examples
Example: SHOW for a SELECT Request
Example: SHOW for a SELECT Request that Accesses Multiple Views
Example: SHOW for a SELECT Request Using the QUALIFIED Option
Example: SHOW for a SELECT Request on a Table with Multiple Referential Integrity Constraints
Example: SHOW for an Updatable DATE PPI Table
Example: SHOW for an SQL Function
Example: SHOW in XML Format
Example: SHOW for SELECT Request with Function Mapping
Object Data Types
Data Types Syntax
SQL Data Manipulation Language
SELECT Statements
SELECT
SELECT Syntax
SELECT Syntax Elements
SELECT Usage Notes
Uses of the SELECT Statement
SELECT Subqueries
SELECT Locks and Concurrency
NoPI Tables and SELECT Statements
SELECT Statements in Embedded SQL
DEFAULT Function in SELECT Statements
Invoking a Scalar UDF from a SELECT Statement
Returning a Varying Column Table External UDF Result
SELECT Statements and Derived Period Columns
SELECT and INSERT ... SELECT Statements with Set Operators
Joins in SELECT Statements
Activity Count Indicates the Number of Rows Returned
SELECT and Queue Tables
Logging Problematic Queries
SELECT Examples
Examples: SELECT Statements
Examples: SELECT Statements with a Correlated Subquery
Example: SELECT Statements with Scalar Subqueries in Expressions and as Arguments to Built-In Functions
Examples: SELECT and PARTITION
Example: SELECT with Queue Tables
Example: SELECT Statements Specifying the DEFAULT Function
Example: Dynamic Row Results Returned by Specifying Table Functions
Example: Scalar Subquery in the Select List of a SELECT Statement
Example: SQL UDF in the Select List of a SELECT Statement
Example: Creating a Time Series Using Expansion by an Interval Constant Value
Example: Invoking an SQL UDF from a Derived Table
Example: Invoking a UDF or Method Using a RETURNS Specification
Example: Invoking a UDF or Method Using a RETURNS STYLE Specification
Example: Selecting Rows from a Table with Row-Level Security Protection
Example: Row-Level Security Constraint and SELECT Statement When User Lacks Required Privileges
Example: Using a Table Operator with Multiple PARTITION BY Inputs
Example: Using a Multiple Input Table Operator with DIMENSION Input
SELECT Related Information
SELECT AND CONSUME
SELECT AND CONSUME Syntax
SELECT AND CONSUME Usage Notes
Locking and Concurrency
Transaction Processing Semantics
FIFO Operations and SELECT AND CONSUME
Rules and Restrictions for SELECT AND CONSUME Statements
Performance Characteristics of SELECT AND CONSUME Statements
Example: Returning the Row Having the Oldest QITS Value in a Queue Table
SELECT ... INTO
SELECT ... AS OF TIMESTAMP
SELECT ... AS OF version_number
AS JSON
AS JSON Usage Notes
Examples: SELECT AS JSON
Select List Syntax
DISTINCT
DISTINCT Usage Notes
Examples: DISTINCT Option
ALL
NORMALIZE Clause
NORMALIZE Clause Syntax
NORMALIZE Clause Usage Notes
Using SELECT with NORMALIZE
INSERT ... SELECT and NORMALIZE
Examples: Using NORMALIZE
TOP Clause
TOP Clause Syntax
TOP Clause Usage Notes
What the TOP n Operator Does
Rules and Restrictions for the TOP n Operator
Evaluation Order of TOP n in a SELECT
TOP n Operator Outperforms QUALIFY RANK and QUALIFY ROW_NUMBER
Examples: Comparing the Results of Specifying TOP n * and TOP n WITH TIES *
WITH Modifier
WITH Modifier Syntax
WITH Modifier Usage Notes
Nonrecursive Named Query in a WITH Modifier
RECURSIVE Named Query in a WITH Modifier
WITH Modifiers
Rules and Restrictions for Embedded SQL
Using a WITH Statement Result as Input to a Table Function in the FROM Clause
Depth Control to Avoid Infinite Recursion
WITH Modifier Examples
Example: Common Table Expression
Example: Recursive Common Table Expressions in a WITH Modifier
Example: Multiple Seed and Recursive Statements
Example: CTE Result Set as Input to a Table Function
Examples: External UDFs
Example: Invoking an SQL UDF in a Recursive Query
FROM Clause
FROM Clause Syntax
TABLE Option
TABLE Option Syntax
TABLE Option Examples
Example: Specifying a TABLE Function in the FROM Clause
Example: Hash Ordering Input Parameters to a Table Function
Example: HASH BY and LOCAL ORDER BY Clauses in the Same Statement
Example: Local Ordering of Input Parameters to a Table Function
Table Operator
Table Operator Syntax
Table Operator Usage Notes
Running a Function Mapping
Function Mapping and the ON Clause
Function Mapping and the USING Clause
Table Operator Examples
Examples: Table Operator Function Mapping
Example: SELECT and the Function Mapping Definition
Examples: Function Processing with Variable Substitution
Example: Function Processing with Variable Substitution for Input Tables and Parameters
Example: Function Processing with a Concatenated Variable Expression
Examples: Function Processing with Default Values for Variables
Example: Function Processing with Nested Variables
Example: Function Processing with a Scalar Subquery
Example: Function Processing with Input Table Variable Substitution
Example: Function Processing with Any Input Table Substitution
Example: Specifying a Sparse Map for a Table Operator
Derived Tables
Derived Table Syntax
Derived Tables Usage Notes
Uses for Derived Tables
Rules and Restrictions for Derived Tables
Example: Using Derived Tables for Multilevel Aggregation
FROM Clause Usage Notes
Fully-qualified Names and the FROM Clause
Subqueries and the FROM Clause
Self-join Operations and the FROM Clause
FROM Clause Examples
Example: FROM Clause Used for a Self-Join
Example: FROM Clause Left Outer Join
WHERE Clause
WHERE Clause Syntax
WHERE Clause Usage Notes
Aggregates in a WHERE Clause
Expressions Containing LOBs
Row-Level Security Constraint Values
SELECT AND CONSUME Subqueries
DEFAULT Function in a Search Condition
DEFAULT Function Data Type
DEFAULT Function Column Name
DEFAULT Function Comparison Operators
AND and OR Logical Operators
Using WHERE to Filter Character Data
Scalar UDFs
Scalar Subqueries in WHERE Clause
SAMPLE Clause in a Subquery
WHERE Clause Defines Condition for Joining Table Rows
EXISTS Quantifier
Specifying a Column PARTITION or PARTITION#L n
Use Consistent Predicate Domains
Join Efficiency and Indexes
NoPI Tables, Column-Partitioned Tables, and WHERE Clause Search Conditions
Unconstrained Joins
WHERE Clause Examples
Example: Simple WHERE Clause Predicate
Example: Using the table_name.* Syntax
Example: Delete from a Row-Partitioned Table Using the System-Defined PARTITION Column in the Search Condition
Example: INSERT SELECT and DELETE Operations from Row-Partitioned Table Using PARTITION Column as Search Condition
Example: DEFAULT Function in a WHERE Clause Search Condition
Example: Scalar Subquery in the WHERE Clause of a SELECT Statement
Example: Invoking an SQL UDF in a Search Condition
Example: Specifying a Row-Level Security Constraint in a Search Condition
GROUP BY Clause
GROUP BY Clause Syntax
ROLLUP Option
ROLLUP Option Syntax
ROLLUP Option Examples
CUBE Option
CUBE Option Syntax
CUBE Option Example
GROUPING SETS Option
GROUPING SETS Option Syntax
GROUPING SETS Option Example
GROUP BY Clause Usage Notes
GROUP BY Clause Terminology
Ordinary Grouping Set Expressions
GROUP BY and Aggregate Operations
GROUP BY and Nonaggregate Operations
GROUP BY and DISTINCT
Comparing GROUP BY and Correlated Subqueries Using a Scalar UDF
GROUP BY and Built-in Ordered Analytic Functions
BLOB or CLOB Columns Not Allowed in a GROUP BY Expression
GROUP BY and Recursive Queries
ORDER BY and GROUP BY
WHERE, GROUP BY, and HAVING Clause Evaluation
Reason for Unexpected Row Length Errors: Sorting Rows for Grouping
How Vantage Resolves Multiple Grouping Sets Specifications
Example: Semantically Identical Grouping Sets Specifications and Their Resolution
GROUP BY Clause Examples
Example: Simple GROUP BY Operation
Example: Specifying a GROUP BY Clause on Nonaggregate Expressions When the Select List Includes an Aggregate
Example: Specifying GROUP BY Using an Ordered Analytic Function
Example: SELECT Statement with a Scalar Subquery in the GROUP BY Clause
Example: GROUP BY and PERIOD Value Expressions
HAVING Clause
HAVING Clause Syntax
HAVING Clause Usage Notes
LOB Columns and the HAVING Clause
Aggregates and the HAVING Clause
Recursive Queries and the HAVING Clause
Row-level Security Constraints and the HAVING Clause
Scalar Subqueries and the HAVING Clause
DEFAULT Function and the HAVING Clause
SAMPLE Clause and the HAVING Clause
Evaluation Order of WHERE, GROUP BY, and HAVING Clauses
Tables References in a HAVING Clause
Aggregating a Join in a HAVING Clause
HAVING Clause Examples
Examples: HAVING Clause
Example: Grouping Departments by Average Salaries
Example: Using a HAVING Clause to Aggregate a Join
QUALIFY Clause
QUALIFY Clause Syntax
QUALIFY Clause Usage Notes
Rules and Restrictions for the QUALIFY Clause
Evaluation Order of WHERE, GROUP BY, and QUALIFY Clauses
QUALIFY Error Conditions
QUALIFY Clause Examples
Example: Using the RANK Function in a QUALIFY Clause
Example: Reporting the Bottom Percentile of Items Using QUANTILE in a QUALIFY Clause
Example: Behavior of OLAP Aggregate Functions that Return Zeros
SAMPLE Clause
SAMPLE Clause Syntax
SAMPLE Usage Notes
SAMPLEID Expression
SAMPLEID Expression Syntax
SAMPLEID Expression Examples
Examples: SAMPLE Clause
EXPAND ON Clause
EXPAND ON Clause Syntax
EXPAND ON Clause Usage Notes
Time Series, Dense Representations, and Sparse Representations of Temporal Data
Rules and Restrictions for the EXPAND ON Clause
Rules and Restrictions for Interval Expansion
Anchor Period and Anchor Point Expansion
Rules and Restrictions for Anchor Point Expansion of the EXPAND ON Clause
Uses of Anchor Period Expansions
Anchored Interval Uses System-defined Business Calendar Set in the Session
Anchor Names
Anchor Results Can Differ between the ISO Calendar and the Vantage or COMPATIBLE Calendars
Expand Does Not Consider Exceptions
Expand Interval Literal Expressions
Anchor Name Literal Expressions
WEEK_BEGIN Anchor Name and WeekStart Anchor Expansion
EXPAND ON Clause Examples
Example: Expansion on an Anchor Point Using WEEK_DAY
Example: Expansion on an Anchor Point Using ANCHOR_SECOND
Example: Expansion over a UNION Operator
Example: EXPAND ON MONTH_BEGIN and MONTH_END
Example: EXPAND ON and DISTINCT
Example: Same Expansion in Two Different Sessions Using Different Time Zone Intervals
Example: EXPAND ON and OLAP Functions
Example: Same Expansion in Two Different Sessions in Different Time Zones
Example: EXPAND ON with a Join
Example: EXPAND ON for an Anchored Interval
Example: EXPAND ON and Span Grouping
Example: EXPAND ON for a Moving Average
Example: EXPAND ON for a WEEK_BEGIN Anchor Point
Example: EXPAND ON for a QUARTER_BEGIN Anchor Period
Example: Join before Expansion
Example: Nullified EXPAND Operation
Example: Null Expansion Period Producing a Null Expanded Value
ORDER BY Clause
ORDER BY Clause Syntax
ORDER BY Clause Usage Notes
Column References and ORDER BY
WITH and ORDER BY
PARTITION BY, HASH BY, and ORDER BY
Sorting and Default Sort Order Row Length Errors
Unexpected Sort Order When Querying DATE Data Type Columns
Specifying Collation
Japanese Character Sort Order Considerations
International Sort Orders
European Sort Order
ORDER BY Clause Examples
Example: Ordering on a Column Name
Example: Ordering on a Column Name Not Specified in the Select List
Example: Ordering on Column Position
Example: Ordering on a Column Name Alias
Example: Ordering on an Expression
Example: Ordering on Column Name or Column Position Using an Ascending or Descending Sort Sequence
Example: Effects of Default and User-Specified Case Sensitivity on the Ordering of a Result Set
Example: Ordering by a Column Alias Name
Example: Ordering on a PERIOD Value Expression
ORDER BY Clause Related Information
WITH Clause
WITH Clause Syntax
WITH Clause Examples
Specifying Subqueries in Search Conditions
IN, NOT IN, ANY, SOME, and ALL Logical Predicate Syntax
EXISTS Logical Expression Syntax
Specifying Subqueries in Search Condition Usage Notes
Subqueries in Search Conditions Examples
Example: Simple Subquery Using the IN Logical Predicate
Example: Simple Subquery Using an AVG Aggregate Expression
Example: Simple Subquery Using the ALL Logical Predicate
Example: Subquery Using the SUM and COUNT Aggregate Functions
Example: Subqueries Using the RANK Functions and QUALIFY Clauses
Correlated Subqueries
Correlated Subqueries Usage Notes
Using Correlated Subqueries
EXISTS Quantifier and Correlated Subqueries
Outer References in Correlated Subqueries
Guidelines for Coding Noncorrelated Subqueries
Comparing Correlated and Noncorrelated Subqueries
Correlated Subquery Examples
Example: Combining Equality and Inequality Conditions in a Correlated Subquery
Example: Using SELECT COUNT(*) in a Correlated Subquery
Scalar Subqueries in SELECT Statements
Time Travel Queries
Working with External Data
Query Foreign Tables
Query Foreign Tables with JSON Data
Query Foreign Tables with CSV Data
Query Foreign Tables with Parquet Data
Query Foreign Tables Usage Notes
Foreign Table Locking
Queries on External Data are Nondeterministic
LOCATION Key Prefix Best Practices
$PATH Expression for External Data Access
Foreign Table Examples
Query External Files
READ_NOS
READ_NOS Syntax
READ_NOS Syntax Elements
Set Up External Object Storage for River Flow Data
Examples: Setting Up Function Mapping for READ_NOS
Define a Function Mapping for READ_NOS
Example: Creating an Authorization Object to Use with a Function Mapping
Example: Function Mapping Definition Using EXTERNAL SECURITY Clause for JSON Data Files
Example: Function Mapping Definition Using ANY IN TABLE for JSON Data Files
Examples: Using READ_NOS
Example: Create an Authorization Object
Example: Using READ_NOS with NOSREAD_KEYS Return Type
Example: Using READ_NOS with NOSREAD_RECORD Return Type
Examples: Using READ_NOS with NOSREAD_SCHEMA Return Type
Example: NOSREAD_SCHEMA with JSON Schema
Example: NOSREAD_SCHEMA with CSV Schema
Example: NOSREAD_SCHEMA with Parquet Schema
Example: Using READ_NOS to Query an External JSON File
Example: Using READ_NOS to Query an External CSV File
Example: Using READ_NOS to Query a File in Google Cloud Storage
Example: Creating an Azure Service Principal Authorization Object and Using It with READ_NOS
Example: Using AWS Assume Role Authorization with READ_NOS
WRITE_NOS
WRITE_NOS Syntax
WRITE_NOS Syntax Elements
WRITE_NOS Returns
WRITE_NOS Usage Notes
WRITE_NOS Examples
Write All Data from a Vantage Table to External Object Storage with Partitioning
Generate Destination Path Name in Subquery
PARTITION BY with INCLUDE_ORDERING ('TRUE')
PARTITION BY with INCLUDE_ORDERING ('FALSE')
Example: Using AWS Assume Role Authorization with WRITE_NOS
Example: Creating a Azure Service Principal Authorization Object and Use It with WRITE_NOS
Set Operators
SQL Set Operators
Teradata SQL Set Operators
query_term Syntax
query_factor Syntax
query_expression Syntax
Rules for Set Operators
Set Operator Duplicate Row Handling
Operations that Support Set Operators
SELECT AND CONSUME Statement
Support for ORDER BY Clause
Restrictions on the Data Types Involved in Set Operations
Set Operators Related Information
Precedence of Set Operators
Example: Select Statement
Example: Using Parentheses to Customize Precedence
Precedence of Set Operators Related Information
Retaining Duplicate Rows Using the ALL Option
Attributes of a Set Result
Example: Attributes-Set-Result
Example: Attributes-Set-Result
Example: Poorly Formed Query Results
Example: How Character Set is Determined for Query
Set Operators with Derived Tables
Set Operators in Subqueries
Set Operators in Subqueries Example 1
Set Operators in Subqueries Example 2
Set Operators in Subqueries Example 3
Set Operators in Subqueries Example 4
Set Operators in Subqueries Example 5
Set Operators in Subqueries Example 6
Set Operators in Subqueries Example 7
Set Operators in INSERT ... SELECT Statements
Example: Simple INSERT ... SELECT Using Set Operators
Example: INSERT ... SELECT from a View that Uses Set Operators
Example: INSERT ... SELECT from Derived Table with Set Operators
Set Operators in View Definitions
Set Operator Restrictions
Set Operators in View Definitions Examples
Queries Connected by Set Operators
Number of Expressions in SELECT Statements
WITH Clause and Set Operators
GROUP BY and ORDER BY Clauses
Table Name in SELECT Statements
Data Type Compatibility
Queries Connected by Set Operators Related Information
INTERSECT Operator
INTERSECT Operator Syntax
INTERSECT Operator Usage Notes
Rules for INTERSECT
Attributes of INTERSECT Set Result
Data Type of Nulls
INTERSECT Duplicate Row Handling
Example: INTERSECT Operator
MINUS/EXCEPT Operator
MINUS/EXCEPT Operator Syntax
MINUS/EXCEPT Usage Notes
UNION Operator
UNION Operator Syntax
UNION Operator Usage Notes
Valid UNION Operations
Unsupported Operations
Description of a UNION Operation
UNION and NULL
UNION Duplicate Row Handling
Unexpected Row Length Errors: Sorting Rows for UNION
UNION Operator Examples
Example: Selecting the Name, Project, and Employee Hours
Example: Determining the Number and Names of Employees
Example: Merging Lists of Values
Example: Performing a Union Operation to Find Hours Worked
UNION Operator and the Outer Join
Example: Effect of Explicit NULLs on Data Type of a UNION
Example: Effect of the Order of SELECT Statements on Data Type
Example: LOB Support in a UNION ALL Query
Join Expressions
Joins
Inner Joins
Ordinary Inner Join
Cross Join
Self-Join
Outer Joins
Definition of the Outer Join
Outer Join Syntax
Outer Join Relational Algebra
Left Outer Join
Right Outer Join
Full Outer Join
Multitable Joins
Coding ON Clauses for Outer Joins
Coding ON Clauses with WHERE Clauses for Outer Joins
Outer Join Case Study
Case Study Examples
Heuristics for Determining a Reasonable Answer Set
Guidelines for Using Outer Joins
Statement Syntax
Statement Independence Support
Null
Null Statement Syntax
Null Statement Examples
ABORT
ABORT Syntax
ABORT Usage Notes
Examples: ABORT Statement
BEGIN TRANSACTION
BEGIN TRANSACTION Syntax
BEGIN TRANSACTION Usage Notes
Rules for Embedded SQL (BEGIN TRANSACTION)
Explicit Transactions
Implicit Transactions
Rules for Transactions Containing DDL Statements
Vantage Transaction Handling Protocol
Specifying SQL Request Modifiers with Explicit Transactions
Nested BEGIN TRANSACTION/END TRANSACTION Pairs
Examples: BEGIN/END TRANSACTION
CALL
CALL Syntax
CALL Usage Notes
CALL Invocation
Rules for Executing SQL Procedures and External Stored Procedures
Rules for Specifying Input and Output Parameters
Rules for Call Arguments in BTEQ and CLIv2
Rules for Call Arguments in ODBC and JDBC
Rules for Call Arguments in Nested Procedures
Rules for Calling Procedures from Embedded SQL
Rules for Calling a Procedure with Dynamic Result Sets from Embedded SQL
Session Dateform and Called Procedures
Retrieving Values of Output Parameters
Status of Unqualified Referenced Objects
Dropped, Renamed, or Replaced Objects
Java External Procedure-Specific Behavior
Called Procedure Priority
Errors and Failures in Procedure Execution
Errors and Failures in Nested Procedures
Ending a CALL Statement
Asynchronous and Synchronous Abort Logic
CALL Examples
Example: Input Arguments in BTEQ and CLIv2
Example: Input and Output Arguments in BTEQ and CLIv2
Example: Stored Procedure and Embedded SQL Input Arguments
Example: Stored Procedures and Embedded SQL Input and Output Arguments
Example: Preventing Memory Overflow Errors for INOUT Parameters
Example: Input and Output Arguments in ODBC
Example: Input and Output Arguments in JDBC
Example: Calling a Stored Procedure that Returns Dynamic Result Sets from Embedded SQL
Example: Using an SQL UDF as an Argument for an External Stored Procedure
Example: Specifying a RETURNS or RETURNS STYLE Clause for an OUT Parameter Return Type
Example: Using RETURNS and RETURNS STYLE Clauses in the Same Procedure Call
CHECKPOINT
CHECKPOINT Syntax
CHECKPOINT Usage Notes
CHECKPOINT Example
COMMENT (Comment-Retrieving Form)
COMMENT Syntax (Comment-Retrieving Form)
COMMENT Example (Comment-Retrieving Form)
COMMIT
COMMIT Syntax
COMMIT Examples
DELETE
DELETE Syntax
DELETE Usage Notes
DELETE Locks and Concurrency
DELETE Processing Time
Unconstrained Fastpath Delete Processing
Restrictions on Fastpath Deletions
Fastpath Positioning Requirements
Position in the Transaction
Proper Positioning for Session Mode and Transaction Type
Fastpath Delete and Join Indexes
Fastpath Delete for Multiple DELETE Statements
Constrained (Slow Path) DELETE Processing
DELETE Duplicate Row Handling
General Rules for Using DELETE
Deleting Rows Using Views
Subqueries in a DELETE Statement
Rules for Using Scalar Subqueries in a DELETE Statement
Rules for Using Correlated Subqueries in a DELETE Statement
DELETE Statement in the Last Request
Deferred Deletion Applies Only to Range Terms
Deferred Deletion is Supported for LIKE Terms
Deferred Deletion and Join Indexes
Collation and Row Deletion
Column Partitioned Tables and DELETE
DELETE for NoPI Tables
Queue Tables and DELETE
Embedded SQL and Stored Procedure Error Condition Handling
DELETE Examples
Example: DELETE Set of Rows
Example: DELETE All Rows
Example: DELETE Single Row
Example: Join Condition DELETE
Example: Self-Join Condition DELETE
Example: Delete Rows with an Equality Constraint on a Partitioning Column
Example: Delete Rows with an Indirect Constraint on the Target Table Partitioning Column
Example: Macro for DELETE
Example: DELETE ALL Multiple-Statement Request
Example: Join Condition DELETE with FROM Clause and Correlation Name
Example: Join Condition DELETE with No FROM Clause
Example: Join Condition DELETE with Derived Table Subquery
Example: DELETE and NoPI Tables
Example: Using an SQL UDF in a DELETE Request WHERE Condition
Example: Deleting a Row from a Table with Row-Level Security Protection
Example: Row-Level Security DELETE and SELECT Constraints for User Lacking Required Privileges (DELETE)
EXPLAIN Request Modifier with a DELETE Statement
DELETE (Datalake Form)
DELETE Syntax (Datalake Form)
Examples: DELETE (Datalake Form)
ECHO
ECHO Syntax
ECHO Example
END TRANSACTION
END TRANSACTION Syntax
END TRANSACTION Usage Notes
EXECUTE (Macro Form)
EXECUTE Syntax (Macro Form)
EXECUTE Examples (Macro Form)
EXECUTE FUNCTION
EXECUTE FUNCTION Syntax
EXECUTE FUNCTION Usage Notes
EXECUTE FUNCTION Example
INSERT/INSERT ... SELECT
INSERT/INSERT ... SELECT Syntax
INSERT/INSERT ... SELECT Usage Notes
INSERT/INSERT ... SELECT Locks and Concurrency
Inserting Using DEFAULT Function Option, DEFAULT VALUES Option, or without Specifying a Value
Inserting into Row-Partitioned Tables, Global Temporary Tables, and NoPI Tables
Rules for Using HASH BY or LOCAL ORDER BY to Insert Rows
Multiple-Statement and Iterated INSERT Requests
Inserting When Using a DEFAULT Function
Using INSERT ... SELECT with Tables that Have Row-Level Security
INSERT, DEFAULT Function, PERIOD Value Constructor, Scalar UDFs, and Stored Procedures
Inserting Rows into Queue Tables
Valid and Invalid INSERT Operations
INSERT Duplicate Row Handling
INSERT Process
Inserting Rows Using Views
Subqueries in INSERT Requests
SELECT AND CONSUME Subqueries in INSERT Requests
Data Takes the Attributes of the New Table
Logging Errors for INSERT ... SELECT Requests
Fast Path INSERT ... SELECT Requests
INSERT in Embedded SQL and Stored Procedures
Rules for Using the JSON Option
INSERT/INSERT ... SELECT Examples
Example: Insert Row
Example: Insert Using a SELECT Subquery
Example: Insert Using a SELECT Subquery without Target Column List
Example: Insert with Named Columns
Example: Insert Ordered Entries
Example: Bulk Insert
Example: INSERT Operation Using Single-Writer Lock
Example: INSERT and GENERATED ALWAYS Identity Columns
Example: INSERT and GENERATED BY DEFAULT Identity Columns
Example: Identity Columns and INSERT ... SELECT
Example: INSERT and Queue Tables
Example: Invalid Use of PARTITION in VALUES Clause of INSERT Request
Example: Simple INSERT Requests Using a DEFAULT Function
Example: Using the DEFAULT Function with INSERT ... SELECT
Example: Logging Errors with INSERT ... SELECT
Example: Using the ST_Geometry Data Type to Represent Other Geospatial Data Types for INSERTs
Example: Using the ST_Geometry Data Type When Inserting Geospatial Data into Tables
Example: INSERT Using a Scalar Subquery
Example: INSERT Using a PERIOD Value Constructor
Example: Passing an SQL UDF to a Single-Row Request
Example: HASH BY Option with NoPI Table
Example: LOCAL ORDER BY Option
Example: Using RANDOM to Redistribute Blocks of Rows and Individual Rows before Copying Them Locally
Examples: Inserting Data in JSON Format
INSERT/INSERT ... SELECT (Datalake Form)
INSERT/INSERT ... SELECT Syntax (Datalake Form)
Examples: INSERT/INSERT ... SELECT (Datalake Form)
LOCKING Request Modifier
LOCKING Request Modifier Syntax
LOCKING Request Modifier Usage Notes
LOCKING Request Modifier and CALL Requests
LOCKING Request Modifier Use with DML Statements
Canceling a Lock
Positioning Explicit Lock Requests
Using Locks with NULL SQL Requests
Using LOCKING ROW
Multiple Locks
Referencing a Locked Object
Specify the Keyword for the Object to Lock
Locks and Views
When the Request and View Referenced Include LOCKING Request Modifiers
READ Locks and Canceled Rollback Operations
Determining Which Locks Are Set
LOCKING Request Modifier Examples
Example: LOCKING Request Modifier
Example: LOCKING Request Modifier and Secondary Indexes
Example: LOCKING ROW
Example: NOWAIT Option
MERGE
MERGE Syntax
MERGE Usage Notes
MERGE Locks and Concurrency
MERGE Statement
MERGE INTO Support for OFS
Rules and Limitations for MERGE
Logging MERGE Errors in an Error Table
MERGE Examples
Example: Using MERGE to Update and Insert
Example: MERGE with Subquery
Example: Using the DEFAULT Function with MERGE
Example: Logging MERGE Errors
Example: Using MERGE for Update and Insert Operations in a Single SQL Request
Example: Target Table Composite Primary Index
Example: ON Clause Conditions Must Be ANDed with Primary Index and Partitioning Column Equality Constraints
Example: RPPI Table ON Clause Must Specify Condition on Partitioning Column and INSERT Specification Must Match
Example: ON Clause Errors or Mismatches between ON Clause and INSERT Specification
Example: MERGE with ON Clause and UPI or USI
Example: MERGE Updating a Primary Index
Example: MERGE and Identity Columns
Example: MERGE and Target Columns
Example: Reference the Source or Target in the ON, WHEN MATCHED, or WHEN NOT MATCHED Clauses
Example: Specifying Partitioning Column Set in ON Clause When Target Relation Has Row-Partitioned Primary Index
Example: Partitioning Column Set for MERGE Operation with RPPI Target Table Cannot Be System-Derived PARTITION Column
Example: Guaranteed Single-Row Source Relation Does Not Need Specified Partitioning Column Set
Example: Using the Target Table as the Source Table
Example: Using the BEGIN Period Bound Function as a Condition When Merging into an RPPI Table
Example: Using the BEGIN and END Period Bound Functions as Conditions When Merging into an RPPI Table
Example: Failure Because the Matching Condition is Defined on a PERIOD Bound Function
Example: Invoking an SQL UDF from a MERGE Request
Example: MERGE Update Request When Target Table and Source Table Have Row-Level Security Constraints
Example: Application of Row-Level Security SELECT Constraints When User Lacks Required Privileges (MERGE Request)
ROLLBACK
ROLLBACK Syntax
ROLLBACK Usage Notes
Definition and Termination of ANSI Transactions
ROLLBACK Is Explicit
Embedded SQL (ROLLBACK)
ROLLBACK and ABORT are Synonyms
Actions Performed by ROLLBACK
Actions Performed by ROLLBACK with Embedded SQL
ROLLBACK with a WHERE Clause
Rules for Using a Scalar UDF in a ROLLBACK Request
Rules for Using Correlated Subqueries in a ROLLBACK Request
Rules for Using Scalar Subqueries in ROLLBACK Requests
Multiple ROLLBACK Requests
Two Types of ROLLBACK Requests
ROLLBACK with BTEQ
ROLLBACK Example
UPDATE
UPDATE Syntax (Basic Form, No FROM Clause)
UPDATE Syntax (Basic Form, FROM Clause)
UPDATE Syntax (Joined Tables Form)
UPDATE Usage Notes
UPDATE Locks and Concurrency
Activity Count
UPDATE Duplicate Row Handling
Duplicate Row Checks
Large Objects and UPDATE
UPDATE Processing Time
Rules for Embedded SQL and Stored Procedures
Queue Tables and UPDATE
Rules for Updating Partitioning Columns of a Row-Partitioned Table
Rules for Updating a Table with a Row-Partitioned Join Index
Rules for Updating Column-Partitioned Tables
Identity Columns and UPDATE
Updating of GENERATED ALWAYS Identity Columns and PARTITION Columns
Rules for Updating Rows Using Views
Rules for Using Scalar Subqueries in UPDATE Requests
Rules for Using a Scalar UDF in an UPDATE Request
Rules for Using the DEFAULT Function with Update
Rules for Using a PERIOD Value Constructor with UPDATE
Invalid Uses of UPDATE
FROM Clause and UPDATE
UPDATEs with a Join
UPDATE Examples
Example: UPDATE to Set a Specific Value
Example: UPDATE by a Percentage
Example: UPDATE to Set a Null Value
Example: Updating a Nonpartitioned Column with an Equality Constraint
Example: UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause
Example: UPDATE with a Subquery in the WHERE Clause
Example: UPDATE with a Noncorrelated Subquery in the WHERE Clause
Example: UPDATE with a Scalar Noncorrelated Subquery in the SET Clause
Example: UPDATE with a Scalar Correlated Subquery in the SET Clause
Example: UPDATE with a Correlated Subquery in the WHERE Clause
Example: UPDATE with a Noncorrelated Subquery in the WHERE Clause
Example: UPDATE with a Join
Example: UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses
Example: Update Requests Using the DEFAULT Function
Example: UPDATE Using a PERIOD Value Constructor
Example: Updating a NoPI Table
Example: Applying Row-Level Security SELECT and UPDATE Constraints When User Lacks Required Privileges (UPDATE Request)
UPDATE (Upsert Form)
UPDATE Syntax (Upsert Form)
UPDATE Syntax Usage Notes (Upsert Form)
Definition of Upsert
Purpose of the Atomic Upsert Operation
Rules for Using Atomic Upsert
UPDATE (Upsert Form) Locks and Concurrency
UPDATE (Upsert Form) Insert Operations and Row-Partitioned Tables
UPDATE (Upsert Form) Update Operations and RPPI Tables
UPDATE (Upsert Form) and Join Indexes
UPDATE (Upsert Form) and Subqueries
Queue Tables and UPDATE (Upsert Form)
UPDATE (Upsert Form) as a Triggering Action
UPDATE (Upsert Form) as a Triggered Action
Rules and Restrictions
Unsupported Syntax or Features
Using UPDATE (Upsert Form) Requests with Scalar Subqueries
Using UPDATE (Upsert Form) Requests on RPPI Tables
Using UPDATE (Upsert Form) with the DEFAULT Function
UPDATE Examples (Upsert Form)
Table for UPDATE Upsert Examples
Example: Upsert Update
Example: Upsert Insert
Example: Upsert Specifying Different Tables
Example: Upsert on Primary Index
Example: Upsert without Specifying Primary Index
Example: Upsert without ELSE Clause
Example: Upsert Update Using the DEFAULT Function
Example: Upsert Update Using a Period Bound Function
UPDATE (Datalake Form)
UPDATE Syntax (Datalake Form)
Examples: UPDATE (Datalake Form)
USING Request Modifier
USING Request Modifier Syntax
USING Request Modifier Usage Notes
USING Is Not Supported for Embedded SQL
Actions Performed by USING
USING Variables
USING Support for Scalar UDFs
Valid Request Types
USING and the EXPLAIN Request Modifier
USING Support for Large Objects
Inline Mode
Deferred Mode
Locator Mode
LOB Transfer Mode Comparison
Client Application Restrictions on USING with Large Objects
USING and DateTime System Functions
ANSI DateTime Considerations
Example: ANSI DateTime and Interval with USING
ANSI DateTime and Parameterized Requests
Array Considerations for Specifying TOP n as a USING Parameter
Character String Definitions in a USING Request Modifier
data_type Considerations for a Japanese Character Site
Non-GRAPHIC Character Data Representation
Character String Assignment and GRAPHIC Columns
CHARACTER and GRAPHIC Server Character Set Limitations for INSERT Operations
Character Data Import Process
UPPERCASE Option and Character Parameter Definition in USING
USING Request Modifier Examples
Example: USING Request Modifier
Example: USING Request Modifier with Variables
Example: USING Request Modifier Reads Character Strings
Example: Inline Mode Processing of a Large Object
Example: Deferred Mode Processing of a Large Object
Example: Deferred Mode Processing of CLOBs Using the DEFERRED BY NAME Phrase
Example: DEFERRED MODE Processing of BLOBs Using the DEFERRED BY NAME Phrase
Example: Locator Mode Processing of a Large Object
Example: Using a Locator Multiple Times in a Session
Example: Iterated Requests
Example: USING Request Modifier with TOP Operator
Example: Non-Support for Iterated Requests with TOP n
Example: USING and SQL UDF Invocation
Query and Workload Analysis Statements
COLLECT DEMOGRAPHICS
COLLECT DEMOGRAPHICS Syntax
COLLECT DEMOGRAPHICS Examples
COLLECT STATISTICS (QCD Form)
COLLECT STATISTICS Syntax (QCD Form)
COLLECT STATISTICS Usage Notes (QCD Form)
COLLECT STATISTICS Invocation (QCD Form)
Where Column and Index Statistics Are Stored
Rules and Guidelines for COLLECT STATISTICS (QCD Form)
Collecting QCD Statistics on Multiple Columns
Collecting QCD Statistics on the PARTITION Column of a Table
COLLECT STATISTICS (QCD Form) And Large Objects
COLLECT STATISTICS (QCD Form) Does Not Collect Statistics for the Optimizer
Implicit Recollection of QCD Statistics Is Not Supported
COLLECT STATISTICS (QCD Form) Is Not Treated as DDL by Transaction Manager
COLLECT STATISTICS (QCD Form) Collects New Statistics Even If Statistics Exist in the Data Dictionary or QCD
Quality of Statistics as a Function of Sample Size
COLLECT STATISTICS Examples (QCD Form)
Example: Collect Statistics on a Single-Column NUSI
Example: Collect Statistics on Index Using an Alternate Syntax
Example: Collecting Single-Column PARTITION Statistics
Example: Collecting Multicolumn PARTITION Statistics
DROP STATISTICS (QCD Form)
DROP STATISTICS Syntax (QCD Form)
DROP STATISTICS Examples (QCD Form)
DUMP EXPLAIN
DUMP EXPLAIN Syntax
DUMP EXPLAIN Usage Notes
EXPLAIN and the USING Request Modifier
Capturing EXPLAIN Text Output in XML Format
EXPLAIN Report Overview
Substitute Characters in Object Names and Literals Returned in an EXPLAIN Report
Standard Form of Display for EXPLAIN
EXPLAIN Processes SQL Requests Only
EXPLAIN and Embedded SQL
EXPLAIN and Procedures
Effect of Request Cache Peeking on EXPLAIN Reports
Using EXPLAIN to Determine the Database Objects that a View Accesses
2PC Session Mode
DUMP EXPLAIN Examples
EXPLAIN Request Modifier
EXPLAIN Request Modifier Syntax
INITIATE INDEX ANALYSIS
INITIATE INDEX ANALYSIS Syntax
INITIATE INDEX ANALYSIS Usage Notes
Boundary Options
Rules for Performing INITIATE INDEX ANALYSIS
Sequence of Events for an Index Analysis
Retrieving a Completed Index Analysis
WITH INDEX TYPE or WITH NO INDEX TYPE
Using the CHECKPOINT Option
CHECKPOINT and TIME LIMIT Options
Evaluation of the ChangeRate Variable by INITIATE INDEX ANALYSIS
INITIATE INDEX ANALYSIS Not Supported from Macros
INITIATE INDEX ANALYSIS Examples
Example: Index Analysis
Example: Index Analysis and Privileges
Example: Index Analysis and Table List
Example: Index Analysis and Duplicate Table Specifications
Example: Index Analysis and Repeated Parameters
Example: Using a CHECKPOINT
Example: Setting a TIME LIMIT on an Index Analysis
Example: Specifying a CHECKPOINT and a TIME LIMIT
Example: Include NUSI and Simple Join Indexes Only in the Analysis
Example: Exclude All Join Indexes from The Analysis
Example: Include All Valid Index Types in the Analysis
INITIATE PARTITION ANALYSIS
INITIATE PARTITION ANALYSIS Syntax
INITIATE PARTITION ANALYSIS Examples
INSERT EXPLAIN
INSERT EXPLAIN Syntax
INSERT EXPLAIN Usage Notes
INSERT EXPLAIN Examples
RESTART INDEX ANALYSIS
RESTART INDEX ANALYSIS Syntax
RESTART INDEX ANALYSIS Examples
Performance Considerations
Logging Row Counts for DML Statements
Optimizing INSERT ... SELECT Requests
Bulk SQL Error Logging
TOP n Row
Using Recursive Queries
Sampling Methods
Distincts and Multiple Aggregate Distincts
Merge Joins and Performance
SQL Functions
SQL Function Reference (alphabetical)
Create, Use, and Migrate UDFs and External Stored Procedures in VantageCloud Lake
External Routine Migration Tool
Install the External Routine Migration Tool
Specifying XRMigrationTool Options
XRMigrationTool Syntax
Migration Tool Config File
PAT Support in XRMigrationTool
Migrating Existing SQL Scripts to VantageCloud Lake
Run the External Routine Migration Tool
Run Migrated Scripts
Example: Migrating Existing UDF SQL Scripts to VantageCloud Lake
External Routine Installation Tool
Install the External Routine Installation Tool
Specifying tdextroutine Options and Environment Variables
tdextroutine UDF Subcommands and Options
tdextroutine External Stored Procedure Subcommands and Options
UDF Manifest File
Example: UDF Manifest File
External Stored Procedure Manifest File
Creating UDFs
Create a C, C++, or Java UDF
Example: Creating a C UDF
Using UDFs
Call a UDF
List C/C++ or Java UDFs
Get Details about a C/C++ or Java UDF
Replace a C/C++ or Java UDF
Redistribute a Java UDF JAR File
Delete a Java UDF JAR File
Drop a C/C++ or Java UDF
Creating and Using External Stored Procedures
SQL Operators and User Defined Functions
Compression/Decompression Functions
CAMSET
CAMSET Syntax
CAMSET Argument Type and Rules
CAMSET Result Type
CAMSET Usage Notes
CAMSET Restrictions
Uncompressing Data Compressed with CAMSET
CAMSET Examples
Example: Compressing Unicode Values with CAMSET
Example: Querying for Compressed Values
CAMSET_L
CAMSET_L Syntax
CAMSET_L Argument Type and Rules
CAMSET_L Result Type
CAMSET_L Usage Notes
Uncompressing Data Compressed with CAMSET_L
CAMSET_L Examples
Example: Compressing Latin Values with CAMSET_L
Example: Querying for Compressed Latin Values
CAMSET_L Related Information
DECAMSET
DECAMSET Syntax
DECAMSET Argument Type and Rules
DECAMSET Result Type
DECAMSET Usage Notes
Example: Decompressing Unicode Values with DECAMSET
DECAMSET_L
DECAMSET_L Syntax
DECAMSET_L Argument Type and Rules
DECAMSET_L Result Type
DECAMSET_L Usage Notes
Example: DECAMSET_L Function
JSON_COMPRESS
JSON_COMPRESS Syntax
JSON_COMPRESS Argument Type and Rules
JSON_COMPRESS Result Type
Example: Compressing JSON Data Types
JSON_DECOMPRESS
JSON_DECOMPRESS Syntax
JSON_DECOMPRESS Argument Type and Rules
JSON_DECOMPRESS Result Type
Example: Decompressing JSON Data Types
LZCOMP
LZCOMP Syntax
LZCOMP Argument Type and Rules
LZCOMP Result Type
LZCOMP Usage Notes
Uncompressing Data Compressed with LZCOMP
LZCOMP Examples
Example: Compressing Unicode Values with LZCOMP
Example: Querying for the Compressed Unicode Values of the Description column
LZCOMP_L
LZCOMP_L Syntax
LZCOMP_L Argument Type and Rules
LZCOMP_L Result Type
LZCOMP_L Usage Notes
Uncompressing Data Compressed with LZCOMP_L
LZCOMP_L Examples
Example: Compressing Latin Values with LZCOMP_L
Example: Querying for the Compressed Latin Values of the Description column
LZDECOMP
LZDECOMP Syntax
LZDECOMP Argument Type and Rules
LZDECOMP Result Type
LZDECOMP Usage Notes
Example: Compressing Unicode Values with LZDECOMP
LZDECOMP_L
LZDECOMP_L Syntax
LZDECOMP_L Argument Type and Rules
LZDECOMP_L Result Type
LZDECOMP_L Usage Notes
Example: Decompressing Latin Values with LZDECOMP_L
LZDECOMP_L Related Information
TD_LZ_COMPRESS
TD_LZ_COMPRESS Syntax
TD_LZ_COMPRESS Argument Type and Rules
TD_LZ_COMPRESS Result Type
TD_LZ_COMPRESS Usage Notes
Uncompressing Data Compressed with TD_LZ_COMPRESS
TD_LZ_DECOMPRESS
TD_LZ_DECOMPRESS Syntax
TD_LZ_DECOMPRESS Argument Type and Rules
TD_LZ_DECOMPRESS Result Type
TD_LZ_DECOMPRESS Usage Notes
TS_COMPRESS
TS_COMPRESS Syntax
TS_COMPRESS Argument Type and Rules
TS_COMPRESS Result Type
TS_COMPRESS Usage Notes
TS_COMPRESS Examples
Example: Compressing the TIME Data Type
Example: Compressing the TIMESTAMP Data Type
TS_DECOMPRESS
TS_DECOMPRESS Syntax
TS_DECOMPRESS Argument Type and Rules
TS_DECOMPRESS Result Type
Example: Creating a Compressed TIME Column
TransUnicodeToUTF8
TransUnicodeToUTF8 Syntax
TransUnicodeToUTF8 Argument Type and Rules
TransUnicodeToUTF8 Result Type
TransUnicodeToUTF8 Usage Notes
TransUnicodeToUTF8 Restrictions
Example: Uncompressing Data Compressed with TransUnicodeToUTF8
TransUTF8ToUnicode
TransUTF8ToUnicode Syntax
TransUTF8ToUnicode Argument Type and Rules
TransUTF8ToUnicode Result Type
TransUnicodeToUTF8 Usage Notes
Example: Uncompressing Unicode Values with TransUTF8ToUnicode
Export Width Procedures
Export Width
ReplaceExportDefinition
ReplaceExportDefinition Syntax
ReplaceExportDefinition Argument Types
ReplaceExportDefinition Usage Notes
Export Width Rules
Example: Replacing Export Definition Authorization
RemoveExportDefinition
RemoveExportDefinition Syntax
RemoveExportDefinition Argument Types
RemoveExportDefinition Usage Notes
Example: RemoveExportDefinition
File System Information Macros and Functions
CreateFsysInfoTable/CreateFsysInfoTable_ANSI
CreateFsysInfoTable/CreateFsysInfoTable_ANSI Syntax
CreateFsysInfoTable/CreateFsysInfoTable_ANSI Examples
Example: Create a File System Information Table for Short Display for SHOWBLOCKS
Example: Create a File System Information Table for Short Display for SHOWCOMPRESS
Example: Create a File System Information Table for Short Display for SHOWWHERE
Example: Create a File System Information Table for Short Display for SHOWOFSSPACE
PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI
PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI Syntax
PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI Syntax Elements
PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI Examples
Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'S'
Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'M'
Example: Populate a File System Information Table for SHOWBLOCKS Output, Option 'L'
Example: Populate a File System Information Table for SHOWCOMPRESS Output, Option 'S'
Example: Populate a File System Information Table for SHOWCOMPRESS Output, Option 'L'
Example: Populate a File System Information Table for SHOWWHERE Output, Option 'S'
Example: Populate a File System Information Table for SHOWWHERE Output, Option 'M'
Example: Populate a File System Information Table for SHOWWHERE Output, Option 'L'
Examples Using CLASSSPOOL and SHOWWHERE
Example: Populate a File System Information Table for SHOWWHERE and SPOOL CLASS Output, 'S' Option
Example: Populate a File System Information Table for SHOWWHERE and SPOOL CLASS Output, 'M' Option
Example: Populate a File System Information Table for SHOWWHERE and SPOOL CLASS Output, 'L' Option
Example: Populate a File System Information Table for SHOWOFSSPACE Output
AlterFsysInfoTable_TD16/AlterFsysInfoTable_ANSI_TD16
AlterFsysInfoTable_TD16/AlterFsysInfoTable_ANSI_TD16 Syntax
AlterFsysInfoTable_TD16/AlterFsysInfoTable_ANSI_TD16 Examples
Example: Using the PopulateFsysInfoTable Macro to Populate a Pre-Existing Table
Example: Migrating Tables to a Prior Release
tdheatmap/tdheatmap_m
Heatmap Table Function and Macro (tdheatmap and tdheatmap_m) Syntax
Differences between the tdheatmap Function, Macro, and View
Returned Columns
tdheatmap and tdheatmap_m Usage Notes
Heatmap Table Function and Macro (tdheatmap and tdheatmap_m) Examples
Example: Using the tdheatmap Function
Example: Using the tdheatmap_m Macro
Example: Using tdheatmap to Create a View
Example: Data Temperature Report Showing a Specific Table
Example: Showing Cylinders in TIM Cache
Example: Heatmap Report Showing Tables with Different Subtable Types
Map Functions, Macros, and Procedures
Map Functions
SYSLIB.ContiguousMapAMPs
SYSLIB.ContiguousMapAMPs Syntax
ContiguousMapAMPs Usage Notes
Example: Identifying Which AMPs are in a Contiguous Map
SYSLIB.SparseMapAMPs
SYSLIB.SparseMapAMPs Syntax
SparseMapAMPs Usage Notes
Example: Identifying an AMP with a Specific Table
SYSLIB.SparseTableAMPs
SYSLIB.SparseTableAMPs Syntax
SparseTableAMPs Usage Notes
Example: Identifying AMPs for a Table with a Sparse Map
Map Macros
Map Procedures
AdjustSpace
AdjustSpace Syntax
BufferPercent Argument Types
AdjustSpace Result Type
AdjustSpace Usage Notes
AdjustSpace Examples
Example: Adjusting the Space Settings after a System Expansion
Example: Using AdjustSpace
Advisor Procedures
AddExclusionListEntrySP(X)
AddExclusionListEntrySP(X) Syntax
AddExclusionListEntrySP and AddExclusionListEntrySPX Argument Types
AddExclusionListEntrySP(X) Usage Notes
AddExclusionListEntrySP(X) Examples
Example: Creating an Exclusion List Named ‘MyExclusions’
Example: Analyzing Exclusions Defined in 'MyExclusions'
AddMapListEntrySP(X)
AddMapListEntrySP(X) Syntax
AddMapListEntrySP or AddMapListEntrySPX Argument Types
AddMapListEntrySP(X) Usage Notes
Example: Calling AddMapListEntrySP(X)
AddObjectListEntrySP(X)
AddObjectListEntrySP(X) Syntax
AddObjectListEntrySP and AddObjectListEntrySPX Argument Types
AddObjectListEntrySP(X) Usage Notes
AddObjectListEntrySP(X) Examples
Example: Creating an Object List
Example: Analyzing Objects Defined in MyObjects
Example: Creating Object List ObjNotInMap2
AnalyzeSP
AnalyzeSP Syntax
AnalyzeSP Argument Types
AnalyzeSP Result Type
AnalyzeSP Usage Notes
Example: Using AnalyzeSP
CleanUpAnalyzerSP
CleanUpAnalyzerSP Syntax
CleanUpAnalyzerSP Argument Types
Example: Calling CleanUpAnalyzerSP
CreateExclusionListSP
CreateExclusionListSP Syntax
CreateExclusionListSP Argument Types
CreateExclusionListSP Result Type
CreateExclusionListSP Usage Notes
Example: Calling CreateExclusionListSP
CreateExpansionMaps
CreateExpansionMaps Syntax
CreateExpansionMaps Usage Notes
Example: Using CreateExpansionMaps
CreateMapListSP
CreateMapListSP Syntax
CreateMapListSP Argument Types
CreateMapListSP Result Type
CreateMapListSP Usage Notes
Example: Calling CreateMapListSP
CreateObjectListSP
CreateObjectListSP Syntax
CreateObjectListSP Argument Types
CreateObjectListSP Result Type
CreateObjectListSP Usage Notes
Example: Calling CreateObjectListSP
MonitorAnalyzeSP
MonitorAnalyzeSP Syntax
MonitorAnalyzeSP Argument Types
MonitorAnalyzeSP Result Type
Example: Calling MonitorAnalyzeSP
PostExpansionAction
PostExpansionAction Syntax
PostExpansionAction Usage Notes
Example: Using PostExpansionAction
Mover Procedures
ManageMoveTablesSP
ManageMoveTablesSP Syntax
ManageMoveTablesSP Argument Types
ManageMoveTablesSP Result Type
ManageMoveTablesSP Usage Notes
Example: Running the Actions in the MyNewMapActions List
MoveTablesSP
MoveTablesSP Syntax
MoveTablesSP Argument Types
MoveTablesSP Usage Notes
ManageMoveTablesSP Examples
Example: Using BTEQ to Run Queued Actions
Example: Move a Table to a Map
MonitorMoveTablesSP
MonitorMoveTablesSP Syntax
MonitorMoveTablesSP Argument Types
MonitorMoveTablesSP Result Type
Example: Using MonitorMoveTablesSP
StopMoveTablesSP
StopMoveTablesSP Syntax
StopMoveTablesSP Argument Types
StopMoveTablesSP Result Type
StopMoveTablesSP Usage Notes
Example: Using StopMoveTablesSP
StopMoverSP
StopMoverSP Syntax
StopMoverSP Argument Types
Example: Using StopMoverSP
StopSerialWorkerSP
StopSerialWorkerSP Syntax
StopSerialWorkerSP Usage Notes
Example: Using StopSerialWorkerSP
CleanUpMoveTablesSP
CleanUpMoveTablesSP Syntax
CleanUpMoveTablesSP Usage Notes
Example: Calling CleanUpMoveTablesSP
MoveTDMapsTablesSP
MoveTDMapsTablesSP Syntax
MoveTDMapsTablesSP Argument Types
MoveTDMapsTablesSP Result Type
Example: Calling MoveTDMapsTablesSP
TruncateHistorySP
TruncateHistorySP Syntax
TruncateHistorySP Argument Types
TruncateHistorySP Result Type
TruncateHistorySP Usage Notes
Example: Calling TruncateHistorySP
Table Operators
APPLY
APPLY Syntax
APPLY Syntax Elements
CSV Style
Input and Output to the APPLY_COMMAND
NULL Handling
APPLY Table Operator Data Types
Setting the User Environment
Running APPLY Table Operator
Permissions Required to Use APPLY Table Operator
Troubleshooting
APPLY Example
Example: APPLY Table Operator Function
JSON Data Type Related Information
CALCMATRIX
CALCMATRIX Syntax
CALCMATRIX Data Types
CALCMATRIX Usage Notes
CALCMATRIX Examples
Example: One Input Matrix, Single SQL Statement
Example: One Input Matrix, Multiple SQL Statements
Example: Multiple Input Matrixes, One SQL Statement
Cogroups
Example: Cogroup Used for a Table Operator with Multiple Inputs
FeatureNames_TBF
FeatureNames_TBF Syntax
FeatureNames_TBF Columns
Example: Generating a Report on Feature Usage
TD_DBQLParam
TD_DBQLParam Syntax
TD_DBQLParam Output
TD_DBQLParam Usage Notes
TD_DBQLParam Example
TD_DBQLFUL
TD_DBQLFUL Syntax
TD_DBQLFUL Output
TD_DBQLFUL Usage Notes
TD_DBQLFUL Examples
TD_UNPIVOT
TD_UNPIVOT Syntax
TD_UNPIVOT Data Types
TD_UNPIVOT Examples
Example: Twelve Columns Unpivoted to One
Example: More than One Value Column
Examples Usage Notes
Datalake Table Operators
TD_SNAPSHOTS
TD_MANIFESTS
TD_HISTORY
TD_PARTITIONS
User-Defined Functions
Scalar UDF
Scalar UDF Syntax
Scalar UDF Restrictions
Scalar UDF Arguments
Scalar UDF Result Type
Scalar UDF Default Title
Scalar UDF Examples
Scalar UDF Related Information
Aggregate UDF
Aggregate UDF Syntax
Aggregate UDF Restrictions
Aggregate UDF Usage Notes
Example: Aggregate UDF
Window Aggregate UDF
Window Aggregate UDF Syntax
Window Aggregate UDF Syntax Elements
Arguments to Window Aggregate UDFs
Supported Window Types for Aggregate UDFs
Unsupported Window Types for Aggregate UDFs
Partitioning
Window Aggregate UDF Result Type and Format
Window Aggregate UDF Usage Notes
Problems with Missing Data
Window Aggregate UDF Restrictions
Example: Window Aggregate
SQL UDF
SQL UDF Function Syntax
Required Privileges
SQL UDF Usage Notes
SQL UDF Function Examples
Example: Defining the Function and Query
Example: Referencing an External UDF
Example: Invoking the SQL UDF
Example: Invoking Compatible Argument Data Types
Example: Invoking Argument Data Types of Different Sizes
Table User-Defined Functions
Table UDF Restrictions
Table UDF Usage Notes
UDF Invocation
Script Installation Procedures
Execution Rules
Installing and Registering External Language Scripts
SYSUIF.INSTALL_FILE
Example: Calling SYSUIF.INSTALL_FILE to Install a Python Script
Replacing External Language Scripts
Privileges Needed to Replace External Language Scripts
SYSUIF.REPLACE_FILE
Example: Replacing an Existing File Mapper
Redistributing External Language Scripts
Privileges Needed to Redistribute External Language Scripts
SYSUIF.REDISTRIBUTE_FILE
Example: Redistributing External Language Script
Displaying the User-Installed File
Removing External Language Scripts
Complete Prerequisites for Removing External Script
SYSUIF.REMOVE_FILE
Example: Removing External Language Script
SQL Date and Time Functions and Expressions
Business Calendars
Calendar Differences
ISO Computation
ISO Computation Week
ISO Computation Year
ISO Computation Month
ISO Computation Quarter
Business Calendar Macros
CreateBusinessCalendarPattern
CreateBusinessCalendarPattern Required Parameters
CreateBusinessCalendarPattern Examples
Example: Setting Non-Working Days
Example: Designating Working Days
CreateException
CreateException Required Parameters
CreateException Usage Notes
Example: Defining OFF and ON Exceptions
DeleteException
DeleteException Required Parameters
Example: Deleting Exceptions
DeleteAllExceptions
DeleteAllExceptions Required Parameter
Example: Removing Exceptions from the ISO Business Calendar
Business Calendar Tables
DBC.BusinessCalendarPattern Table
DBC.BusinessCalendarException Table
Business Calendar Views
Sys_Calendar.BusinessCalendarExceptions
Sys_Calendar.BusinessCalendarPatterns
Sys_Calendar.BusinessCalendar
Business Calendar View Examples
Example: Querying for Day of the Week Using the ISO Calendar
Example: Querying for Day of the Week Using the COMPATIBLE Calendar
Example: Querying for Beginning of the Week Using the ISO Calendar
Example: Querying for Beginning of the Week Using the COMPATIBLE calendar
Example: Adding an OFF Exception Day
Example: Querying for Beginning of the Week and the Week Number
Business Calendar Functions
Prerequisites for Using the Business Calendar Functions
Business Calendar Function Examples
Example: Cumulative Sales during First Week of All Quarters - Teradata Calendar
Example: Cumulative Sales during First Week of All Quarters - ISO Calendar
Example: Cumulative Sales during First Week of All Quarters - Compatible Calendar
Example: Cumulative Weekday Sales during First Week of All Quarters - Teradata Calendar
Example: Show Week 1 of September 2011
Example: Inventory Status at the Beginning of Each Week in a Month
Example: Number of Weeks in Each Quarter in 2004
Example: Sales by Quarter
td_week_begin
td_week_begin Syntax
td_week_begin Argument Types
Example: Querying the Week before a DATE or TIMESTAMP
td_week_end
td_week_end Argument Types
Example: Querying the Week End after a DATE or TIMESTAMP
td_sunday
td_sunday Syntax
td_sunday Argument Types
Example: Querying the Sunday before a DATE or TIMESTAMP
td_monday
td_monday Syntax
td_monday Argument Types
Example: Querying the Monday before a DATE or TIMESTAMP
td_tuesday
td_tuesday Syntax
td_tuesday Argument Types
Example: Querying the Tuesday before a DATE or TIMESTAMP
td_wednesday
td_wednesday Syntax
td_wednesday Argument Types
Example: Querying the Wednesday before the DATE or TIMESTAMP
td_thursday
td_thursday Syntax
td_thursday Argument Types
Example: Querying the Thursday before a DATE or TIMESTAMP
td_friday
td_friday Syntax
td_friday Argument Types
Example: Querying the Friday before a DATE or TIMESTAMP
td_saturday
td_saturday Syntax
td_saturday Argument Types
Example: Querying the Saturday before a DATE or TIMESTAMP
DayNumber_Of_Week
DayNumber_Of_Week Syntax
DayNumber_Of_Week Argument Types
Example: Querying Employee Names
td_month_begin
td_month_begin Syntax
td_month_begin Argument Types
Example: Querying for the Month Beginning Immediately before the DATE or TIMESTAMP
td_month_end
td_month_end Syntax
td_month_end Argument Types
Example: Querying for the Month Ending Immediately after the DATE or TIMESTAMP
DayNumber_Of_Month
DayNumber_Of_Month Syntax
DayNumber_Of_Month Argument Types
Examples: Querying the Number of Days from the Month Beginning
DayOccurrence_Of_Month
DayOccurrence_Of_Month Syntax
DayOccurrence_Of_Month Argument Types
Example: Querying the nth Occurrence of the Weekday in the Month
WeekNumber_Of_Month
WeekNumber_Of_Month Syntax
WeekNumber_Of_Month Argument Types
Example: Querying the Number of Weeks from the Month Beginning
td_year_begin
td_year_begin Syntax
td_year_begin Argument Types
Example: Querying the Year that Begins Immediately before the DATE or TIMESTAMP
td_year_end
td_year_end Syntax
td_year_end Argument Types
Example: Querying the Year Ending after the DATE or TIMESTAMP
DayNumber_Of_Year
DayNumber_Of_Year Syntax
td_quarter_end Argument Types
Example: Querying the Year Ending Immediately after the DATE or TIMESTAMP
WeekNumber_Of_Year
WeekNumber_Of_Year Syntax
WeekNumber_Of_Year Argument Types
Example: Querying the Number of Weeks from the Year Beginning
MonthNumber_Of_Year
MonthNumber_Of_Year Syntax
MonthNumber_Of_Year Argument Types
Example: Querying the Number of Months from the Year Beginning
td_quarter_begin
td_quarter_begin Syntax
td_quarter_begin Argument Types
Example: Querying the Quarter Beginning Immediately before the DATE or TIMESTAMP
td_quarter_end
td_quarter_end Syntax
td_quarter_end Argument Types
Example: Querying the Quarter Ending Immediately after the DATE or TIMESTAMP
WeekNumber_Of_Quarter
WeekNumber_Of_Quarter Syntax
WeekNumber_Of_Quarter Argument Types
Example: Querying the Number of Weeks from the Beginning of the Quarter
MonthNumber_Of_Quarter
MonthNumber_Of_Quarter Syntax
MonthNumber_Of_Quarter Argument Types
Example: Querying the Number of Months from the Quarter Beginning
QuarterNumber_Of_Year
QuarterNumber_Of_Year Syntax
QuarterNumber_Of_Year Argument Types
Example: Querying the Number of Quarters from the Year Beginning
DayNumber_Of_Calendar
DayNumber_Of_Calendar Syntax
DayNumber_Of_Calendar Argument Types
Example: Querying the Number of Days from the Beginning of the Business Calendar
WeekNumber_Of_Calendar
WeekNumber_Of_Calendar Syntax
WeekNumber_Of_Calendar Argument Types
Example: Querying the Number of Weeks from the Beginning of the Business Calendar
MonthNumber_Of_Calendar
MonthNumber_Of_Calendar Syntax
MonthNumber_Of_Calendar Argument Types
Example: Querying Number of Months from the Beginning of the Calendar
QuarterNumber_Of_Calendar
QuarterNumber_Of_Calendar Syntax
QuarterNumber_Of_Calendar Argument Types
Example: Query the Number of Quarters from the Beginning of the Calendar
YearNumber_Of_Calendar
YearNumber_Of_Calendar Syntax
YearNumber_Of_Calendar Argument Types
Example: Querying the Year of the Specified Date
Calendar Functions
td_day_of_week/DayOfWeek
td_day_of_week/DayOfWeek Syntax
td_day_of_week/DayOfWeek Argument Types
Example: Querying for the Day of the Week on Which the Specified Date Falls
td_day_of_month
td_day_of_month Syntax
td_day_of_month Argument Types
Example: Querying for the Number of Days from the Beginning of the Month
td_day_of_year
td_day_of_year Syntax
td_day_of_year Argument Types
Example: Querying for the Number of Days from the Beginning of the Year
td_weekday_of_month
td_weekday_of_month Syntax
td_weekday_of_month Argument Types
Example: Querying for the nth Occurrence of the Weekday in the Month
td_week_of_month
td_week_of_month Syntax
td_week_of_month Argument Types
Example: Querying for the nth Full Week from the Beginning of the Month
td_week_of_year
td_week_of_year Syntax
td_week_of_year Argument Types
Example: Querying for the nth Full Week from the Beginning of the Year
td_week_of_calendar
td_week_of_calendar Syntax
td_week_of_calendar Argument Types
Example: Querying for the Number of Weeks from the Beginning of the Calendar
td_month_of_quarter
td_month_of_quarter Syntax
td_month_of_quarter Argument Types
Example: Querying for the Number of Months from the Beginning of the Quarter
td_month_of_year
td_month_of_year Syntax
td_month_of_year Argument Types
Example: Querying for the Number of Months from the Beginning of the Year
td_month_of_calendar
td_month_of_calendar Syntax
td_month_of_calendar Argument Types
Example: Querying for the Number of Months from the Beginning of the Calendar Year
td_quarter_of_year
td_quarter_of_year Syntax
td_quarter_of_year Argument Types
Example: Querying for the Quarter Number of the Year
td_quarter_of_calendar
td_quarter_of_calendar Syntax
td_quarter_of_calendar Argument Types
Example: Querying for the Number of Quarters from the Beginning of the Calendar
td_year_of_calendar
td_year_of_calendar Syntax
td_year_of_calendar Argument Types
Example: Querying for the Year of the Specified Date
td_year_of_calendar Related Information
DateTime and Interval Functions and Expressions
ANSI DateTime Data Types
ANSI DateTime and Interval Data Type Assignment Rules
Data Type Compatibility and Conversion
CAST Syntax
Interval Data Type Assignment Rules
Scalar Operations on ANSI SQL:2011 DateTime and Interval Values
Data Type Compatibility
ANSI DateTime Expressions
AT LOCAL and AT TIME ZONE Time Zone Specifiers
Gregorian Calendar Rules
Evaluation Types
Adding and Subtracting Interval Values
Computations with Time Zones
ANSI DateTime Expression Examples
Example: date_time_primary
Example: date_time_term with an Integer Numeric Time Zone Specifier
Example: date_time_term with a Scaled Decimal Time Zone Specifier
Example: date_time_term with an 'hh:mm' String Time Zone Specifier
Example: date_time_term with an Interval Column Time Zone Specifier
Example: date_time_term with an Interval Literal Time Zone Specifier
Example: date_time_term with a Time Zone String Time Zone Specifier
Example: date_time_expression
Example: date_time_expression with Addition
Example: date_time_expression with Subtraction
Time Zone Sort Order
ANSI DateTime Expression Examples Related Information
ANSI Interval Expressions
ANSI Interval Expression Syntax
ANSI Interval Expressions Usage Notes
ANSI Interval Expressions Rules
Normalization of Intervals with Multiple Fields
ANSI Interval Expression Examples
Example: interval_term
Example: numeric_factor
Example: interval_term / numeric_factor
Example: numeric_term * interval_primary
Example: numeric_term * ± interval_primary
Example: interval_expression
Arithmetic Operators and ANSI DateTime and Interval Data Types
Arithmetic Operators and Result Types
Adding or Subtracting Numbers from DATE
Calculating the Difference between Two DateTime Values
Arithmetic Operators and ANSI DateTime and Interval Data Types Examples
Example: Calculating the Difference in Days between DATE Values
Example: Adding Interval to DATE
Aggregate Functions and ANSI DateTime and Interval Data Types
Interval Data Types
Scalar Operations and DateTime Functions
Example: Using the CURRENT_DATE DateTime Function
Teradata Date and Time Expressions
DATE and Date Arithmetic
Example: DATE/2 Integer Result
Scalar Operations on Teradata DATE Values
ADD_MONTHS Function
EXTRACT Function
YEAR/MONTH/DAYOFMONTH/HOUR/MINUTE/SECOND
YEAR/MONTH/DAYOFMONTH/HOUR/MINUTE/SECOND Syntax
YEAR/MONTH/DAYOFMONTH/HOUR/MINUTE/SECOND Examples
WEEK
WEEK Syntax
Examples: SELECT WEEK
LAST_DAY
LAST_DAY Syntax
LAST_DAY Argument Types and Rules
Examples: LAST_DAY Function
NEXT_DAY
NEXT_DAY Syntax
NEXT_DAY Argument Types and Rules
Example: NEXT_DAY Function
MONTHS_BETWEEN
MONTHS_BETWEEN Syntax
MONTHS_BETWEEN Argument Types and Rules
MONTHS_BETWEEN Examples
Example: Querying for the Number of Months between Two Dates
Example: Querying for the Number of Months for One Date
ADD_MONTHS
ADD_MONTHS Syntax
ADD_MONTHS Usage Notes
ADD_MONTHS Rules
Scalar Arithmetic on Months Issues
Normalization Behavior of ADD_MONTHS
Definition of Normalization
Non-Intuitive Results of ADD_MONTHS
ADD_MONTHS Summarized
ADD_MONTHS Examples
Example: Intuitive Examples
Example: Non-Intuitive Examples
OADD_MONTHS
OADD_MONTHS Syntax
OADD_MONTHS Argument Types and Rules
Examples: OADD_MONTHS Function
TRUNC(Date)
TRUNC(Date) Syntax
ROUND(Date) and TRUNC(Date) Argument Types and Rules
TRUNC and ROUND Date Formats
TRUNC(Date) Examples
Example: Returning a Date Value for the First Day of the Week
Example: Returning a Date Value for the Beginning of the Month
ROUND(Date)
ROUND(Date) Syntax
ROUND(Date) and TRUNC(Date) Argument Types and Rules
TRUNC and ROUND Date Formats
ROUND(Date) Examples
Example: Rounding the Date to the First Day of the Week
Example: Rounding the Date to the Beginning of the Next Month
EXTRACT
EXTRACT Syntax
EXTRACT Result Types
Examples: EXTRACT Function
GetTimeZoneDisplacement
GetTimeZoneDisplacement Syntax
GetTimeZoneDisplacement Result Type
GetTimeZoneDisplacement Usage Notes
Limitation on the Use of TimeZone Strings
Add or Modify Time Zone Strings
TZ_DST Structure
Example: Adding a New Time Zone String
Period Functions and Operators
Period Value Constructor
Period Value Constructor Syntax
Period Value Constructor Usage Notes
Period Value Constructor Result Value Rules
Period Value Constructor Rules
Example: Period Value Constructor Function
Arithmetic Operators
Arithmetic Operator Syntax
Arithmetic Operator Usage Notes
Comparison of Period Types
Comparison Operators
BEGIN
BEGIN Syntax
BEGIN Usage Notes
BEGIN Examples
BEGIN Example 1
BEGIN Example 2
CONTAINS
CONTAINS Syntax
CONTAINS Usage Notes
CONTAINS Examples
CONTAINS Example 1
CONTAINS Example 2
END
END Syntax
END Usage Notes
END Examples
END Example 1
END Example 2
EQUALS
EQUALS Syntax
EQUALS Examples
EQUALS Example 1
EQUALS Example 2
IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED
IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED Syntax
IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED Usage Notes
IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED Examples
IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED Example 1
IS UNTIL_CHANGED/IS NOT UNTIL_CHANGED Example 2
IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED
IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED Syntax
IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED Usage Notes
IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED Examples
IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED Example 1
IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED Example 2
IMMEDIATELY PRECEDES
IMMEDIATELY PRECEDES Syntax
IMMEDIATELY PRECEDES Examples
IMMEDIATELY PRECEDES Example 1
IMMEDIATELY PRECEDES Example 2
IMMEDIATELY SUCCEEDS
IMMEDIATELY SUCCEEDS Syntax
IMMEDIATELY SUCCEEDS Examples
IMMEDIATELY SUCCEEDS Example 1
IMMEDIATELY SUCCEEDS Example 2
INTERVAL
INTERVAL Syntax
INTERVAL Usage Notes
INTERVAL Examples
Example: Using the INTERVAL Function
Example: Running a Query on the Employee Table
Example: Returning the Interval Value of a Derived Period
LAST
LAST Syntax
LAST Usage Notes
LAST Examples
LAST Example 1
LAST Example 2
MEETS
MEETS Syntax
MEETS Usage Notes
MEETS Examples
MEETS Example 1
MEETS Example 2
NEXT
NEXT Syntax
NEXT Usage Notes
Example: NEXT Function
OVERLAPS
OVERLAPS Syntax
OVERLAPS Result Type
OVERLAPS Usage Notes
OVERLAPS Time Periods
OVERLAPS Rules
OVERLAPS Examples
OVERLAPS Example 1
OVERLAPS Example 2
OVERLAPS Example 3
OVERLAPS Example 4
OVERLAPS Example 5
OVERLAPS Example 6
OVERLAPS Example 7
OVERLAPS Example 8
P_INTERSECT
P_INTERSECT Syntax
P_INTERSECT Result Type
P_INTERSECT Usage Notes
Example: P_INTERSECT Function
PRECEDES
PRECEDES Syntax
PRECEDES Result Type
PRECEDES Usage Notes
PRECEDES Examples
PRECEDES Example 1
PRECEDES Example 2
PRIOR
PRIOR Syntax
PRIOR Usage Notes
Example: PRIOR Function
LDIFF
LDIFF Syntax
LDIFF Result Type
LDIFF Usage Notes
Example: LDIFF Function
RDIFF
RDIFF Syntax
RDIFF Result Type
RDIFF Usage Notes
Example: RDIFF Function
SUCCEEDS
SUCCEEDS Syntax
SUCCEEDS Result Type
SUCCEEDS Usage Notes
SUCCEEDS Examples
SUCCEEDS Example 1
SUCCEEDS Example 2
TD_NORMALIZE_OVERLAP
TD_NORMALIZE_OVERLAP Syntax
TD_NORMALIZE_OVERLAP Usage Notes
TD_NORMALIZE_OVERLAP Input and Output
TD_NORMALIZE_OVERLAP Format and Title
TD_NORMALIZE_OVERLAP Error Conditions
Example: Using TD_NORMALIZE_OVERLAP
TD_NORMALIZE_MEET
TD_NORMALIZE_MEET Syntax
TD_NORMALIZE_MEET Usage Notes
TD_NORMALIZE_MEET Input and Output
TD_NORMALIZE_MEET Format and Title
TD_NORMALIZE_MEET Error Conditions
Example: Using TD_NORMALIZE_MEET
TD_NORMALIZE_OVERLAP_MEET
TD_NORMALIZE_OVERLAP_MEET Syntax
TD_NORMALIZE_OVERLAP_MEET Usage Notes
TD_NORMALIZE_OVERLAP_MEET Input and Output
TD_NORMALIZE_OVERLAP_MEET Format and Title
TD_NORMALIZE_OVERLAP_MEET Error Conditions
Example: Using TD_NORMALIZE_OVERLAP_MEET
TD_SUM_NORMALIZE_OVERLAP
TD_SUM_NORMALIZE_OVERLAP Syntax
TD_SUM_NORMALIZE_OVERLAP Usage Notes
TD_SUM_NORMALIZE_OVERLAP Input and Output
TD_SUM_NORMALIZE_OVERLAP Format and Title
TD_SUM_NORMALIZE_OVERLAP Error Conditions
Example: Using TD_SUM_NORMALIZE_OVERLAP
TD_SUM_NORMALIZE_MEET
TD_SUM_NORMALIZE_MEET Syntax
TD_SUM_NORMALIZE_MEET Usage Notes
TD_SUM_NORMALIZE_MEET Input and Output
TD_SUM_NORMALIZE_MEET Format and Title
TD_SUM_NORMALIZE_MEET Error Conditions
Example: Using TD_SUM_NORMALIZE_MEET
TD_SUM_NORMALIZE_OVERLAP_MEET
TD_SUM_NORMALIZE_OVERLAP_MEET Syntax
TD_SUM_NORMALIZE_OVERLAP_MEET Usage Notes
Example: Using TD_SUM_NORMALIZE_OVERLAP_MEET
TD_SEQUENCED_SUM
TD_SEQUENCED_SUM Syntax
TD_SEQUENCED_SUM Usage Notes
TD_SEQUENCED_SUM Input and Output
TD_SEQUENCED_SUM Format and Title
TD_SEQUENCED_SUM Error Conditions
Example: Using TD_SEQUENCED_SUM
TD_SEQUENCED_AVG
TD_SEQUENCED_AVG Syntax
TD_SEQUENCED_AVG Usage Notes
TD_SEQUENCED_AVG Input and Output
TD_SEQUENCED_AVG Format and Title
TD_SEQUENCED_AVG Error Conditions
Example: Using TD_SEQUENCED_AVG
TD_SEQUENCED_COUNT
TD_SEQUENCED_COUNT Syntax
TD_SEQUENCED_COUNT Usage Notes
TD_SEQUENCED_COUNT Input and Output
TD_SEQUENCED_COUNT Format and Title
TD_SEQUENCED_COUNT Error Conditions
Example: Using TD_SEQUENCED_COUNT
SQL Functions, Expressions, and Predicates
Functions, Operators, Expressions, and Predicates
SQL Functions
Types of SQL Functions
Examples: Functions
Embedded Services System Functions
Activating Embedded Services System Functions
Invoking Embedded Services System Functions
Implicit Data Type Conversion Rules
SQL Operators
SQL Expressions
Types of Expressions
Expression Examples
SQL Predicates
Types of Logical Predicates
Logical Operators that Operate on Predicates
Predicate Quantifiers
Examples: Predicates
Aggregate Functions
Aggregates in the Select List
Aggregates and GROUP BY
Aggregates and Date
Aggregates and Literal Expressions in the Select List
Nesting Aggregates
Results of Aggregation on Zero Rows
Aggregates and Nulls
Aggregate Operations on Floating Point Data
Aggregates and LOBs
Aggregates and Period Data Types
Aggregates and SELECT AND CONSUME Statements
Aggregates and Recursive Queries
Aggregates in WHERE and HAVING Clauses
DISTINCT Option
Aggregates and Row Level Security Tables
Time Series Aggregate Functions Overview
Aggregate Functions Related Information
AVG
AVG Function Syntax
AVG Usage Notes
Example: Using the AVG Function
AVG Related Information
CORR
CORR Function Syntax
CORR Result Type and Attributes
CORR Usage Notes
Example: Querying Data from the HomeSales Table
COUNT
COUNT Function Syntax
COUNT Result Type and Attributes
COUNT Usage Notes
Examples: Using the COUNT Function
COUNT Related Information
COVAR_POP
COVAR_POP Function Syntax
COVAR_POP Result Type and Attributes
COVAR_POP Usage Notes
COVAR_SAMP
COVAR_SAMP Function Syntax
COVAR_SAMP Result Type and Attributes
COVAR_SAMP Usage Notes
Example: Using the SELECT Statement to Return the Sample Covariance of Weight and Height
GROUPING
GROUPING Function Syntax
GROUPING Result Type and Attributes
GROUPING Usage Notes
Example: Viewing Sales Summaries by County and by City
GROUPING Related Information
KURTOSIS
KURTOSIS Function Syntax
KURTOSIS Usage Notes
MAXIMUM
MAXIMUM Function Syntax
MAX Result Type and Attributes
MAXIMUM Usage Notes
Examples: Using the MAXIMUM Function
MINIMUM
MINIMUM Function Syntax
MIN Result Type and Attributes
MINIMUM Usage Notes
Examples: Using the MINIMUM Function
PIVOT
PIVOT Function Syntax
PIVOT Usage Notes
PIVOT Function Examples
Example: Alias Names Contained in the IN List
Example: Naming Columns with the column_value_list Values
Example: Pivot Operation on View
Example: Table Source Using the WITH Clause
Example: SELECT Query with the WHERE Condition
Example: CREATE TABLE AS Statement Contains Special Characters
Example: PIVOT Query Response in Different Response Modes
Example: Using TD_UNPIVOT or UNPIVOT as a Source to PIVOT
Example: Aggregation on Two Columns from PIVOT Results
Example: Subquery in PIVOT IN List
REGR_AVGX
REGR_AVGX Function Syntax
REGR_AVGX Result Type and Attributes
REGR_AVGX Usage Notes
Example: Returning the Mean Height for regrtbl
REGR_AVGY
REGR_AVGY Function Syntax
REGR_AVGY Result Type and Attributes
REGR_AVGY Usage Notes
Example: Returning the Mean Weight from regrtbl
REGR_COUNT
REGR_COUNT Function Syntax
REGR_COUNT Result Type and Attributes
REGR_COUNT Usage Notes
Example: Returning the Number of Rows in regrtbl
REGR_INTERCEPT
REGR_INTERCEPT Function Syntax
REGR_INTERCEPT Result Type and Attributes
REGR_INTERCEPT Usage Notes
Example: Returning the Intercept of the Regression Line for NbrSold and SalesPrice
REGR_R2
REGR_R2 Function Syntax
REGR_R2 Result Type and Attributes
REGR_R2 Usage Notes
Example: Returning the Coefficient of Determination for Height and Weight
REGR_SLOPE
REGR_SLOPE Function Syntax
REGR_SLOPE Result Type and Attributes
REGR_SLOPE Usage Notes
Example: Returning the Slope of the Regression Line for NbrSold and SalesPrice
REGR_SXX
REGR_SXX Function Syntax
REGR_SXX Result Type and Attributes
REGR_SXX Usage Notes
Example: Returning the Sum of Squares for Height
REGR_SXY
REGR_SXY Function Syntax
REGR_SXY Result Type and Attributes
REGR_SXY Usage Notes
Example: Returning the Sum of Products of Height and Weight
REGR_SYY
REGR_SYY Function Syntax
REGR_SYY Result Type and Attributes
REGR_SYY Usage Notes
Example: Returning the Sum of Squares for Weight
SKEW
SKEW Function Syntax
STDDEV_POP
STDDEV_POP Function Syntax
STDDEV_POP Usage Notes
STDDEV_SAMP
STDDEV_SAMP Function Syntax
STDDEV_SAMP Usage Notes
SUM
SUM Function Syntax
SUM Return Values
SUM Usage Notes
SUM Function Examples
Example: Accounts Receivable
Example: Face Value of Inventory
UNPIVOT
UNPIVOT Function Syntax
UNPIVOT Usage Notes
UNPIVOT Function Examples
Example: Unpivoted Sales and Cogs Columns
Example: Using UNPIVOT for a Unique Year Value
Example: Normalizing the UNPIVOT Operation
Example: Using UNPIVOT with the INCLUDE NULLS Clause
Example: Using UNPIVOT with the EXCLUDE NULLS Clause
Example: Using an IN List with Multiple Column Lists and Unspecified Aliases
Example: Using an IN List that Contains Multiple Columns with a Compatible Data Type
Example: Using an IN List that Contains Multiple Columns with an Incompatible Data Type
VAR_POP
VAR_POP Function Syntax
VAR_POP Usage Notes
VAR_SAMP
VAR_SAMP Function Syntax
VAR_SAMP Usage Notes
VAR_SAMP Related Information
Arithmetic, Trigonometric, Hyperbolic Operators/Functions
Binary Arithmetic Result Data Types
Result Data Type
DECIMAL Result Data Type
Numeric Results and Rounding
Binary Arithmetic Error Conditions
Integer Division and Truncation
Structure of Arithmetic Expressions
Order of Evaluation
Arithmetic Expression Format
Example: Determining Employee Salary Increases
Arithmetic Functions
ABS
ABS Function Syntax
ABS Argument Types and Rules
ABS Result Type and Attributes
Example: Using ABS Arithmetic Function Expressions
CASE_N
CASE_N Function Syntax
CASE_N Result Type and Attributes
CASE_N Usage Notes
Using CASE_N to Define Partitioned Primary Indexes
Using CASE_N with CURRENT_DATE or CURRENT_TIMESTAMP in a PPI
Using CASE_N with Character Comparison
CASE_N Restrictions
CASE_N Function Examples
Example: Defining the Partition to Which a Row is Assigned
Example: Using CASE_N in a List of Partitioning Expressions that Define a Multilevel PPI
Example: Showing the Count of Rows in Each Partition
Example: Creating a Table Partitioned with Orders Data
Example: Verifying the Ending Bound of a Period Expression
Example: Verifying the Ending Bound of a Transaction Time Column
Example: Viewing Results for FALSE Conditions
Example: Viewing Results for UNKNOWN Conditions
Example: Defining Partitions Based on the Value of a
CASE_N Related Information
CEILING
CEILING Function Syntax
CEILING Result Type and Attributes
Examples: CEILING Function
DEGREES/RADIANS
DEGREES/RADIANS Function Syntax
DEGREES/RADIANS Argument Types and Rules
DEGREES/RADIANS Result Type and Format
DEGREES/RADIANS Usage Notes
Examples: Representative DEGREES/RADIANS Function Expressions
EXP
EXP Function Syntax
EXP Argument Types and Rules
EXP Result Type and Attributes
EXP Usage Notes
Examples: Representative EXP Arithmetic Function Expressions
FLOOR
FLOOR Function Syntax
FLOOR Argument Types and Rules
FLOOR Result Type and Attributes
Examples: Using the FLOOR Function
HYPERBOLIC
HYPERBOLIC Function Syntax
HYPERBOLIC Argument Types and Rules
HYPERBOLIC Result Type and Attributes
LN
LN Function Syntax
LN Argument Types and Rules
LN Result Type and Attributes
Examples: Representative LN Arithmetic Function Expressions
LOG
LOG Function Syntax
LOG Argument Types and Rules
LOG Result Type and Attributes
Examples: Representative LOG Arithmetic Function Expressions
MOD
MOD Function Syntax
MOD Argument Types and Rules
MOD Result Type and Attributes
Example: Using MOD Arithmetic Function Expression
NULLIFZERO
NULLIFZERO Function Syntax
NULLIFZERO Argument Types and Rules
NULLIFZERO Result Type and Attributes
NULLIFZERO Function Examples
POWER
POWER Function Syntax
POWER Argument Types and Rules
POWER Result Type
Examples: Querying Returns for the Power of exponent_value
RANDOM
RANDOM Function Syntax
RANDOM Result Type and Attributes
RANDOM Usage Notes
RANDOM Restrictions
Multiple RANDOM Calls in a SELECT List
Using RANDOM as a Condition on an Index
Example: Returning Random Integer Numbers as Results
RANGE_N
RANGE_N Function Syntax
RANGE_N Result Type and Attributes
RANGE_N Usage Notes
RANGE_N Restrictions
Using RANGE_N to Define Partitioned Primary Indexes
Using RANGE_N with CURRENT_DATE or CURRENT_TIMESTAMP in a PPI
Using RANGE_N with Character Data
Examples: RANGE_N Function
RANGE_N Related Information
ROUND
ROUND Function Syntax
ROUND Argument Types and Rules
ROUND Result Type
ROUND Usage Notes
Examples: ROUND Function
SIGN
SIGN Function Syntax
SIGN Argument Types and Rules
SIGN Result Type
SIGN Usage Notes
Examples: SIGN Function
SQRT
SQRT Function Syntax
SQRT Argument Types and Rules
SQRT Result Type and Attributes
Examples: Representative SQRT Arithmetic Function Expressions
TRIGONOMETRIC
TRIGONOMETRIC Function Syntax
TRIGONOMETRIC Argument Types and Rules
TRIGONOMETRIC Result Type and Attributes
Examples: Representative Function Expressions
TRUNC
TRUNC Function Syntax
TRUNC Argument Types and Rules
TRUNC Result Type
TRUNC Usage Notes
Examples: Query Returns for SELECT TRUNC
WIDTH BUCKET
WIDTH BUCKET Function Syntax
WIDTH BUCKET Argument Types and Rules
WIDTH BUCKET Result Type and Attributes
WIDTH BUCKET Usage Notes
WIDTH BUCKET Rules
Example: Using WIDTH BUCKET to Create a Histogram for Employee Salaries in a Range
ZEROIFNULL
ZEROIFNULL Function Syntax
ZEROIFNULL Argument Types and Rules
ZEROIFNULL Result Type and Attributes
Example: Testing the Salary Column for Null
Attribute Functions
ANSI Equivalence of Teradata Attribute Functions
BIT_LENGTH
BIT_LENGTH Function Syntax
Example: BIT_LENGTH Function
BYTE/BYTES
BYTE/BYTES Function Syntax
BYTE/BYTES Usage Notes
Example: Using BYTE to Get the Number of Bytes in a Badge Picture
CHARACTER_LENGTH
CHARACTER_LENGTH Function Syntax
CHARACTER_LENGTH Result Type
CHARACTER_LENGTH Usage Notes
Examples: CHARACTER_LENGTH Function
DEFAULT
DEFAULT Function Syntax
DEFAULT Result Type and Attributes
DEFAULT Usage Notes
Omitting the Column Name
Using a Qualified Column Name
DEFAULT Function Error Conditions
DEFAULT Function Examples
Example: Inserting the Default Value under Certain Conditions
Example: Using DEFAULT in a Predicate
Example: Specifying a View Column Name
FORMAT
FORMAT Function Syntax
FORMAT Result Type and Attributes
Example: Requesting the Format of the Salary Column
OCTET_LENGTH
OCTET_LENGTH Function Syntax
OCTET_LENGTH Usage Notes
Examples: Output from OCTET_LENGTH
TITLE
TITLE Function Syntax
TITLE Result Type and Attributes
TITLE Usage Notes
Example: Requesting the Title of the Salary Column
TYPE
TYPE Function Syntax
TYPE Result Type and Attributes
TYPE Usage Notes
Examples: TYPE Function
Bit/Byte Manipulation Functions
Bit and Byte Numbering Model
BYTEINT
SMALLINT
INTEGER
BIGINT
BYTE and VARBYTE
HEXADECIMAL BYTE LITERALS
Performing Bit-Byte Operations against Arguments with Unequal Lengths
BITAND
BITAND Function Syntax
BITAND Argument Types and Rules
BITAND Result Type and Attributes
Examples: Querying with the BITAND Function
BITNOT
BITNOT Function Syntax
BITNOT Argument Types and Rules
BITNOT Result Type and Attributes
Example: Querying with the BITNOT Function
BITOR
BITOR Function Syntax
BITOR Argument Types and Rules
BITOR Result Type and Attributes
Example: Querying with the BITOR Function
BITXOR
BITXOR Function Syntax
BITXOR Argument Types and Rules
BITXOR Result Type and Attributes
Example: Querying with the BITXOR Function
COUNTSET
COUNTSET Function Syntax
COUNTSET Argument Types and Rules
COUNTSET Result Type and Attributes
Example: Querying with the COUNTSET Function
GETBIT
GETBIT Function Syntax
GETBIT Argument Types and Rules
GETBIT Result Type and Attributes
Example: Querying with the GETBIT Function
ROTATELEFT
ROTATELEFT Function Syntax
ROTATELEFT Argument Types and Rules
ROTATELEFT Result Type and Attributes
Examples: ROTATELEFT Function
ROTATERIGHT
ROTATERIGHT Function Syntax
ROTATERIGHT Argument Types and Rules
ROTATERIGHT Result Type and Attributes
Examples: ROTATERIGHT Function
SETBIT
SETBIT Function Syntax
SETBIT Argument Types and Rules
SETBIT Result Type and Attributes
Examples: SETBIT Function
SHIFTLEFT
SHIFTLEFT Function Syntax
SHIFTLEFT Argument Types and Rules
SHIFTLEFT Result Type and Attributes
Example: Querying with the SHIFTLEFT Function
SHIFTRIGHT
SHIFTRIGHT Function Syntax
SHIFTRIGHT Argument Types and Rules
SHIFTRIGHT Result Type and Attributes
Example: Querying with the SHIFTRIGHT Function
SUBBITSTR
SUBBITSTR Function Syntax
SUBBITSTR Argument Types and Rules
SUBBITSTR Result Type and Attributes
Example: Querying with the SUBBITSTR Function
TO_BYTE
TO_BYTE Function Syntax
TO_BYTE Argument Types and Rules
TO_BYTE Result Type and Attributes
Example: Querying with the TO_BYTE Function
Built-In Functions
ACCOUNT
ACCOUNT Function Syntax
ACCOUNT Result Type and Attributes
ACCOUNT Usage Notes
Example: Requesting Account Strings for a User
CURRENT_DATE/CURDATE
CURRENT_DATE/CURDATE Function Syntax
CURRENT_DATE Result Type and Attributes
CURRENT_DATE/CURDATE Usage Notes
CURRENT_DATE/CURDATE Function Examples
Example: Returning the Current Date for INTERVAL -'08:00' HOUR TO MINUTE
Example: Returning the Current Date for INTERVAL -'09:00' HOUR TO MINUTE
Example: Returning the Current Date for INTERVAL -'05:45' HOUR TO MINUTE
Example: Returning the Current Date for the Time Zone String, 'America Pacific'
Example: Changing Default CURRENT_DATE Output Format
CURRENT_ROLE
CURRENT_ROLE Function Syntax
CURRENT_ROLE Result Type and Attributes
CURRENT_ROLE Usage Notes
Example: Selecting CURRENT_ROLE
CURRENT_TIME/CURTIME
CURRENT_TIME/CURTIME Function Syntax
CURRENT_TIME Result Type and Attributes
CURRENT_TIME/CURTIME Usage Notes
CURRENT_TIME/CURTIME Function Examples
Example: Requesting the Current Time
Example: Requesting the Current Time with a Time Zone String
Example: Changing Default CURRENT_TIME Output Format
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP Function Syntax
CURRENT_TIMESTAMP Result Type and Attributes
CURRENT_TIMESTAMP Usage Notes
CURRENT_TIMESTAMP Function Examples
Example: Requesting the Current Timestamp with a Time Zone String
Example: Changing Default CURRENT_TIMESTAMP Output Format
CURRENT_USER
CURRENT_USER Function Syntax
CURRENT_USER Result Type and Attributes
CURRENT_USER Usage Notes
CURRENT_USER Function Examples
DATABASE
DATABASE Function Syntax
DATABASE Result Type and Attributes
Example: Requesting the Name of the Default Database
DATE
DATE Function Syntax
DATE Result Type and Attributes
DATE Usage Notes
Examples: DATE Function
NOW
NOW Function Syntax
NOW Result Type and Attributes
Example: NOW Function
PROFILE
PROFILE Function Syntax
PROFILE Result Type and Attributes
Example: Identifying Current Profile in a Session with PROFILE Function
ROLE
ROLE Function Syntax
ROLE Result Type and Attributes
ROLE Usage Notes
Example: Identifying the Session Current Role
SESSION
SESSION Function Syntax
SESSION Result Type and Attributes
Example: Identifying the Session Number for the Current User
TEMPORAL_DATE
TEMPORAL_DATE Function Syntax
TEMPORAL_DATE Result Type and Attributes
TEMPORAL_DATE Usage Notes
TEMPORAL_TIMESTAMP
TEMPORAL_TIMESTAMP Function Syntax
TEMPORAL_TIMESTAMP Result Type and Attributes
TEMPORAL_TIMESTAMP Usage Notes
TIME
TIME Function Syntax
TIME Result Type and Attributes
TIME Usage Notes
Examples: TIME Function
USER
USER Function Syntax
USER Result Type and Attributes
USER Function Examples
Example: Selecting the User Job Title
ZONE
ZONE Function Syntax
ZONE Result Type and Attributes
Example: ZONE Function
Comparison Operators and Functions
Comparison Operators
Comparison Operator Syntax
CASE Results
Comparison Operators Usage Notes
Supported Comparison Operators
Comparison Operators Using Subqueries
Example: Using the ALL Quantifier to Compare Two Expressions
Comparisons that Produce TRUE Results
Conditions
Null Expressions in Comparisons
Floating Point Expressions
Comparisons Related Information
Data Type Evaluation
Implicit Type Conversion of Comparison Operands
Data Types on Which Implicit Conversion is Performed
Implicit Conversion of DateTime Types
Data Types on Which Implicit Conversion is Not Performed
Comparison of ANSI DateTime and Interval in USING Clause
Proper Forms of DATE Types in Comparisons
Examples: CASE Function Comparison Operators
Character String Comparisons
Comparison of Character Strings of Unequal Length
Character Strings and Server Character Sets
Effect of Collation on Character String Comparisons
Case Sensitivity
Using UPPER for Case Blind Comparisons
Example: Querying for Case-Specific Names
Character String Comparisons Related Information
Comparison of KANJI1 Characters
Equality Comparison
Nonequality Comparison
Kanji Comparison Related Information
Comparison Operators and the DEFAULT Function in Predicates
DECODE
DECODE Function Syntax
DECODE Argument Types and Rules
DECODE Usage Notes
DECODE Function Examples
GREATEST
GREATEST Function Syntax
GREATEST and LEAST Argument Types and Rules
GREATEST and LEAST Usage Notes
GREATEST Result Type
Examples: GREATEST Function
LEAST
LEAST Function Syntax
GREATEST and LEAST Argument Types and Rules
GREATEST and LEAST Usage Notes
LEAST Result Type
Examples: LEAST Function
CASE Expressions
Valued CASE Expression
Valued CASE Expression Syntax
Valued CASE Expression Usage Notes
Valued CASE Expression Examples
Example: Calculating the Fraction of Cost
Example: Valued CASE Expression
Example: Combining a CASE Expression with a Concatenation Operator
Searched CASE Expression
Searched CASE Expression Syntax
Searched CASE Expression Usage Notes
Rules for WHEN Search Conditions
Restrictions on the Data Types in a CASE Expression
Searched CASE Expression Examples
Example: Evaluating a Search Condition
Example: Searched CASE Expression
Example: Using an ELSE Clause
Example: Using a CASE expression to Enhance Performance
Example: Producing a Report to Show Employee Salary
CASE Expression Error Conditions
Rules for the CASE Expression Result Type
THEN/ELSE Expressions Having the Same Non-Character Data Type
THEN/ELSE Character Type Expressions
CASE Expression Examples
Examples: Character Data in CASE Expression
Example: Differing Character Sets
Example: KANJI1 Literal
Example: Unicode Column
Example: LATIN Column
THEN/ELSE Expressions Having Mixed Data Types
Examples: Numeric Data in a CASE Expression
Example: CASE Expression Fails
Example: Shortening the Scale of the Multiplier
Example: Returning a DECIMAL(38,2) Result
Example: VARCHAR Result
Example: GRAPHIC Server Character Set
Format for a CASE Expression
Default Format
Using Explicit Type Conversion to Change Format
CASE and Nulls
Nulls and CASE Expressions
COALESCE Expression
COALESCE Expression Syntax
COALESCE Expression Usage Notes
COALESCE Expression Examples
NULLIF Expression
NULLIF Expression Syntax
NULLIF Usage Notes
Examples: NULLIF Expression
Hash-Related Functions
HASHAMP
HASHAMP Function Syntax
HASHAMP Usage Notes
HASHAMP Function Examples
HASHAMP Example Assumptions
Example: Querying the Distribution of Hash Buckets
Example: Querying the Number of Rows on Each Primary AMP
Example: HASHAMP with a Contiguous Map
Example: HASHAMP with an Expression and a Contiguous Map
Example: Which AMPs Contain the Rows of a Table
HASHBAKAMP
HASHBAKAMP Function Syntax
HASHBAKAMP Usage Notes
HASHBAKAMP Function Examples
HASHBUCKET
HASHBUCKET Function Syntax
HASHBUCKET Usage Notes
HASHBUCKET Function Examples
HASHROW
HASHROW Function Syntax
HASHROW Usage Notes
HASHROW Function Examples
Logical Predicates
Logical Predicate Usage Notes
Where Logical Predicates Are Used
Conditional Expressions as a Collection of Logical Primitives
Restrictions on the Data Types Involved in Predicates
Restrictions on the DEFAULT Function in a Predicate
Logical Operators and Search Conditions
Logical Operators Syntax
Where to Use Search Conditions
Rules for Order of Evaluation
Evaluation Results
AND Truth Table
OR Truth Table
NOT Truth Table
Subquery Restrictions
Examples: Logical Operators in Search Conditions
ANY/ALL/SOME
ANY/ALL/SOME Predicate Syntax
ANY/ALL/SOME Usage Notes
ANY/ALL/SOME Quantifiers and Literal Syntax
ANY/ALL/SOME Quantifiers and Subquery Syntax
Equivalences Using ANY/ALL/SOME and Comparison Operators
Equivalences Using ANY/ALL/SOME and IN/NOT IN
ANY/ALL/SOME Examples
Example: ANY Quantifier
Example: ALL Quantifier
Example: ANY/ALL/SOME
BETWEEN/NOT BETWEEN
BETWEEN/NOT BETWEEN Predicate Syntax
BETWEEN/NOT BETWEEN Usage Notes
Example: BETWEEN Predicate
EXISTS/NOT EXISTS
EXISTS/NOT EXISTS Predicate Syntax
EXISTS/NOT EXISTS Usage Notes
Relationship between EXISTS/NOT EXISTS and IN/NOT IN
EXISTS Predicate and NOT IN and Nulls
For ALL
NOT EXISTS Clauses and Stored Procedures
NOT EXISTS and Recursive Queries
EXISTS/NOT EXISTS Examples
Example: EXISTS/NOT EXISTS Functions
Example: EXISTS with Correlated Subqueries
Example: NOT EXISTS with Correlated Subqueries
IN/NOT IN
IN/NOT IN Predicate Syntax
IN/NOT IN Usage Notes
IN/NOT IN Result
Relationship between IN/NOT IN and EXISTS/NOT EXISTS
Equivalences Using IN/NOT IN, NOT, and ANY/ALL/SOME
Behavior of Nulls for IN
Behavior of Nulls for NOT IN
NOT IN Clauses and Stored Procedures
NOT IN and Recursive Queries
Queries with Large NOT IN Clauses May Fail
IN/NOT IN Examples
Example: Searching for Atlanta Employees
Example: Searching when DeptNo Has Two Columns
Example: Using IN/NOT IN with a List of Literals
IS NULL/IS NOT NULL
IS NULL/IS NOT NULL Predicate Syntax
IS NULL/IS NOT NULL Examples
Example Using IS NULL Predicate
Example: Using IS NOT NULL
Example: Searching for NULL and NOT-NULL in the Same Statement
Example: Searching a Table with Possible Nulls
LIKE/NOT LIKE
LIKE/NOT LIKE Predicate Syntax
LIKE/NOT LIKE Usage Notes
Optimized Performance Using a NUSI
Null Expressions in LIKE Operations
Case Specification
Wildcard Characters
ESCAPE Feature of LIKE
Pad Characters
ANY/ALL/SOME Quantifiers
Behavior of the ESCAPE Character
LIKE/NOT LIKE Examples
Example: ESCAPE
Example: ANY
Example: Matching Patterns from Another Table
Example: LIKE Predicate
Example: Last Name Spelling
Example: % and _ Characters
Example: Pad Characters and Letter
Examples: KanjiEBCDIC
Examples: KanjiEUC
Examples: KanjiShift-JIS
Examples: Miscellaneous
OVERLAPS
Null-Handling Functions
NVL
NVL Function Syntax
NVL Argument Types and Rules
NVL Result Type
Example: NVL Function
NVL2
NVL2 Function Syntax
NVL2 Argument Types and Rules
NVL2 Result Type
NVL2 Function Examples
Ordered Analytical/Window Aggregate Functions
Ordered Analytical Functions
Benefits
Ordered Analytical Calculations at the SQL Level
Teradata Warehouse Miner
Example: Ordered Analytical Functions
Characteristics of Ordered Analytical Functions
The Function Value
Use of QUALIFY Clause
DISTINCT Clause Restriction
Permitted Query Objects
Where Ordered Analytical Functions are Not Permitted
Use of Standard SQL Features
Unsupported Data Types
Ordered Analytical Functions and Period Data Types
Ordered Analytical Functions and Recursive Queries
Ordered Analytical Functions and Join Indexes
Ordered Analytical Functions and Row Level Security Tables
Computation Sort Order and Result Order
Data in Partitioning Column of Window Specification and Resource Impact
Using Ordered Analytical Functions
Example: Using RANK and AVG
Example: Ordered Analytical Functions Using QUALIFY with RANK
Example: Computation Sort Order and Result Order Using QUALIFY with RANK
Window Feature
PARTITION BY Phrase
ORDER BY Phrase
RESET WHEN Phrase
RESET WHEN Condition Rules
Rules for Using a DEFAULT Function in a RESET WHEN Condition
RESET WHEN Phrase Examples
ROWS Phrase
Multiple Window Specifications
Window Aggregate Functions
Window Specification
Window Specification Syntax
Type of Computation
Arguments to Window Aggregate Functions
Window Aggregate Functions Result Type and Format
Result Title
Window Specification and Missing Data Points
Nesting Aggregates in Window Functions
Alternative: Using Derived Tables
Teradata-Specific Alternatives to Ordered Analytical Functions
Teradata-Specific Functions and ANSI SQL:2011 Window Functions
Comparing Window Aggregate Functions and Teradata-Specific Functions
Example: Group Count
Example: Remaining Count
Example: Cumulative Maximum
Example: Cumulative Minimum
Example: Cumulative Sum
Example: Group Sum
Example: Group Sum
Example: Moving Sum
Example: Remaining Sum
GROUP BY Clause
GROUP BY and Window Functions
GROUP BY and Teradata-Specific Functions
Combining Window Functions, Teradata-Specific Functions, and GROUP BY
Possible Window Specification Result Overflow with SELECT Sum
CSUM
CSUM Function Syntax
CSUM Result Type and Attributes
CSUM Usage Notes
CSUM Function Examples
CUME_DIST
CUME_DIST Function Syntax
CUME_DIST Results
Example: CUME_DIST Function
DENSE_RANK (ANSI)
DENSE_RANK Function Syntax (ANSI)
DENSE_RANK Result Type (ANSI)
Example: DENSE_RANK (ANSI) Function
FIRST_VALUE/LAST_VALUE
FIRST_VALUE/LAST_VALUE Function Syntax
FIRST_VALUE/LAST_VALUE Usage Notes
Example: FIRST_VALUE/LAST_VALUE Function
LAG/LEAD
LAG/LEAD Function Syntax
LAG/LEAD Result Type
LAG/LEAD Usage Notes
LAG/LEAD Function Examples
Example: LAG with IGNORE NULLS
Example: LAG with RESPECT NULLS
Example: LAG with RESPECT NULLS without Explicitly Specifying RESPECT NULLS
Example: LEAD with RESPECT NULLS
Example: LEAD with IGNORE NULLS
Example: LEAD with RESPECT NULLS without Explicitly Specifying RESPECT NULLS
MAVG
MAVG Function Syntax
MAVG Result Type and Attributes
MAVG Usage Notes
MAVG Function Examples
MDIFF
MDIFF Function Syntax
MDIFF Result Type and Attributes
MDIFF Usage Notes
MDIFF Function Examples
MEDIAN
MEDIAN Function Syntax
Example: MEDIAN Function
MLINREG
MLINREG Function Syntax
MLINREG Result Type and Attributes
MLINREG Usage Notes
Example: MLINREG Function
MSUM
MSUM Function Syntax
MSUM Result Type and Attributes
MSUM Usage Notes
PERCENT_RANK
PERCENT_RANK Function Syntax
PERCENT_RANK Result Type and Attributes
PERCENT_RANK Function Examples
Example: Relative Rank
Example: Rank and Relative Rank
Example: PERCENT_RANK and CUM_DIST
PERCENTILE_CONT/PERCENTILE_DISC
PERCENTILE_CONT/PERCENTILE_DISC Function Syntax
PERCENTILE_CONT/PERCENTILE_DISC Usage Notes
Example: PERCENTILE_CONT/PERCENTILE_DISC Function
QUANTILE
QUANTILE Function Syntax
QUANTILE Result Type and Attributes
QUANTILE Usage Notes
QUANTILE Function Examples
RANK (ANSI)
RANK Function Syntax (ANSI)
RANK Result Type and Attributes (ANSI)
RANK Function Examples (ANSI)
Example: Ranking Salespeople Based on Sales
Example: Finding Differences between RANK(ANSI) and DENSE_ RANK(ANSI)
RANK (Teradata)
RANK Function Syntax (Teradata)
RANK Result Type and Attributes
RANK Usage Notes (Teradata)
RANK Function Examples (Teradata)
Example: Displaying Total Sales and Sales Rank in Top 100 Selling Items Using RANK (Teradata)
Example: Sorting Employees by Name and Company Seniority Using RANK (Teradata)
Example: Sorting and Reporting Items by Category and Descending Revenue Rank Using RANK (Teradata)
ROW_NUMBER
ROW_NUMBER Function Syntax
ROW_NUMBER Usage Notes
Example: ROW_NUMBER Function
Regular Expression Functions
REGEXP_SUBSTR
REGEXP_SUBSTR Function Syntax
REGEXP_SUBSTR Argument Types and Rules
REGEXP_SUBSTR Result Type
REGEXP_SUBSTR Function Examples
REGEXP_REPLACE
REGEXP_REPLACE Function Syntax
REGEXP_REPLACE Argument Types and Rules
REGEXP_REPLACE Result Type
REGEXP_REPLACE Usage Notes
REGEXP_REPLACE Function Examples
REGEXP_INSTR
REGEXP_INSTR Function Syntax
REGEXP_INSTR Argument Types and Rules
REGEXP_INSTR Result Type
REGEXP_INSTR Function Examples
REGEXP_SIMILAR
REGEXP_SIMILAR Function Syntax
REGEXP_SIMILAR Argument Types and Rules
REGEXP_SIMILAR Result Type
Example: Returning Matching Customer Names with REGEXP_SIMILAR Function
REGEXP_SPLIT_TO_TABLE
REGEXP_SPLIT_TO_TABLE Function Syntax
REGEXP_SPLIT_TO_TABLE Argument Types and Rules
REGEXP_SPLIT_TO_TABLE Result Type
Example: REGEXP_SPLIT_TO_TABLE Function
String Operators and Functions
String Functions
Data Types on Which String Functions Can Operate
ANSI Equivalence of Teradata SQL String Functions
Additional Functions that Operate on Strings
Effects of Server Character Sets on Character String Functions
Uppercase Character Conversion for LATIN
Logical Characters and Physical Characters
Untranslatable KANJI1 Characters
Implicit Server Character Set Translation
Concatenation Operator
Concatenation Operator Syntax
Concatenation Operator Argument Types and Rules
Concatenation Operator Result Type and Attributes
Concatenation Operator Usage Notes
Concatenation Operator Examples
Example: Using Concatenation to Create More Readable Results
Example: Concatenating First Name with Last Name
Example: Concatenating Last Name with First Name
Example: Concatenating Byte Strings
ASCII
ASCII Function Syntax
ASCII Argument Types and Rules
ASCII Result Type
Example: ASCII Function
CHAR2HEXINT
CHAR2HEXINT Function Syntax
CHAR2HEXINT Result Type and Attributes
CHAR2HEXINT Usage Notes
CHAR2HEXINT Function Examples
Example: Hexadecimal Representation of Japanese Characters
Example: Hexadecimal Representation of Table Names
CHR
CHR Function Syntax
CHR Argument Types and Rules
CHR Result Type
Example: CHR Function
CONCAT
CONCAT Function Syntax
Argument Type and Rules
CONCAT Result Type and Attributes
Example: CONCAT Function
CSV
CSV Function Syntax
CSV Argument Types and Rules
CSV Result Type
CSV Function Examples
CSVLD
CSVLD Function Syntax
CSVLD Argument Types and Rules
CSVLD Result Type
Example: CSVLD Function
EDITDISTANCE
EDITDISTANCE Function Syntax
EDITDISTANCE Argument Types and Rules
EDITDISTANCE Result Type
EDITDISTANCE Usage Notes
EDITDISTANCE Function Examples
INDEX
INDEX Function Syntax
INDEX Argument Types and Rules
INDEX Result Type and Attributes
INDEX Usage Notes
Expected INDEX Values
Rules for Character Type Arguments
Rules for KANJI1 Server Character Set
Relationship between INDEX and POSITION
INDEX Function Examples
Example: Using Simple INDEX Expressions
Example: Using INDEX String Function
Example: Using INDEX with KANJI1 and KanjiEBCDIC
Example: Using INDEX with KANJI1 and KanjiEUC
Example: Using INDEX with KANJI1 and KanjiShift-JIS
Example: Applying INDEX to the SPACE Character
Example: Using "Batch" in the Project Description
Example: Using Concatenation, SUBSTRING, and INDEX
INITCAP
INITCAP Function Syntax
INITCAP Argument Types and Rules
INITCAP Result Type
Example: INITCAP Function
INSTR
INSTR Function Syntax
INSTR Argument Types and Rules
INSTR Result Type
INSTR Usage Notes
INSTR Function Examples
LEFT
LEFT Function Syntax
LEFT Usage Notes
LEFT Result Type
Example: LEFT Function
LENGTH
LENGTH Function Syntax
LENGTH Argument Types and Rules
LENGTH Result Type
Example: LENGTH Function
LOCATE
LOCATE Function Syntax
LOCATE Argument Types and Rules
LOCATE Result Type and Attributes
LOCATE Function Examples
LOWER
LOWER Function Syntax
LOWER Argument Types
LOWER Result Type and Attributes
Example: Using LOWER to Return and Store Values
LPAD
LPAD Function Syntax
LPAD Argument Types and Rules
LPAD Result Type
LPAD Function Examples
LTRIM
LTRIM Function Syntax
LTRIM Argument Types and Rules
LTRIM Result Type
LTRIM Function Examples
NGRAM
NGRAM Function Syntax
NGRAM Argument Types and Rules
NGRAM Result Type
NGRAM Usage Notes
NGRAM Function Examples
NVP
NVP Function Syntax
NVP Argument Types and Rules
NVP Result Type
NVP Function Examples
OREPLACE
OREPLACE Function Syntax
OREPLACE Argument Types and Rules
OREPLACE Result Type
OREPLACE Usage Notes
OREPLACE Function Examples
OTRANSLATE
OTRANSLATE Function Syntax
OTRANSLATE Argument Types and Rules
OTRANSLATE Result Type
OTRANSLATE Usage Notes
OTRANSLATE Function Examples
POSITION
POSITION Function Syntax
POSITION Argument Types and Rules
POSITION Result Type and Attributes
POSITION Usage Notes
Expected POSITION Values
How POSITION and INDEX Differ
REVERSE
REVERSE Function Syntax
REVERSE Usage Notes
REVERSE Result Type
Example: REVERSE Function
RIGHT
RIGHT Function Syntax
RIGHT Usage Notes
RIGHT Result Type
Example: RIGHT Function
RPAD
RPAD Function Syntax
RPAD Argument Types and Rules
RPAD Result Type
RPAD Function Examples
RTRIM
RTRIM Function Syntax
RTRIM Argument Types and Rules
RTRIM Result Type
RTRIM Function Examples
SOUNDEX
SOUNDEX Function Syntax
SOUNDEX Argument Types
SOUNDEX Usage Notes
Code the Soundex
SOUNDEX Function Examples
STRING_CS
STRING_CS Function Syntax
STRING_CS Argument Types
STRING_CS Result Value
STRING_CS Usage Notes
STRING_CS Function Examples
STRTOK
STRTOK Function Syntax
STRTOK Argument Types and Rules
STRTOK Result Type
STRTOK Usage Notes
Example: Using STRTOK
STRTOK_SPLIT_TO_TABLE
STRTOK_SPLIT_TO_TABLE Function Syntax
STRTOK_SPLIT_TO_TABLE Argument Types and Rules
STRTOK_SPLIT_TO_TABLE Result Type
Example: STRTOK_SPLIT_TO_TABLE Function
SUBSTRING
SUBSTRING Function Syntax
SUBSTRING Argument Types and Rules
SUBSTRING Result Type and Attributes
SUBSTRING Usage Notes
SUBSTRING Result Value
Usage Rules for SUBSTRING and SUBSTR
Difference between SUBSTRING and SUBSTR
SUBSTRING Function Examples
Example: Searching for Car Serial IDs
Example: Accessing Serial ID Characters
Example: Limiting Returned Characters
Example: Using a SELECT Statement to Request Substrings
Example: Using the CREATE TABLE cstr Table
Example: Differences between SUBSTR and SUBSTRING
Example: Using the KanjiEUC Client Character Set with the ctable1 Table
Example: Using Examples for the KanjiShift-JIS Client Character Set
Example: Applying the SUBSTRING Function to a CLOB Column
TRANSLATE
TRANSLATE Function Syntax
TRANSLATE Argument Types
TRANSLATE Result Type and Attributes
TRANSLATE Usage Notes
Supported Translations for CLOB Strings
Supported Translations between Character Sets
Source Characters that Generate Errors
Error Characters Assigned by the WITH ERROR Option
Suffixes
Translations between Fullwidth and Halfwidth Character Data
Space Folding
UNICODE Normalization Form Translations
Pad Character Translation
Migration
Implicit Character Data Type Conversion
TRANSLATE Related Information
TRANSLATE_CHK
TRANSLATE_CHK Function Syntax
TRANSLATE_CHK Argument Types
TRANSLATE_CHK Result Type and Attributes
TRANSLATE_CHK Usage Notes
TRANSLATE_CHK Function Examples
TRIM
TRIM Function Syntax
TRIM Argument Types and Rules
TRIM Result Type and Attributes
TRIM Usage Notes
TRIM Function Examples
UPPER/UCASE
UPPER/UCASE Function Syntax
UPPER/UCASE Argument Types
UPPER/UCASE Result Type and Attributes
UPPER/UCASE Usage Notes
UPPER/UCASE Function Examples
Example: Using a Table Definition with CASESPECIFIC Attributes
Example: Using UPPER to Store Values
Example: Converting Single-Byte Characters to Uppercase
VARGRAPHIC
VARGRAPHIC Function Syntax
VARGRAPHIC Argument Types
VARGRAPHIC Result Type and Attributes
VARGRAPHIC Usage Notes
VARGRAPHIC Rules
VARGRAPHIC Function Conversion Tables
VARGRAPHIC Function Examples
Elastic TCore Functions
GetSystemTCore
SetSystemTCore
SQL Data Types
Data Types and Literals
SQL Data Definition
Data Definition in SQL
Data Definition Syntax
Using Data Definition
Data Definition Phrases
Data Type Phrases
Data Type Classification
Core Data Type Attributes
Storage and Constraint Attributes
Data Type Attributes and Views
Data Definition Examples
Example: Data Type Attribute Carried over to a View
Example: Overriding the Data Type Attribute Defined in a Base Table
Example: Data Type Attribute Not Carried over to a View
Data Conversion
Data Conversion Syntax
ANSI SQL-Compliant Data Conversion
COMPRESS and DECOMPRESS Phrases
COMPRESS and DECOMPRESS Syntax
Number of Columns that Can Be Compressed
Multivalue Compression (MVC)
Using MVC with CHARACTER or VARCHAR Data Type
Rules for Specifying constant Values
Compress Values of a CHAR or VARCHAR Column in SHOW TABLE
Achieving Maximum Benefit with COMPRESS
Algorithmic Compression (ALC)
Teradata Compression and Decompression Functions
COMPRESS and DECOMPRESS Restrictions
COMPRESS and DECOMPRESS Examples
Example: Compression Using MVC with a constant Value
Example: Compression Using MVC with a Multivalued List
Example: Compression Using MVC on an INTEGER Column
Example: Compression Using ALC Only
Example: Compression Using MVC and ALC
Example: ALC Using the LZCOMP Function
COMPRESS and DECOMPRESS Related Information
Constraint Attributes
Column and Table Constraints
Uniqueness Constraints
Definition
PRIMARY KEY Constraint
UNIQUE Constraint
CHECK Constraints
CHECK Constraints Usage Notes
Example: CHECK Constraints
Referential Constraints
Data Literals
Hexadecimal Byte Literals
Numeric Literals
Integer Literals
Hexadecimal Integer Literals
Decimal Literals
Floating Point Literals
NUMBER Literals
Date and Time Literals
DateTime Literals
Date Literals
Time Literals
Timestamp Literals
Interval Literals
INTERVAL YEAR Literals
INTERVAL YEAR TO MONTH Literals
INTERVAL MONTH Literals
INTERVAL DAY Literals
INTERVAL DAY TO HOUR Literals
INTERVAL DAY TO MINUTE Literals
INTERVAL DAY TO SECOND Literals
INTERVAL HOUR Literals
INTERVAL HOUR TO MINUTE Literals
INTERVAL HOUR TO SECOND Literals
INTERVAL MINUTE Literals
INTERVAL MINUTE TO SECOND Literals
INTERVAL SECOND Literals
Period Literals
Character String Literals
Character String Syntax
Character Literal Identifiers and KANJI1 Data [Deprecated]
Simple Examples
Example: Converting the Server Character Set of a Character Literal
Example: Using Pass Through Character Literals
Unicode Delimited Character Literals
Hexadecimal Character Literals
Graphic Literals
Numeric Data Types
BYTEINT Data Type
BYTEINT Data Type Syntax
BYTEINT Data Type Usage Notes
Example: BYTEINT Data Type
SMALLINT Data Type
SMALLINT Data Type Syntax
SMALLINT Data Type Usage Notes
Example: SMALLINT Data Type
INTEGER Data Type
INTEGER Data Type Syntax
INTEGER Data Type Usage Notes
Example: INTEGER Data Type
BIGINT Data Type
BIGINT Data Type Syntax
BIGINT Data Type Usage Notes
Example: BIGINT Data Type
DECIMAL/NUMERIC Data Types
DECIMAL/NUMERIC Data Types Syntax
DECIMAL/NUMERIC Data Types Usage Notes
DECIMAL/NUMERIC Data Types Examples
DECIMAL/NUMERIC Data Types Related Information
FLOAT/REAL/DOUBLE PRECISION Data Types
FLOAT/REAL/DOUBLE PRECISION Data Types Syntax
FLOAT/REAL/DOUBLE PRECISION Data Types Usage Notes
Example: FLOAT Data Type
FLOAT/REAL/DOUBLE PRECISION Data Types Related Information
NUMBER Data Type
NUMBER Data Type Syntax
NUMBER Data Type Usage Notes
Example: NUMBER Data Type
NUMBER Data Type Related Information
Operations on Floating-Point Values
Inconsistencies When Used with Common C Library Functions
Comparison and Computation Inaccuracies
Example: Non-Associativity of Floating Point Arithmetic
Example: Comparing Floating Point Values
Rounding
Character and CLOB Data Types
Character Data
CHARACTER Data Type
CHARACTER Data Type Syntax
CHARACTER Data Type Usage Notes
CHARACTER Data Type Example
CHARACTER Data Type Related Information
VARCHAR Data Type
VARCHAR Data Type Syntax
VARCHAR Data Type Usage Notes
VARCHAR Data Type Examples
VARCHAR Data Type Related Information
CLOB Data Type
CLOB Data Type Syntax
CLOB Data Type Usage Notes
CLOB Data Type Examples
Default Case Specificity of Character Columns
CASESPECIFIC Phrase
CASESPECIFIC Phrase Syntax
CASESPECIFIC Phrase Usage Notes
CASESPECIFIC Phrase Examples
UPPERCASE Phrase
UPPERCASE Phrase Syntax
UPPERCASE Phrase Usage Notes
UPPERCASE Phrase Examples
Teradata SQL Character Strings and Client Physical Bytes
CHARACTER SET Phrase
CHARACTER SET Phrase Syntax
CHARACTER SET Phrase Usage Notes
LATIN Server Character Set
UNICODE Server Character Set
GRAPHIC Server Character Set
KANJISJIS Server Character Set
Byte and BLOB Data Types
Data Storage of Byte and BLOB Types
BYTE Data Type
BYTE Data Type Syntax
BYTE Data Type Usage Notes
Example: Using BYTE Data Type
VARBYTE Data Type
VARBYTE Data Type Syntax
VARBYTE Data Type Usage Notes
Example: VARBYTE Data Type
BLOB Data Type
BLOB Data Type Syntax
BLOB Data Type Usage Notes
BLOB Data Type Examples
LOB Functions
EMPTY_BLOB
EMPTY_BLOB Syntax
Example: EMPTY_BLOB Function
EMPTY_CLOB
EMPTY_CLOB Syntax
Example: EMPTY_CLOB Function
DateTime and Interval Data Types
DateTime Fields
Time Zones
Daylight Saving Time
DATE Data Type
DATE Data Type Syntax
DATE Data Type Usage Notes
Examples: DATASET Data Type
TIME Data Type
TIME Data Type Syntax
TIME Data Type Usage Notes
Internal Representation of TIME
External Representation of TIME
TIME Formats
Implicit and Explicit TIME Conversion
TIMESTAMP Data Type
TIMESTAMP Data Type Syntax
TIMESTAMP Data Type Usage Notes
Internal Representation of TIMESTAMP
External Representation of TIMESTAMP
TIMESTAMP Formats
Implicit and Explicit TIMESTAMP Conversion
Examples: TIMESTAMP Data Type
TIME WITH TIME ZONE Data Type
TIME WITH TIME ZONE Data Type Syntax
TIME WITH TIME ZONE Data Type Usage Notes
Internal Representation of TIME WITH TIME ZONE
External Representation of TIME WITH TIME ZONE
TIME WITH TIME ZONE Format
Implicit and Explicit TIME WITH TIME ZONE Conversion
TIMESTAMP WITH TIME ZONE Data Type
TIMESTAMP WITH TIME ZONE Data Type Syntax
TIMESTAMP WITH TIME ZONE Data Type Usage Notes
Internal Representation of TIMESTAMP WITH TIME ZONE
External Representation of TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE Format
Implicit and Explicit TIMESTAMP WITH TIME ZONE Conversion
INTERVAL YEAR Data Type
INTERVAL YEAR Data Type Syntax
INTERVAL YEAR Data Type Usage Notes
Internal Representation of INTERVAL YEAR
External Representation of INTERVAL YEAR
INTERVAL YEAR Range of Values
Implicit and Explicit INTERVAL YEAR Conversion
INTERVAL YEAR TO MONTH Data Type
INTERVAL YEAR TO MONTH Data Type Syntax
INTERVAL YEAR TO MONTH Data Type Usage Notes
Internal Representation of INTERVAL YEAR TO MONTH
External Representation of INTERNAL YEAR TO MONTH
INTERNAL YEAR TO MONTH Range of Values
Implicit and Explicit INTERVAL YEAR TO MONTH Conversion
Example: Creating a Table with INTERVAL YEAR TO MONTH Column
INTERVAL MONTH Data Type
INTERVAL MONTH Data Type Syntax
INTERVAL MONTH Data Type Usage Notes
Internal Representation of INTERVAL MONTH
External Representation of INTERVAL MONTH
INTERVAL MONTH Range of Values
Implicit and Explicit INTERVAL MONTH Conversion
Example: Creating a Table with INTERVAL MONTH Data Type
INTERVAL DAY Data Type
INTERVAL DAY Data Type Syntax
INTERVAL DAY Data Type Usage Notes
Internal Representation of INTERVAL DAY
External Representation of INTERVAL DAY
INTERVAL DAY Range of Values
Implicit and Explicit INTERVAL DAY Conversion
Example: Creating a Table with INTERVAL DAY Column
INTERVAL DAY TO HOUR Data Type
INTERVAL DAY TO HOUR Data Type Syntax
INTERVAL DAY TO HOUR Data Type Usage Notes
Internal Representation of INTERVAL DAY TO HOUR
External Representation of INTERVAL DAY TO HOUR
INTERVAL DAY TO HOUR Range of Values
Implicit and Explicit INTERVAL DAY TO HOUR Conversion
Example: Creating a Table with INTERVAL DAY TO HOUR Data Type
INTERVAL DAY TO MINUTE Data Type
INTERVAL DAY TO MINUTE Data Type Syntax
INTERVAL DAY TO MINUTE Data Type Usage Notes
Internal Representation of INTERVAL DAY TO MINUTE
External Representation of INTERVAL DAY TO MINUTE
INTERVAL DAY TO MINUTE Range of Values
Implicit and Explicit INTERVAL DAY TO MINUTE Conversion
Example: Creating a Table with INTERVAL DAY TO MINUTE Data Type
INTERVAL DAY TO SECOND Data Type
INTERVAL DAY TO SECOND Data Type Syntax
INTERVAL DAY TO SECOND Data Type Usage Notes
Internal Representation of INTERVAL DAY TO SECOND
External Representation of INTERVAL DAY TO SECOND
INTERVAL DAY TO SECOND Range of Values
Implicit and Explicit INTERVAL DAY TO SECOND Conversion
Example: Creating a Table with INTERVAL DAY TO SECOND Data Type
INTERVAL HOUR Data Type
INTERVAL HOUR Data Type Syntax
INTERVAL HOUR Data Type Usage Notes
Internal Representation of INTERVAL HOUR
External Representation of INTERVAL HOUR
INTERVAL HOUR Range of Values
Implicit and Explicit INTERVAL HOUR Conversion
INTERVAL HOUR TO MINUTE Data Type
INTERVAL HOUR TO MINUTE Data Type Syntax
INTERVAL HOUR TO MINUTE Data Type Usage Notes
Internal Representation of INTERVAL HOUR TO MINUTE
External Representation of INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO MINUTE Range of Values
Implicit and Explicit INTERVAL HOUR TO MINUTE Conversion
Example: Creating a Table with INTERVAL HOUR TO MINUTE Data Type
INTERVAL HOUR TO SECOND Data Type
INTERVAL HOUR TO SECOND Data Type Syntax
INTERVAL HOUR TO SECOND Data Type Usage Notes
Internal Representation of INTERVAL HOUR TO SECOND
External Representation of INTERVAL HOUR TO SECOND
INTERVAL HOUR TO SECOND Range of Values
Implicit and Explicit INTERVAL HOUR TO SECOND Conversion
INTERVAL MINUTE Data Type
INTERVAL MINUTE Data Type Syntax
INTERVAL MINUTE Data Type Usage Notes
Internal Representation of INTERVAL MINUTE
External Representation of INTERVAL MINUTE
INTERVAL MINUTE Range of Values
Implicit and Explicit INTERVAL MINUTE Conversion
Example: Creating a Table with INTERVAL MINUTE Data Type
INTERVAL MINUTE TO SECOND Data Type
INTERVAL MINUTE TO SECOND Data Type Syntax
INTERVAL MINUTE TO SECOND Data Type Usage Notes
Internal Representation of INTERVAL MINUTE TO SECOND
External Representation of INTERVAL MINUTE TO SECOND
INTERVAL MINUTE TO SECOND Range of Values
Implicit and Explicit INTERVAL MINUTE TO SECOND Conversion
Example: Creating a Table with INTERVAL MINUTE TO SECOND Data Type
INTERVAL SECOND Data Type
INTERVAL SECOND Data Type Syntax
INTERVAL SECOND Data Type Usage Notes
Internal Representation of INTERVAL SECOND
External Representation of INTERVAL SECOND
INTERVAL SECOND Range of Values
Implicit and Explicit INTERVAL SECOND Conversion
Example: Creating a Table with INTERVAL SECOND Data Type
Period Data Types
Period Data Types: Basic Definitions
Period Type Input and Output Parameters
Period Data Types Related Information
PERIOD(DATE) Data Type
PERIOD(DATE) Data Type Syntax
PERIOD(DATE) Data Type Usage Notes
Examples: PERIOD(DATE) Data Type
PERIOD(TIME) Data Type
PERIOD(TIME) Data Type Syntax
PERIOD(TIME) Data Type Usage Notes
Example: PERIOD(TIME) Data Type
PERIOD(TIME WITH TIME ZONE) Data Type
PERIOD(TIME WITH TIME ZONE) Data Type Syntax
Period Data Types Usage Notes
Example: PERIOD(TIME WITH TIME ZONE) Data Type
PERIOD(TIMESTAMP) Data Type
PERIOD(TIMESTAMP) Data Type Syntax
PERIOD(TIMESTAMP) Data Type Usage Notes
Example: PERIOD(TIMESTAMP) Data Type
PERIOD(TIMESTAMP WITH TIME ZONE) Data Type
PERIOD(TIMESTAMP WITH TIME ZONE) Data Type Syntax
PERIOD(TIMESTAMP WITH TIME ZONE) Data Type Usage Notes
Example: PERIOD(TIMESTAMP WITH TIME ZONE) Data Type
Data Type Formats and Format Phrases
Data Type Default Formats
Default Formats
DATE Formats
Changing or Overriding the DATE Format Settings
The DateForm Setting
System Default DATE Format
Using the FORMAT Phrase to Set DATE Formats
Valid DATE Formats
Hierarchy of Date Formats
TIME and TIMESTAMP Formats
Changing or Overriding the TIME and TIMESTAMP Format Settings
System Default TIME and TIMESTAMP Format
Using the FORMAT Phrase to Set TIME or TIMESTAMP Formats
TIME and TIMESTAMP Format Components
Hierarchy of TIME and TIMESTAMP Formats
Example: TIME Output Format in Field Mode
Example: TIMESTAMP Output Format in Field Mode
FORMAT
FORMAT Syntax
FORMAT Usage Notes
Relationship to SDF
Example Display Formats
FORMAT Examples
Example: No FORMAT Clause
Example: FORMAT Clause
Example: Using FORMAT to Change the Format of Returned Data
Example: Using FORMAT to Override Default Format
Example: Using FORMAT as Part of Derived Expression
FORMAT Related Information
FORMAT Phrase and Character Formats
Default Result for Character Data
Formatting Characters
How FORMAT Applies to Server Character Sets
Truncation of KANJI1 Character Set Fields [Deprecated]
FORMAT Phrase and Character Format Examples
Example: FORMAT Phrase and Character Formats
Example: Formatting Characters
FORMAT Phrase and NUMERIC Formats
Formatting Characters for Non-Monetary Numeric Information
Formatting Characters for Monetary Numeric Information
Results of a FORMAT Phrase Defining Too Few Positions
Examples: Rounding
Examples: Display Results
FORMAT Phrase and DateTime Formats
Formatting Characters for Date Information
Formatting Characters for Time Information
Kanji Date and Time Markers
Examples: Date Formats
Examples: Time Formats
Examples: Timestamp Formats
Format Consistency
DATE Comparisons
Examples: Querying Using Date Formats
Examples: Using Time Formats
Record Mode Import Anomaly
FORMAT Phrase, DateTime Formats, and Japanese Character Sets
Separators
Using FORMAT for Input
Rules Applied for the First Year of an Era
Naming Columns and Expressions
Naming Columns
Rules for Columns Associated with an Explicit Table Name
Rules for Columns Not Associated with an Explicit Table Name
Qualifying Column Names and Named Objects
AS Phrase
AS Syntax
AS Usage Notes
AS Examples
Example: Using AS to Assign a Temporary Name to an Expression
Example: Creating a Temporary Named Column
NAMED Phrase
NAMED Syntax
NAMED Usage Notes
Differences between NAMED and AS
Example: Using a NAMED Phrase
TITLE Phrase
TITLE Syntax
TITLE Usage Notes
Determining the Title for a Column or Expression
TITLE Phrase Rules
TITLE Examples
Example: Using the TITLE Phrase
Example: Using the TITLE Phrase in a SELECT Statement
Data Type Conversions
Data Type Conversions with CAST Function
Forms of Data Type Conversions
Implicit Type Conversions
Implicit Type Conversion Examples
Example: Implicit Type Conversion during Assignment
Example: Implicit Type Conversion during Comparison
Example: Implicit Type Conversion in Parameter Passing Operations
Implicitly Convertible Data Types
Implicit Conversion of DateTime Types
Implicit Conversion Rules
Truncation during Implicit Conversion
Implicit Type Conversion Restrictions
Implicit Type Conversion Related Information
CAST in Explicit Data Type Conversions
CAST in Explicit Data Type Conversions Syntax
CAST in Explicit Data Type Conversions Usage Notes
Character Truncation Rules
Server Character Set Rules
Numeric Overflow, Field Mode, and CAST
CAST in Explicit Data Type Conversions Examples
Data Conversions in Field Mode
Field Mode: User Response Data
Conversions to Numeric Types
Byte-to-Byte Conversion
Byte-to-Byte Conversion with CAST
Teradata Byte-to-Byte Conversion Syntax
Byte-to-Byte Conversion Usage Notes
Conversions Where Source and Target Types Differ in Length
Supported Source and Target Data Types
Using HASHBUCKET to Convert a BYTE Type to an INTEGER Type
Byte-to-Byte Conversion Examples
Example: Explicit Conversion of BLOB to VARBYTE
Example: Implicit Conversion of VARBYTE to BLOB
Character-to-Character Conversion
Character-to-Character Conversion with CAST
Teradata Character-to-Character Conversion
Character-to-Character Conversion Usage Notes
General Usage Notes
CAST Syntax Usage Notes
Teradata Conversion Syntax Usage Notes
Implicit Character-to-Character Conversion
Implicit Character-to-Character Translation
Character Literals
Implicit KANJISJIS Character-to-Character Translation
SQL Rules for Implicit Translation for Expression and Function Arguments
Character-to-DATE Conversion
Character-to-DATE Conversion with CAST
Teradata Character-to-DATE Conversion Syntax
Character-to-DATE Conversion Usage Notes
Character String Format
Forcing a FORMAT on CAST for Converting Character to DATE
Character Strings that Omit Day, Month, or Year
Implicit Character-to-DATE Conversion
Character-to-DATE Conversion Examples
Example: IntegerDate Dateform Mode
Example: ANSIDate Dateform Mode
Example: Implicit Character-to-DATE Conversion
Character-to-INTERVAL Conversion
Character-to-INTERVAL Conversion with CAST
Teradata Character-to-INTERVAL Conversion Syntax
Character-to-INTERVAL Conversion Usage Notes
Character-to-INTERVAL Conversion Examples
Character-to-INTERVAL Conversion Example: Querying with CAST
Example: Converting to an INTERVAL Value
Character-to-Numeric Conversion
Character-to-Numeric Conversion with CAST
Teradata Character-to-Numeric Conversion Syntax
Character-to-Numeric Conversion Usage Notes
Character-to-Numeric Conversion Supported Character Types
Numeric Overflow
FORMAT Phrase Controls Parsing of the Data
Implicit Character-to-Numeric Conversion
Example: Implicit Conversion of Character to Numeric
Character-to-Period Conversion
Character-to-Period Conversion Syntax
Character-to-Period Conversion Usage Notes
Character Strings that Use ANSI DateTime Format
Implicit Character-to-Period Conversion
Example: Casting Concatenated Character Literals
Character-to-TIME Conversion
Character-to-TIME Conversion with CAST
Teradata Character-to-TIME Conversion Syntax
Character-to-TIME Conversion Usage Notes
Character-to-TIME Conversions with Time Zone
Character-to-TIME Conversions with Fractional Seconds
Character Strings that Omit Hour, Minute, or Second
FORMAT Phrase Restrictions
Implicit Character-to-TIME Conversion
Character-to-TIME Conversion Examples
Example: Fractional Seconds
Example: Truncation of Non-Pad Character Data
Example: Invalid MINUTE Value
Example: FORMAT Phrase
Example: Implicit Conversion of Character to TIME
Character-to-TIMESTAMP Conversion
Character-to-TIMESTAMP Conversion with CAST
Teradata Character-to-TIMESTAMP Conversion Syntax
Character-to-TIMESTAMP Conversion Usage Notes
Character-to-TIMESTAMP Conversions with Time Zone
Character-to-TIMESTAMP Conversions with Fractional Seconds
Character Strings that Omit Day, Month, Year, Hour, Minute, or Second
Restrictions on FORMAT Phrase
Implicit Character-to-TIMESTAMP Conversion
Character-to-TIMESTAMP Example: Querying with CAST
Character Data Type Assignment Rules
DATE-to-Character Conversion
DATE-to-Character Conversion with CAST
Teradata DATE-to-Character Conversion Syntax
DATE-to-Character Conversion Usage Notes
DATE-to-Character Conversion Restrictions
Forcing a FORMAT on CAST for Converting DATE to Character
DATE-to-Character Conversion Examples
Example: Converting a DATE Value to a Character String
Example: Creating a Script to Convert Date Values
DATE-to-DATE Conversion
DATE-to-DATE Conversion with CAST
Teradata DATE-to-DATE Conversion Syntax
Example: Finding Employee Birthdates
DATE-to-Numeric Conversion
DATE-to-Numeric Conversion with CAST
Teradata DATE-to-Numeric Conversion Syntax
DATE-to-Numeric Conversion Usage Notes
FORMAT Phrase
Implicit DATE-to-Numeric Conversion
Example: DATE-to-Numeric Conversion
DATE-to-Period Conversion
DATE-to-Period Conversion Syntax
DATE-to-Period Conversion Usage Notes
DATE-to-Period Conversion Examples
Example: Casting a DATE literal as PERIOD(DATE)
Example: Casting a DATE literal as PERIOD(TIMESTAMP(4))
DATE-to-TIMESTAMP Conversion
DATE-to-TIMESTAMP Conversion with CAST
Teradata DATE-to-TIMESTAMP Conversion Syntax
DATE-to-TIMESTAMP Conversion Usage Notes
Implicit DATE-to-TIMESTAMP Conversion
DATE-to-TIMESTAMP Conversion Examples
Example: Converting a DATE Value to a TIMESTAMP Value
Example: Converting a DATE Value to a TIMESTAMP WITH TIME ZONE value
Example: CAST for a TIMESTAMP
Example: Converting the DATE Value to a TIMESTAMP Value Based on a Time Zone String
INTERVAL-to-Character Conversion
INTERVAL-to-Character Conversion with CAST
Teradata INTERVAL-to-Character Conversion Syntax
INTERVAL-to-Character Conversion Usage Notes
INTERVAL-to-Fixed CHARACTER Conversion
INTERVAL-to-VARCHAR Conversion
INTERVAL-to-INTERVAL Conversion
INTERVAL-to-INTERVAL Conversion with CAST
Teradata INTERVAL-to-INTERVAL Conversion Syntax
INTERVAL-to-INTERVAL Conversion Usage Notes
INTERVAL-to-INTERVAL Conversion Compatible Types
Precision of Source and Target Types
Implicit INTERVAL-to-INTERVAL Conversion
INTERVAL-to-INTERVAL Conversion Examples
Example: Least Significant Field in INTERVAL YEAR(2) Source Lower than Target
Example: Least Significant Field in DAY(3) TO SECOND(2) Source Lower than Target
Example: Least Significant Field in INTERVAL YEAR Source Higher than Target
Example: Least Significant Field in DAY Source Higher than Target
Example: Most Significant Field in Source Higher than Target
Example: Implicit Type Conversion during Assignment
INTERVAL-to-Numeric Conversion
INTERVAL-to-Numeric Conversion with CAST
Teradata INTERVAL-to-Numeric Conversion Syntax
Implicit INTERVAL-to-Numeric Conversion
Example: Using CAST to Convert INTERVAL MONTH Values
Numeric-to-Character Conversion
Numeric-to-Character Conversion with CAST
Teradata Numeric-to-Character Conversion Syntax
Numeric-to-Character Conversion Usage Notes
How CAST Differs from Teradata Conversion Syntax
Numeric-to-Character Conversion Supported Character Types
Implicit Numeric-to-Character Conversion
Numeric-to-Character Conversion Examples
Example: Converting an INTEGER Data Type to a Character
Example: Converting Salaries
Example: Converting Employee Numbers
Numeric-to-DATE Conversion
Numeric-to-DATE Conversion with CAST
Teradata Numeric-to-DATE Conversion Syntax
Numeric-to-DATE Conversion Usage Notes
Translation of Numbers to Dates
Range of Allowable Values
Numeric-to-DATE Implicit Type Conversion
Example: Converting a Numeric Integer Expression to a Date Format
Numeric-to-INTERVAL Conversion
Numeric-to-INTERVAL Conversion with CAST
Teradata Numeric-to-INTERVAL Conversion Syntax
Numeric-to-INTERVAL Conversion Usage Notes
Example: Converting Numeric Data to an INTERVAL Value
Numeric-to-Numeric Conversion
Numeric-to-Numeric Conversion with CAST
Teradata Numeric-to-Numeric Conversion Syntax
Numeric-to-Numeric Conversion Usage Notes
Conversion to FLOAT/REAL/DOUBLE PRECISION
Truncation and Rounding during Conversion
Using CAST in Applications with DECIMAL Type Size Restrictions
Using CAST to Avoid Numeric Overflow
Implicit Numeric-to-Numeric Conversion
Numeric-to-Numeric Conversion Examples
Example: Casting a Numeric Integer Expression
Example: Changing the FORMAT Phrase to Display a Numeric Value
Period-to-Character Conversion
Period-to-Character Conversion with CAST
Teradata Period-to-Character Conversion Syntax
Period-to-Character Conversion Usage Notes
Example: Converting a PERIOD Data Type to Its Canonical Character String Representation
Period-to-DATE Conversion
Period-to-DATE Conversion Syntax
Period-to-DATE Conversion Usage Notes
Example: Converting Period Data to a DATE Value
Period-to-Period Conversion
Period-to-Period Conversion Syntax
Period-to-Period Conversion Usage Notes
Period-to-Period Conversion Compatible Types
PERIOD(DATE) to PERIOD(TIMESTAMP)
PERIOD(TIME) to PERIOD(TIME)
PERIOD(TIME) to PERIOD(TIMESTAMP)
PERIOD(TIMESTAMP) to PERIOD(DATE)
PERIOD(TIMESTAMP) to PERIOD(TIME)
PERIOD(TIMESTAMP) to PERIOD(TIMESTAMP)
Period-to-Period Conversion Examples
Example: PERIOD(DATE) to PERIOD(TIMESTAMP)
Period-to-Period Conversion Example: Least Significant Field in Source Lower than Target
Period-to-TIME Conversion
Period-to-TIME Conversion Syntax
Period-to-TIME Conversion Usage Notes
Period-to-TIME Conversion Example
Period-to-TIMESTAMP Conversion
Period-to-TIMESTAMP Conversion Syntax
Period-to-TIMESTAMP Conversion Usage Notes
Example: Period-to-TIMESTAMP Conversion
Signed Zone DECIMAL Conversion
Using FORMAT in CREATE TABLE
Using Another FORMAT in the SELECT Statement
If FORMAT is Not Attached to the Column
Signed Zone DECIMAL Conversion Examples
Signed Zone DECIMAL Conversion Example 1
Signed Zone DECIMAL Conversion Example 2
Signed Zone DECIMAL Conversion Example 3
Signed Zone DECIMAL Conversion Example 4
Signed Zone DECIMAL Conversion Example 5
TIME-to-Character Conversion
TIME-to-Character Conversion with CAST
Teradata TIME-to-Character Conversion Syntax
TIME-to-Character Conversion Usage Notes
Forcing a FORMAT on CAST for Converting TIME to Character
Example: TIME-to-Character Conversion
TIME-to-Period Conversion
TIME-to-Period Conversion Syntax
TIME-to-Period Conversion Usage Notes
Example: TIME-to-Period Conversion
TIME-to-TIME Conversion
TIME-to-TIME Conversion with CAST
Teradata TIME-to-TIME Conversion Syntax
TIME-to-TIME Conversion Usage Notes
TIME-to-TIME Conversion Examples
TIME-to-TIME Conversion Example 1
TIME-to-TIME Conversion Example 2
TIME-to-TIME Conversion Example 3
TIME-to-TIME Conversion Example 4
TIME-to-TIME Conversion Example 5
TIME-to-TIME Conversion Example 6
TIME-to-TIME Conversion Example 7
TIME-to-TIMESTAMP Conversion
TIME-to-TIMESTAMP Conversion with CAST
Teradata TIME-to-TIMESTAMP Conversion Syntax
TIME-to-TIMESTAMP Conversion Usage Notes
Implicit TIME-to-TIMESTAMP Conversion
TIME-to-TIMESTAMP Conversion Examples
TIME-to-TIMESTAMP Conversion Example 1
TIME-to-TIMESTAMP Conversion Example 2
TIME-to-TIMESTAMP Conversion Example 3
TIME-to-TIMESTAMP Conversion Example 4
TIME-to-TIMESTAMP Conversion Example 5
TIME-to-TIMESTAMP Conversion Example 6
TIME-to-TIMESTAMP Conversion Example 7
TIME-to-TIMESTAMP Conversion Example 8
TIME-to-TIMESTAMP Conversion Example 9
TIME-to-TIMESTAMP Conversion Example 10
TIME-to-TIMESTAMP Conversion Example 11
TIME-to-TIMESTAMP Conversion Example 12
TIME-to-TIMESTAMP Conversion Example 13
TIME-to-TIMESTAMP Conversion Example 14
TIMESTAMP-to-Character Conversion
TIMESTAMP-to-Character Conversion with CAST
Teradata TIMESTAMP-to-Character Conversion Syntax
TIMESTAMP-to-Character Conversion Usage Notes
Forcing a FORMAT on CAST for Converting TIMESTAMP to Character
Example: TIMESTAMP-to-Character Conversion
TIMESTAMP-to-DATE Conversion
TIMESTAMP-to-DATE Conversion with CAST
Teradata TIMESTAMP-to-DATE Conversion Syntax
TIMESTAMP-to-DATE Conversion Usage Notes
Implicit TIMESTAMP-to-DATE Conversion
TIMESTAMP-to-DATE Conversion Examples
TIMESTAMP-to-DATE Conversion Example 1
TIMESTAMP-to-DATE Conversion Example 2
TIMESTAMP-to-DATE Conversion Example 3
TIMESTAMP-to-DATE Conversion Example 4
TIMESTAMP-to-DATE Conversion Example 5
TIMESTAMP-to-DATE Conversion Example 6
TIMESTAMP-to-DATE Conversion Example 7
TIMESTAMP-to-Period Conversion
TIMESTAMP-to-Period Conversion Syntax
TIMESTAMP-to-Period Conversion Usage Notes
Example: TIMESTAMP-to-Period Conversion
TIMESTAMP-to-TIME Conversion
TIMESTAMP-to-TIME Conversion with CAST
Teradata TIMESTAMP-to-TIME Conversion Syntax
TIMESTAMP-to-TIME Conversion Usage Notes
Implicit TIMESTAMP-to-TIME Conversion
TIMESTAMP-to-TIME Conversion Examples
TIMESTAMP-to-TIME Conversion Example 1
TIMESTAMP-to-TIME Conversion Example 2
TIMESTAMP-to-TIME Conversion Example 3
TIMESTAMP-to-TIME Conversion Example 4
TIMESTAMP-to-TIME Conversion Example 5
TIMESTAMP-to-TIME Conversion Example 6
TIMESTAMP-to-TIME Conversion Example 7
TIMESTAMP-to-TIMESTAMP Conversion
TIMESTAMP-to-TIMESTAMP Conversion with CAST
Teradata TIMESTAMP-to-TIMESTAMP Conversion Syntax
TIMESTAMP-to-TIMESTAMP Conversion Usage Notes
TIMESTAMP-to-TIMESTAMP Conversion Examples
TIMESTAMP-to-TIMESTAMP Conversion Example 1
TIMESTAMP-to-TIMESTAMP Conversion Example 2
TIMESTAMP-to-TIMESTAMP Conversion Example 3
TIMESTAMP-to-TIMESTAMP Conversion Example 4
TIMESTAMP-to-TIMESTAMP Conversion Example 5
TIMESTAMP-to-TIMESTAMP Conversion Example 6
TIMESTAMP-to-TIMESTAMP Conversion Example 7
TIMESTAMP-to-TIMESTAMP Conversion Example 8
TRYCAST
TRYCAST Syntax
TRYCAST Examples
Example: TRYCAST Conversion
Example: TRYCAST Conversion Failure
Data Type Conversion Functions
TO_BYTES
TO_BYTES Syntax
TO_BYTES Argument Types and Rules
TO_BYTES Examples
Example: Decoding a Sequence of Characters to Base16
Example: Decoding a Sequence of Characters to Base36
FROM_BYTES
FROM_BYTES Syntax
FROM_BYTES Argument Types and Rules
FROM_BYTES Examples
TO_NUMBER
TO_NUMBER Syntax
TO_NUMBER format_arg Format Elements
TO_NUMBER Argument Types and Rules
TO_NUMBER Usage Notes
TO_NUMBER Examples
Example: Converting string_expr to a NUMBER Data Type
Example: Converting a Dollar Amount to a NUMBER Data Type
TO_CHAR(Numeric)
TO_CHAR(Numeric) Syntax
TO_CHAR(Numeric) Argument Types and Rules
Example: Converting a numeric_expr to a Character String
TO_CHAR(DateTime)
TO_CHAR(DateTime) Syntax
TO_CHAR(DateTime) Argument Types and Rules
TO_CHAR(DateTime) Examples
Example: Converting a date_timestamp_value to a Character String
Example: Truncating Digits in Fractional Seconds
TO_DATE
TO_DATE Syntax
TO_DATE format_arg Format Elements
TO_DATE Argument Types and Rules
Example: Converting a string_expr to a DATE Data Type
TO_TIMESTAMP
TO_TIMESTAMP Syntax
TO_TIMESTAMP Argument Types and Rules
TO_TIMESTAMP Examples
Example: Converting string_expr or integer_expr to a TIMESTAMP Data Type
Example: Converting a POSIX Epoch
TO_TIMESTAMP_TZ
TO_TIMESTAMP_TZ Syntax
TO_TIMESTAMP_TZ Argument Types and Rules
Example: Converting string_expr to a TIMESTAMP WITH TIME ZONE Data Type
TO_YMINTERVAL
TO_YMINTERVAL Syntax
TO_YMINTERVAL string_value Formats
TO_YMINTERVAL Argument Types and Rules
TO_YMINTERVAL Examples
TO_DSINTERVAL
TO_DSINTERVAL Syntax
TO_DSINTERVAL string_value Formats
TO_DSINTERVAL Argument Types and Rules
TO_DSINTERVAL Examples
NUMTODSINTERVAL
NUMTODSINTERVAL Syntax
NUMTODSINTERVAL Argument Types and Rules
NUMTODSINTERVAL Examples
NUMTOYMINTERVAL
NUMTOYMINTERVAL Syntax
NUMTOYMINTERVAL Argument Types and Rules
NUMTOYMINTERVAL Examples
Example: Converting numeric_value into an INTERVAL YEAR(40) TO MONTH Value
Example: Converting numeric_value into an INTERVAL YEAR(100) TO MONTH Value
Default Value Control Phrases
NOT NULL Phrase
DEFAULT Phrase
DEFAULT Phrase Syntax
DEFAULT Phrase Usage Notes
DEFAULT Phrase Examples
WITH DEFAULT Phrase
WITH DEFAULT Phrase Syntax
WITH DEFAULT Phrase Usage Notes
WITH DEFAULT Phrase Examples
DATASET Data Type
Teradata Support for the DATASET Data Type
Standards Compliance
DATASET Data Type Specifications
Maximum and In-Line Length of a DATASET Instance
Storage Formats
Variable Schema Formats
Privileges Required for Creating and Using Schemas
DATASET Data Type Syntax
Data Definition Language Statements
Character Set Handling
DATASET Type CreateDATASET Function
DATASET Type Transform
DATASET Type Cast
DATASET Type Ordering
DATASET Type Usage
FNC Library Routines that Support the DATASET Type
Restrictions for the DATASET Type
Operations on the DATASET Data Type
Creating and Altering Tables to Store DATASET Data
Column-Level Schemas
Instance-Level Schemas
Disabling DATASET Validation
Example: Creating a Table with the DATASET Data Type
Using Algorithmic Compression on DATASET Columns
Accessing DATASET Data Using Dot Notation
WHERE Clause Enhancements
Examples: Using Dot Notation with the DATASET Data Type
Example: Using the WHERE Clause
Modifying DATASET Columns
Modifying DATASET Columns Examples
Example: Successful INSERT-SELECT Statements
Example: Unsuccessful INSERT-SELECT Statements
DATASET Methods, Functions, and Table Operators
DATASET Methods
AvroProject
AvroProject Syntax
AvroProject Usage Notes
Example: Reading Specified Portions of an Avro Instance
AvroProjectToJSON
AvroProjectToJSON Syntax
AvroProjectToJSON Usage Notes
Example: Returning a JSON-Encoded Avro Value Instead of an Avro Instance as the Result
DATASET Constructor
DATASET Constructor Syntax
DATASET Constructor Examples
ExtractValue
ExtractValue Syntax
ExtractValue Usage Notes
ExtractValue Examples
getRawData
getRawData Syntax
getRawData Usage Notes
getRawData Examples
getRawDataLob
getRawDataLob Syntax
getRawDataLob Usage Notes
getRawDataLob Examples
getRawDataSize
getRawDataSize Syntax
getRawDataSize Examples
getSchema
getSchema Syntax
getSchema Examples
getSchemaSize
getSchemaSize Syntax
getSchemaSize Examples
Header
Header Syntax
Example: Retrieving the Header Row
numRecords
numRecords Syntax
Example: numRecords
toAvro
toAvro Syntax
Example: Converting a DATASET Data Type Instance into an Avro Instance
toJSON
toJSON Syntax
toJSON Examples
Validate
Validate Syntax
Validate Examples
DATASET Functions and Operators
AVRO_CHECK
AVRO_CHECK Syntax
AVRO_CHECK Usage Notes
AVRO_CHECK Examples
Example: Storing Data in a Table
Example: Passing Valid Data
Example: Passing Invalid Data
AvroContainerSplit
AvroContainerSplit Syntax
Example: Using AvroContainerSplit
CreateDATASET
CreateDATASET Syntax
CreateDATASET Examples
Example: Creating DATASET Data Type Instances
Example: Using Column Based Schema that Loads Rows of Data
CSVSplit
CSVSplit Syntax
CSVSplit Usage Notes
CSVSplit Examples
DATASET_KEYS
DATASET_KEYS Syntax
DATASET_KEYS Usage Notes
Example: Using DATASET_KEYS
DATASET_TABLE
DATASET_TABLE Syntax
DATASET_TABLE Usage Notes
DATASET_TABLE Examples
Example: Using the Built-In Cast from Varbyte to DATASET to Perform the Insert
Example: Using Extra Columns
Example: Shredding a DATASET Values Table with the Storage Format CSV
Examples: Shredding the CSV Data
DataSize
DataSize Syntax
DataSize Examples
SchemaEqual
SchemaEqual Syntax
Example: Comparing Data Types
SchemaMatch
SchemaMatch Syntax
Examples: Matching Schemas
CSV Conversion
CSV Schema
CSV_TO_AVRO
CSV_TO_AVRO Syntax
CSV_TO_AVRO Usage Notes
Specifying CSV_TO_AVRO Input Values
CSV_TO_AVRO Rules and Restrictions
Converting CSV to Avro
CSV_TO_AVRO Examples
Example: Using the SCHEMA Custom Clause to Specify Non-Standard Data
Example: Changing the Key Names in the Avro Record
Example: Using CSV Data in Double Quotation Marks in CSV_TO_AVRO
Example: Aggregating CSV_TO_AVRO Output
Example: Converting a DATASET STORAGE FORMAT CSV Value to AVRO
CSV_TO_JSON
CSV_TO_JSON Syntax
CSV_TO_JSON Usage Notes
Specifying CSV_TO_JSON Input Values
CSV_TO_JSON Rules and Restrictions
CSV_TO_JSON Examples
Example: Converting CSV to JSON
Example: Changing the Key Names in the JSON Documents
Example: Using CSV Data in Double Quotation Marks in CSV_TO_JSON
Example: Aggregating CSV_TO_JSON Output
Example: Converting a DATASET STORAGE FORMAT CSV Value to JSON
DATASET Publishing
DATASET_PUBLISH Table Operator
DATASET_PUBLISH Usage Notes
Using Custom Clauses with DATASET_PUBLISH
Publishing NUMBER or DECIMAL Types to an Avro Value
DATASET_PUBLISH Examples
Example: Composing a Table to a DATASET Data Type
Example: Specifying Schemas
Example: Publishing Table Columns
Example: Returning Results that are Not Aggregated
Example: Using the UNIQUE_NAMES Clause
Example: Aggregating Multiple Values from Multiple Rows into One Instance
Example: Aggregating Local Results
Avro Object Container Files
Importing and Exporting Avro Object Container Files
Importing from an Avro Object Container File
Example: Loading Avro Object Container Files as BLOBs Using BTEQ
Exporting to an Avro Object Container File
Scenario 1: Data Stored as Avro with Column-Level Schema
Scenario 2: Data Stored as Avro without Column-Level Schema
Scenario 3: Publishing Data to the Avro Format
External Representations for the DATASET Type
Data Type Encoding
SQL Capabilities Parcel
Database Limits (ConfigResponse) Parcel
StatementInfo Parcel
Example: Metadata Parcel Sequence
JSON Data Type
Teradata Vantage Support for JSON
Standards Compliance
JSON Data Type Syntax
Maximum Length of a JSON Instance
Absolute Maximum Length for JSON Text Data
Absolute Maximum Length for JSON Binary Data
Determining the Maximum Length for a Binary-formatted JSON Column
Exceeding the Maximum Length
Maximum Length and the Storage of JSON Data
JSON Instance Maximum Length Examples
JSON INLINE LENGTH Specification
JSON Minimum Values for INLINE LENGTH
JSON Maximum Values for INLINE LENGTH
JSON Default Values for INLINE LENGTH
Examples: Specifying the INLINE LENGTH for a JSON Type
Character Sets for the JSON Data Type
Storage Formats for the JSON Data Type
Storage Format Comparison
BSON Storage Format
UBJSON Storage Format
Migrating from Text to Binary Storage Formats
NEW JSON Constructor
NEW JSON Constructor Syntax
NEW JSON Constructor Usage Notes
NEW JSON Constructor Examples
JSON Type Transform Groups
Casting JSON Data
Casting and Storage Formats
Casting JSON Data Examples
JSON Type Ordering
JSON Type Usage
Using the JSON Type with UDFs
Example: UDF Parameter Style SQL
Example: UDF Parameter Style TD_GENERAL
Example: SQL UDF with RETURN Clause
Example: UDF Returning a Value Using Dot Notation (Level 1)
Example: UDF Returning a Value Using Dot Notation (Level 2)
Example: UDF Returning a Value Using Dot Notation (Level 3)
Example: JSON Type Parameter and Return Type for a Java UDF
Using the JSON Type with Procedures (External Form)
Example: Procedure with Parameter Style SQL
Example: Procedure with Parameter Style TD_General
Example: JSON Type Parameter for a Java External Stored Procedure
Using the JSON Type with Procedures (SQL Form)
Example: Stored Procedures with JSON Type Parameters
Example: Stored Procedures with JSON Type Local Variables
Using the JSON Type with UDFs
Restrictions for the JSON Type
JSON String Syntax
Rules for JSON Data
Operations on the JSON Type
Importing and Exporting JSON Data
Creating and Altering Tables to Store JSON Data
Examples: Creating and Altering Tables for JSON Data
Compressing JSON Type Data
Example: Compressing JSON Type Data
Storing JSON Data
Storage Format Conversions
Validating JSON Data
Loading JSON Data Using Load Utilities
Example: Loading JSON Data
Inserting Values into a JSON Column
Examples: Inserting Values into a JSON Column
Migrate Data to the JSON Type
JSON Dot Notation (Entity Reference)
JSON Dot Notation (Entity Reference) Syntax
JSON Dot Notation (Entity Reference) Usage Notes
Ambiguity between a JSON Dot Notation Reference and References to Other Database Objects
ARRAY and JSON Type Element References
Column Reference Compared to JSON Type Dot Notation Reference
Setting Up the JSON Dot Notation Examples
JSON Dot Notation (Entity Reference) Examples
Examples: Unambiguous JSON Dot Notation
Examples: Ambiguous JSON Dot Notation
Example: Error: JSON Dot Notation Reference with Multiple Results
JSONPath Request
JSONPath Request Syntax
Example: JSONPath Request
Retrieving JSON Data Using SELECT
Retrieving JSON Data Using SELECT Example
Extracting JSON Data Using SELECT and JSON Methods
Examples: Extracting JSON Data
Setting Up the Examples Using Dot Notation in SELECT and WHERE Clause
Examples: Using Dot Notation in the SELECT List
Using Dot Notation in a WHERE Clause
Examples: Using Dot Notation in the WHERE Clause
Modifying JSON Columns
Examples: Modifying JSON Columns
Using the JSON Type in a DELETE or ABORT Statement
Examples: DELETE and ABORT with JSON
Creating a Join Index with a JSON Type
Examples: Creating a Join Index with a JSON Type
Setting Up Join Index Examples
Example: Use JOIN INDEX with JSON Dot Notation
Example: Use JOIN INDEX with JSONExtractValue
Collecting Statistics on JSON Data
Examples: Collecting Statistics on JSON Data
Setting Up Collect Statistics Examples
Example: Use COLLECT STATISTICS with a JSON Dot Notation Reference
Example: Use COLLECT STATISTICS with JSONExtractValue
JSON Methods, Functions, External Stored Procedures, and Table Operators
JSON Methods
AsBSON
AsBSON Syntax
AsBSON Usage Notes
Examples: Get the BSON Representation of JSON Data
AsJSONText
AsJSONText Syntax
AsJSONText Usage Notes
Example: Get the Text Representation of BSON Data
Combine
Combine Syntax
Combine Usage Notes
Examples: Combine
Setting Up the Combine Examples
Example: UNICODE Combine LATIN
Example: UNICODE Combine UNICODE
Example: LATIN Combine LATIN
Example: Result Is Implicitly a JSON ARRAY
Example: Explicit Statement of Result Type as JSON ARRAY
Example: Combine Two JSON ARRAYs and Explicitly State the Result Is a JSON ARRAY
Example: Combine a JSON ARRAY and a JSON Expression and Specify the Result Is a JSON ARRAY
Example: Error - Combine Two JSON ARRAYs and State the Result Is a JSON OBJECT
ExistValue
ExistValue Syntax
ExistValue Examples
Setting Up the ExistValue Examples
Example: Use ExistValue to Check for a Specific Child
Example: ExistValue Does Not Find the Specified Child
Comparison of JSONExtract and JSONExtractValue
JSONExtract
JSONExtract Syntax
JSONExtract Examples
Setting Up the JSONExtract Examples
Example: JSONExtract with Multiple Results
Example: JSONExtract with Filtered Results
Example: JSONExtract with NULL Results
JSONExtractValue and JSONExtractLargeValue
JSONExtractValue and JSONExtractLargeValue Syntax
JSONExtractValue and JSONExtractLargeValue Result Types
JSONExtractValue and JSONExtractLargeValue Usage Notes
JSONExtractValue and JSONExtractLargeValue Examples
Setting Up the JSONExtractValue and JSONExtractLargeValue Examples
Example: JSONExtractValue with Multiple Results
Example: JSONExtractValue with Filtered Results
Example: JSONExtractValue with NULL Results
Example: JSONExtractValue with Multiple Results Error
Example: Difference between JSONExtract and JSONExtractValue
KEYCOUNT
KEYCOUNT Syntax
KEYCOUNT Examples
KEYCOUNT Example 1
KEYCOUNT Example 2
METADATA
METADATA Syntax
METADATA Example
StorageSize
StorageSize Syntax
StorageSize Usage Notes
Examples: Get the Storage Size Required for a Storage Format
JSON Functions and Operators
ARRAY_TO_JSON
ARRAY_TO_JSON Syntax
ARRAY_TO_JSON Usage Notes
ARRAY_TO_JSON Examples
Setting Up the ARRAY_TO_JSON Examples
Example: Array to JSON Type Conversion Using the ARRAY_TO_JSON Function
Setting Up the ARRAY_TO_JSON Using ARRAY_AGG Examples
Example: Use ARRAY_AGG
Example: Use ARRAY_AGG as Input to ARRAY_TO_JSON
Example: Use ARRAY_AGG to Aggregate Salaries
Example: Use ARRAY_AGG to Aggregate Salaries as Input to ARRAY_TO_JSON
BSON_CHECK
BSON_CHECK Syntax
BSON_CHECK Usage Notes
Examples: Using BSON_CHECK to Validate BSON Data
DataSize
DataSize Syntax
DataSize Examples
GeoJSONFromGeom
GeoJSONFromGeom Syntax
GeoJSONFromGeom Result Types
GeoJSONFromGeom Usage Notes
GeoJSONFromGeom Examples
GeomFromGeoJSON
GeomFromGeoJSON Syntax
GeomFromGeoJSON Usage Notes
GeomFromGeoJSON Examples
JSON_CHECK
JSON_CHECK Syntax
JSON_CHECK Usage Notes
JSON_CHECK Examples
JSONGETVALUE
JSONGETVALUE Syntax
JSONGETVALUE Example
JSON_KEYS
JSON_KEYS Syntax
JSON_KEYS Result Types
JSON_KEYS Usage Notes
JSON_KEYS Examples
Setting Up the JSON_Keys Examples
Example: Use JSON_KEYS to Get Key Names with No Depth Specified
Example: Use JSON_Keys to Get Key Names for the Top Level Depth
Examples: Using the USING QUOTES Clause
Example: Get an Ordered List of Unique Keys from Documents
Example: Get a List of Unique Keys without Quotes
Example: Use JSON_KEYS with JSONExtractValue to Extract All Values
JSONMETADATA
JSONMETADATA Syntax
Format of Returned Metadata
NVP2JSON
NVP2JSON Syntax
NVP2JSON Usage Notes
NVP2JSON Examples
NVP2JSON Example 1
NVP2JSON Example 2
NVP2JSON Example 3
JSON Shredding
Differences between JSON_TABLE and TD_JSONSHRED
JSON Shredding with INSERT JSON Statement
Rules for Shredding JSON Data Using the INSERT JSON Statement
JSON AUTO COLUMN
Examples: Shredding JSON Data with INSERT JSON
JSON_TABLE
JSON_TABLE Syntax
JSON_TABLE Result Types
JSON_TABLE Usage Notes
Rules and Restrictions for JSON_TABLE
Supported Output Data Types
JSON_TABLE Examples
Setting Up the JSON_TABLE Examples
Example: JSON_TABLE Using Normal Column
Example: JSON_TABLE Using Extra Columns
Example: JSON_TABLE Using Ordinal Column
TD_JSONSHRED
TD_JSONSHRED Syntax
TD_JSONSHRED Result Types
TD_JSONSHRED Usage Notes
TD_JSONSHRED Examples
Example: TD_JSONSHRED
Example: TD_JSONSHRED Nested Data
Example: TD_JSONSHRED Nested Objects and Dot Notation
Example: TD_JSONSHRED Nested Arrays
Example: TD_JSONSHRED Multiple Potential Row Expressions
Example: TD_JSONSHRED Common Value
JSON_SHRED_BATCH and JSON_SHRED_BATCH_U
Required Privileges
JSON_SHRED_BATCH and JSON_SHRED_BATCH_U Syntax
JSON_SHRED_BATCH and JSON_SHRED_BATCH_U Usage Notes
Supported Data Types
Choosing between JSON_SHRED_BATCH and JSON_SHRED_BATCH_U
JSON_SHRED_BATCH and JSON_SHRED_BATCH_U Examples
Setting up the JSON_SHRED_BATCH Examples
Example: JSON_SHRED_BATCH Extracts from a JSON Object and Inserts into a Table
Example: Use JSON_SHRED_BATCH to Update a Table from a JSON Object
Example: Populate Multiple Tables from a JSON Object Using JSON_SHRED_BATCH
Setting Up the JSON_SHRED_BATCH JSONID and ROWINDEX Keyword Example
Example: JSON_SHRED_BATCH Using JSONID and ROWINDEX Keywords
JSON Publishing
Comparison of JSON_AGG and JSON_COMPOSE
Advantages of JSON_PUBLISH over JSON_AGG and JSON_COMPOSE
JSON Composition Using SELECT AS JSON
SELECT AS JSON Examples
JSON_AGG
JSON_AGG Syntax
JSON_AGG Result Type
JSON_AGG Usage Notes
JSON_AGG Examples
Setting Up the JSON_AGG Examples
Example: Using JSON_AGG without GROUP BY and No Parameter Names
Example: Using JSON_AGG without GROUP BY and with Parameter Names
Example: Using JSON_AGG with GROUP BY and with All Parameter Names
Example: Using JSON_AGG with Multiple GROUP BY and Parameter Names
JSON_COMPOSE
JSON_COMPOSE Syntax
JSON_COMPOSE Result Type
JSON_COMPOSE Usage Notes
JSON_COMPOSE Examples
Setting Up the JSON_COMPOSE Basic Examples
Example: Use JSON_COMPOSE to Extract Values from a Table and Compose a JSON Object
Example: Use JSON_COMPOSE with JSON_AGG
Setting up the JSON_COMPOSE Advanced Examples
Example: Use JSON_COMPOSE with Subqueries and GROUP BY
Example: Use JSON_COMPOSE with Multiple Subqueries and GROUP BY
JSON_PUBLISH
JSON_PUBLISH Syntax
Examples: JSON_PUBLISH
Conversion Rules for JSON Storage Formats
External Representations for the JSON Type
Data Type Encoding
SQL Capabilities Parcel
Database Limits (ConfigResponse) Parcel
StatementInfo Parcel
XML Data Type
Teradata Support for XML
Examples in this Document
Standards Compatibility
XML Data Type Syntax
XML INLINE LENGTH Specification
XML Minimum and Maximum Values for INLINE LENGTH
XML Default Values for INLINE LENGTH
Examples: Specifying the INLINE LENGTH for an XML Type
New XML Type Instances
XML Type Usage Notes
Example: Using the XML Type in a Table Definition
Example: XML Parameter in a Java UDF
Example: XML Parameter in a Java External Stored Procedure
Restrictions for the XML Type
XML Type Transform
XML Type Ordering
XML Type Cast
XML Data Type External Representation
Migrate Data to the XML Type
XML Operations
Processing Large XML Documents
Creating and Altering Tables to Store XML Data
Storing XML Data
Store XML Documents in a Table
Storing XML Data Related Information
Retrieving XML Data
XML Value Encoding and the Encoding Declaration
Document Size Limitation and Encoding
Creating a Join Index with an XML Type
Query XML Content Using XPath and XQuery
Improving XML Query Performance
Improving XML Query Performance Related Information
XSLT Transformation
Transform an XML Value Using a Stylesheet
Validate an XML Document
XML Validation Related Information
Convert XML Data to Relational Data
XML Type Usage Examples
Example: Loading an XML Document into a Table
Example: Loading Large XML Documents
Example: Storing a Schema in a Table
Example: Validating an XML Document
Example: XPath Query
Example: XQuery Query
Example: XSL Transform on XML Values
Example: Using Stylesheets in XSLT Transformations
Example: Converting XML Documents to Rows and Columns
XML Type Examples Related Information
Functions for XML Type and XQuery
CREATEXML
CREATEXML Syntax
Examples: Using CREATEXML to Create XML Type Instances
DataSize
DataSize Syntax
DataSize Examples
XMLQUERY
XMLQUERY Syntax
XMLQUERY Examples
XMLSERIALIZE
XMLSERIALIZE Syntax
XMLSERIALIZE Examples
Example: Using XMLSERIALIZE to Serialize an XML Value to a CLOB Value
Example: Using XMLSERIALIZE to Serialize an XML Value to a BLOB Value
XMLTABLE
XMLTABLE Syntax
XMLTABLE Examples
Example: Using XMLTABLE to Convert XML Data to Relational Data
Example: Using XMLTABLE without the COLUMNS Clause
Example: Using XMLTABLE with the default_clause
XMLDOCUMENT
XMLDOCUMENT Syntax
Examples: Using XMLDOCUMENT to Create an XML Document Node from an XML Type Value
XMLELEMENT
XMLELEMENT Syntax
Examples: Using XMLELEMENT to Construct an XML Element Node
XMLFOREST
XMLFOREST Syntax
Example: Using XMLFOREST to Construct a Sequence of XML Elements
XMLCONCAT
XMLCONCAT Syntax
Example: Using XMLCONCAT to Concatenate a List of XML Values
XMLCOMMENT
XMLCOMMENT Syntax
Example: Using XMLCOMMENT to Create a Comment Node
XMLPI
XMLPI Syntax
Example: Using XMLPI to Create an XML Processing Instruction Node
XMLTEXT
XMLTEXT Syntax
Example: Using XMLTEXT to Create an XML Text Node
XMLPARSE
XMLPARSE Syntax
XMLPARSE Examples
Example: Using XMLPARSE with the CONTENT and STRIP WHITESPACE Clauses
Example: Using XMLPARSE with the CONTENT and PRESERVE WHITESPACE Clauses
Example: Using XMLPARSE with the DOCUMENT and STRIP WHITESPACE Clauses
Example: Using XMLPARSE with the DOCUMENT and PRESERVE WHITESPACE Clauses
XMLVALIDATE
XMLVALIDATE Syntax
Example: Using XMLVALIDATE to Validate XML Values
XMLAGG
XMLAGG Syntax
Example: Using XMLAGG to Aggregate Multiple Rows to Construct an XML Value
XMLSPLIT
XMLSPLIT Syntax
Example: XMLSPLIT Usage Example
Methods on the XML Type
CREATESCHEMABASEDXML
CREATESCHEMABASEDXML Syntax
Example: Using CREATESCHEMABASEDXML to Create a Schema-validated and Type-annotated XML Value
CREATENONSCHEMABASEDXML
CREATENONSCHEMABASEDXML Syntax
Example: Using CREATENONSCHEMABASEDXML to Remove Type Annotations from a Schema-Based XML Value
EXISTSNODE
EXISTSNODE Syntax
Example: Using EXISTSNODE to Check If a Query Returns a Result
ISCONTENT
ISCONTENT Syntax
Example: Using ISCONTENT to Check if an XML Value is a Document Node with Child Elements
ISDOCUMENT
ISDOCUMENT Syntax
Example: Using ISDOCUMENT to Check if an XML Value is a Well-formed XML Document
ISSCHEMAVALID
ISSCHEMAVALID Syntax
Example: Using ISSCHEMAVALID to Check an XML Instance for Validity against an XML Schema
ISSCHEMAVALIDATED
ISSCHEMAVALIDATED Syntax
Example: Using ISSCHEMAVALIDATED to Check if an XML Value is a Validated, Type-annotated Value
XMLEXTRACT
XMLEXTRACT Syntax
Example: Using XMLEXTRACT to Evaluate a Query against an XML Instance
XSLTTRANSFORM
XSLTTRANSFORM Syntax
Example: Using XSLTTRANSFORM to Transform an XML Value
Schema and Stylesheet Management
Schema Management
Resolving Schema Dependencies
Example: Invoking the ConsolidateSchema Utility
Example: Consolidated Schema Document
Using Schemas
Stylesheet Management
Resolving Stylesheet Dependencies
Example: Invoking the ConsolidateStylesheet Utility
Example: Consolidated Stylesheet Document
Using Stylesheets
Schema and Stylesheet Management Related Information
XML Shredding and Publishing
Use XML Shredding Based on a Schema
Mapping XML Documents to Target Tables
Adding Schema Annotations
defaultDatabase Element
defaultEncoding Element
rootElement Element
Transaction and Operation Elements
Table Element
Column Element
retainedElements Element
Example: Annotated Schema
Example: Shredding an XML Document
AS_SHRED_BATCH
AS_SHRED_BATCH Syntax
AS_SHRED_BATCH Error Handling
XML Shredding Based on a Stylesheet
XSLT_SHRED_BATCH
XSLT_SHRED_BATCH Syntax
AS_SHRED_BATCH Error Handling
XSLT_SHRED_BATCH Examples
Setting Up the XSLT_SHRED_BATCH and XSLT_SHRED Examples
Example: XSLT_SHRED_BATCH <xsl:copy-of select="">
Example: XSLT_SHRED_BATCH <xsl:value-of select="">
Example: XSLT_SHRED_BATCH Combined <xsl:copy-of select=""> and <xsl:value-of select="">
Example: XSLT_SHRED_BATCH with a Transient SQL Expression
Example: XSLT_SHRED_BATCH defaultDatabase
Examples: XSLT_SHRED_BATCH Using a Context Parameter
Example: XSLT_SHRED_BATCH <xsl:param>
Example: XSLT_SHRED_BATCH Default Value for Absent or Empty Column Value
Example: XSLT_SHRED_BATCH Multiple Operations
XSLT Shredding Mapping Definition
Example XSLT Stylesheet
Transaction Element
Operation Elements
XSLT_SHRED
XSLT_SHRED Syntax
XSLT_SHRED Examples
Example: XSLT_SHRED Using genexp
Example: XSLT_SHRED Usage Example
Publish XML
Canonical XML Publishing
Mapping SQL Query Results to an XML Format
Adding Annotations to the Mapping Stylesheet
teradata_group Attribute
teradata_optional Attribute teradata_optional_attributes Attribute
teradata_sort Attribute
XMLPUBLISH
XMLPUBLISH Syntax
XMLPUBLISH_STREAM
XMLPUBLISH_STREAM Syntax
External Representations for the XML Type
Text Format for XML Values
Example: Retrieved Document
Document Retrieval
XPath/XQuery Result Retrieval
Text Format for XML Values Examples
Example: XQuery/XPath Query Returns the First Customer Child
Example: XQuery/XPath Query Returns a Sequence of Customer Elements
Example: XQuery/XPath Query Returns a Sequence of xs:integer or xs:double Values
Example: XQuery/XPath Query Uses XMLTABLE to Retrieve Items from a Sequence
Example: XQuery/XPath Query Returns Multiple Atomic Values Cast to the DECIMAL Data Type
Character Sets
XML Data Loading
Encoding of Documents for Loading
XML Data Loading as XML Data Type
XML Data Loading as VARCHAR or CLOB Data Type
XML Data Loading as BLOB Data Type
XML Data Unloading
Encoding of Documents for Retrieval
XML Data Retrieval as XML Data Type
XML Data Retrieval as VARCHAR or CLOB Data Type
XML Data Retrieval as BLOB Data Type
Data Type Encoding Numbers for the XML Type
Configuration Response Parcel
StatementInfo Parcel
Data Parcels
Encoding Names Supported by Teradata XML
XML Data Type Related Information
Character Shorthand Notation Used in This Document
How to Read Syntax
ANSIDATE
A DATE format of CHARACTER(10) for the session, formatted as ‘YYYY-MM-DD’ for importing and exporting DATE values.
INTEGERDATE
Encoded integers for importing and exporting DATE values.
When the session DATEFORM is INTEGERDATE, the default DATE format in field mode is 'YY YY/MM/DD' for date columns created and date constants in character form.