Rules for Using the JSON Option - Analytics Database - Teradata Vantage
SQL Data Manipulation Language
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
Introduction to SQL Data Manipulation Language
Changes and Additions
SELECT Statements
SELECT
SELECT Syntax
SELECT Syntax Elements
Usage Notes
Uses of the SELECT Statement
SELECT Subqueries
Locks and Concurrency
Derived Tables
NoPI Tables and SELECT Statements
SELECT Statements in Embedded SQL
DEFAULT Function in SELECT Statements
Specifying UDTs in an SQL Request
Invoking a Scalar UDF From a SELECT Statement
Returning a Varying Column Table External UDF Result
SELECT Statements and Derived Period Columns
SELECT and INSERT ... SELECT Statements with Set Operators
Joins
Activity Count Indicates the Number of Rows Returned
SELECT and Queue Tables
Logging Problematic Queries
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: Specifying UDTs in the SELECT List and WHERE Clause of SELECT Statements
Example: SELECT Statements Specifying the DEFAULT Function
Example: Dynamic Row Results Returned by Specifying Table Functions
Example: Scalar Subquery in the Select List of a SELECT Statement
Example: SQL UDF in the Select List of a SELECT Statement
Example: Creating a Time Series Using Expansion By an Interval Constant Value
Example: Invoking an SQL UDF From a Derived Table
Example: Invoking a UDF or Method Using a RETURNS Specification
Example: Invoking a UDF or Method Using a RETURNS STYLE Specification
Example: Selecting Rows From a Table With Row-Level Security Protection
Example: Row-Level Security Constraint and SELECT Statement When User Lacks Required Privileges
Example: Using a Table Operator with Multiple PARTITION BY Inputs
Example: Using a Multiple Input Table Operator with DIMENSION Input
Related Information
SELECT AND CONSUME
SELECT AND CONSUME Syntax
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
SELECT AND CONSUME Examples
Example: Returning the Row Having the Oldest QITS Value in a Queue Table
Related Information
SELECT ... INTO
WITH DELETED ROWS
WITH DELETED ROWS Examples
AS JSON
Usage Notes
Rules for Using SELECT AS JSON
AS JSON Examples
Examples: SELECT AS JSON
Select List Syntax
DISTINCT
Usage Notes
Examples
ALL
ALL Example
NORMALIZE Clause
NORMALIZE Clause Syntax
Usage Notes
Using SELECT with NORMALIZE
INSERT ... SELECT and NORMALIZE
NORMALIZE Clause Examples
Examples: Using NORMALIZE
.ALL Operator
.ALL Operator Examples: Using the .ALL Operator with Structured UDTs
TOP Clause
TOP Clause Syntax
Usage Notes
What the TOP n Operator Does
Rules and Restrictions for the TOP n Operator
Evaluation Order of TOP n in a SELECT
TOP n Operator Out Performs QUALIFY RANK and QUALIFY ROW_NUMBER
TOP Clause Examples
Examples: Comparing the Results of Specifying TOP n * and TOP n WITH TIES *
Related Information
WITH Modifier
WITH Modifier Syntax
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
Example: Specifying a Dynamic UDT in a Recursive Query
Examples: External UDFs
Example: Invoking an SQL UDF in a Recursive Query
Related Information
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
Usage Notes
Executing 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
Usage Notes
Uses for Derived Tables
Rules and Restrictions for Derived Tables
Derived Tables Examples
Example: Using Derived Tables for Multilevel Aggregation
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
Related Information
WHERE Clause
WHERE Clause Syntax
Usage Notes
Aggregates in a WHERE Clause
Expressions Containing LOBs
UDT Comparisons
ARRAY or VARRAY Data Type
Row-Level Security Constraint Values
SELECT AND CONSUME Subqueries
DEFAULT Function in a Search Condition
DEFAULT Function Data Type
DEFAULT Function Column Name
DEFAULT Function Comparison Operators
AND and OR Logical Operators
Using WHERE to Filter Character Data
Scalar UDFs
Scalar Subqueries
SAMPLE Clause in a Subquery
WHERE Clause Defines Condition for Joining Table Rows
EXISTS Quantifier
Specifying a Column PARTITION or PARTITION#L n
Use Consistent Predicate Domains
Join Efficiency and Indexes
NoPI Tables, Column-Partitioned Tables, and WHERE Clause Search Conditions
Unconstrained Joins
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 Within a Search Condition
Example: Specifying a Row-Level Security Constraint in a Search Condition
Related Information
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
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 Its GROUP BY Clause
Example: GROUP BY and PERIOD Value Expressions
Related Information
HAVING Clause
HAVING Clause Syntax
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
Related Information
QUALIFY Clause
QUALIFY Clause Syntax
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
Related Information
SAMPLE Clause
SAMPLE Clause Syntax
Usage Notes
SAMPLEID Expression
SAMPLEID Expression Syntax
SAMPLEID Expression Examples
Examples: Sample Clause
EXPAND ON Clause
EXPAND ON Clause Syntax
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
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
ORDERING 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
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
Usage Notes
Specifying Subqueries
Subqueries Examples
Example: Simple Subquery Using the IN Logical Predicate
Example: Simple Subquery Using an AVG Aggregate Expression
Example: Simple Subquery Using the ALL Logical Predicate
Example: Subquery Using the SUM and COUNT Aggregate Functions
Example: Subqueries Using the RANK Functions and QUALIFY Clauses
Related Information
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 Subqueries Examples
Example: Combining Equality and Inequality Conditions in a Correlated Subquery
Example: Using SELECT COUNT(*) in a Correlated Subquery
Related Information
Scalar Subqueries
Working with External Data
Query Foreign Tables
Query Foreign Tables with JSON Data
Query Foreign Tables with CSV Data
CSV File Headers and Schemas
Query Foreign Tables with Parquet Data
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
Setting Up an Object Store for River Flow Data
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: 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 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
Set Operators
SQL Set Operators
Teradata SQL Set Operators
query_term Syntax
query_factor Syntax
query_expression Syntax
Related Information
Rules for Set Operators
Duplicate Rows
Operations That Support Set Operators
SELECT AND CONSUME Statement
Support for ORDER BY Clause
Restrictions on the Data Types Involved in Set Operations
Related Information
Precedence of Set Operators
Example: Select Statement
Using Parentheses to Customize Precedence
Related Information
Retaining Duplicate Rows Using the ALL Option
Example
Attributes of a Set Result
Examples
Example
Example
Example
Example of How the Character Set is Determined for the Query
Set Operators With Derived Tables
Example 1
Restrictions
Example 2
Set Operators in Subqueries
Examples
Example 1
Example 2
Example 3
Example 4
Example 5
Example 6
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
Support for the GROUP BY Clause
Restrictions
Examples
Example 1
Example 2
Example 3
Related Information
Queries Connected by Set Operators
Number of Expressions in SELECT Statements
WITH Clause
GROUP BY and ORDER BY Clauses
Table Name in SELECT Statements
Data Type Compatibility
Related Information
INTERSECT Operator
INTERSECT Operator Syntax
INTERSECT Operator Usage Notes
Rules for INTERSECT
Attributes of a Set Result
Data Type of Nulls
Duplicate Row Handling
Example: INTERSECT
MINUS/EXCEPT Operator
MINUS/EXCEPT Operator Syntax
Usage Notes
MINUS/EXCEPT and NULL
Duplicate Rows
UNION Operator
UNION Operator Syntax
UNION Operator Usage Notes
Valid UNION Operations
Unsupported Operations
Description of a UNION Operation
UNION and NULL
Duplicate Rows
Unexpected Row Length Errors: Sorting Rows for UNION
Examples
Example: Selecting the Name, Project, and Employee Hours
Example: Determining the Number and Names of Employees
Example: Merging Lists of Values
Example: Performing a Union Operation to Find Hours Worked
UNION Operator and the Outer Join
Example: Effect of Explicit NULLs on Data Type of a UNION
Example: Effect of the Order of SELECT Statements on Data Type
Example: LOB Support in a UNION ALL Query
Related Information
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
Usage Notes
Examples
Related Information
BEGIN TRANSACTION
BEGIN TRANSACTION Syntax
Usage Notes
Rules for Embedded SQL
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
Scenarios
Related Information
CALL
CALL Syntax
Usage Notes
Invocation
Rules for Executing SQL Procedures and External Stored Procedures
Rules For Specifying Input And Output Parameters
Rules For Call Arguments In BTEQ And CLIv2
Rules For Call Arguments In ODBC And JDBC
Rules For Call Arguments In Nested Procedures
Rules For Calling Procedures From Embedded SQL
Rules for Calling a Procedure With Dynamic Result Sets From Embedded SQL
Session Dateform and Called Procedures
Retrieving Values of Output Parameters
Status of Unqualified Referenced Objects
Dropped, Renamed, or Replaced Objects
Java External Procedure-Specific Behavior
Called Procedure Priority
Errors and Failures in Procedure Execution
Errors and Failures in Nested Procedures
Aborting a CALL Statement
Asynchronous and Synchronous Abort Logic
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
Related Information
CHECKPOINT
CHECKPOINT Syntax
Usage Notes
CHECKPOINT Example
COMMENT (Comment-Retrieving Form)
COMMENT Syntax (Comment-Retrieving Form)
COMMENT Examples
COMMIT
COMMIT Syntax
COMMIT Examples
DELETE
DELETE Syntax
Usage Notes
Locks and Concurrency
DELETE Processing Time
Unconstrained Fastpath Delete Processing
Restrictions on Fastpath Deletions
Fastpath Positioning Requirements
Position within the Transaction
Proper Positioning for Session Mode and Transaction Type
Fastpath Delete and Join Indexes
Fastpath Delete for Multiple DELETE Statements
Constrained (Slow Path) DELETE Processing
Duplicate Rows and DELETE Processing
General Rules for Using DELETE
Deleting Rows Using Views
Subqueries in a DELETE Statement
Rules for Using Scalar Subqueries in a DELETE Statement
Rules for Using Correlated Subqueries in a DELETE Statement
DELETE Statement in the Last Request
Deferred Deletion Applies Only to Range Terms
Deferred Deletion is Supported for LIKE Terms
Deferred Deletion and Join Indexes
Collation and Row Deletion
Column Partitioned Tables and DELETE
DELETE for NoPI Tables
Queue Tables and DELETE
DELETE Support for External UDT Expressions
DELETE Support for SQL UDT Expressions
Embedded SQL and Stored Procedure Error Condition Handling
DELETE Support for Load Isolated Tables
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 Multistatement Request
Example: Join Condition DELETE With FROM Clause and Correlation Name
Example: Join Condition DELETE With No FROM Clause
Example: Join Condition DELETE With Derived Table Subquery
Example: UDT Support For DELETE Statements
Example: DELETE and NoPI Tables
Example: Using an SQL UDF in a DELETE Request WHERE Condition
Example: Deleting from a Table with an Implicit Isolated Load Operation
Example: Deleting from a Table with an Explicit Isolated Load Operation
Example: Deleting a Row From a Table With Row-Level Security Protection
Example: Row-Level Security DELETE and SELECT Constraints For User Lacking Required Privileges (DELETE)
EXPLAIN Request Modifier with a DELETE Statement
ECHO
ECHO Syntax
ECHO Example
END TRANSACTION
END TRANSACTION Syntax
EXECUTE (Macro Form)
EXECUTE Syntax (Macro Form)
EXECUTE Examples
EXECUTE FUNCTION
EXECUTE FUNCTION Syntax
EXECUTE FUNCTION Syntax Elements
EXECUTE FUNCTION Examples
INSERT/INSERT ... SELECT
INSERT/INSERT ... SELECT Syntax
Usage Notes
Locks and Concurrency
Inserting using the DEFAULT Function Option, the DEFAULT VALUES Option, or Without Specifying a Value
Inserting into UDT Columns
Inserting into Row-Partitioned Tables, Global Temporary Tables, and NoPI Tables
Rules for Using HASH BY or LOCAL ORDER BY to Insert Rows
Multistatement and Iterated INSERT Requests
Inserting When Using a DEFAULT Function
Using INSERT ... SELECT with Tables That Have Row-Level Security
INSERT, DEFAULT Function, PERIOD Value Constructor, Scalar UDFs, UDTs, and Stored Procedures
Inserting Rows into Queue Tables
Valid and Invalid INSERT Operations
Duplicate Rows and INSERT
INSERT Process
Inserting Rows through Views
Subqueries in INSERT Requests
SELECT AND CONSUME Subqueries in INSERT Requests
Data Takes the Attributes of the New Table
Logging Errors for INSERT ... SELECT Requests
Fast Path INSERT ... SELECT Requests
INSERT in Embedded SQL and Stored Procedures
Inserting into Load Isolated Tables
Rules for Using the JSON Option
INSERT/INSERT ... SELECT Examples
Example: Inserting a Row
Example: Insert Using a SELECT Subquery
Example: Insert Using a SELECT Subquery Without Target Column List
Example: Insert with Named Columns
Example: Insert Ordered Entries
Example: Bulk Insert
Example: INSERT Operation Using Single-Writer Lock
Example: INSERT and GENERATED ALWAYS Identity Columns
Example: INSERT and GENERATED BY DEFAULT Identity Columns
Example: Identity Columns and INSERT ... SELECT
Example: INSERT and Queue Tables
Example: Non-Valid Use of PARTITION In VALUES Clause of INSERT Request
Example: Simple INSERT Requests Using a DEFAULT Function
Example: Using the DEFAULT Function with INSERT ... SELECT
Example: Logging Errors with INSERT ... SELECT
Example: Using the ST_Geometry Data Type to Represent Other Geospatial Data Types for INSERTs
Example: Using the ST_Geometry Data Type When Inserting Geospatial Data Into Tables
Example: INSERT Using a Scalar Subquery
Example: INSERT Using a PERIOD Value Constructor
Example: Passing an SQL UDF to a Single-Row Request
Example: HASH BY Option with NoPI Table
Example: LOCAL ORDER BY Option
Example: Using RANDOM to Redistribute Data Blocks of Rows and Individual Rows Before Copying Them Locally
Example: Inserting into a Table with an Implicit Isolated Load Operation
Example: Inserting into a Table with an Explicit Isolated Load Operation
Examples: Inserting Data in JSON Format
LOCKING Request Modifier
LOCKING Request Modifier Syntax
Usage Notes
LOCKING Request Modifier and CALL Requests
LOCKING Request Modifier Use With DML Statements
Canceling a Lock
Positioning Explicit Lock Requests
Using Locks with NULL SQL Requests
Using LOCKING ROW
Multiple Locks
Referencing a Locked Object
Specify the Keyword For the Object To Be Locked
Locks and Views
When the Request and View Referenced Include LOCKING Request Modifiers
READ Locks and Canceled Rollback Operations
Determining Which Locks Are Set
LOCKING Request Modifier Examples
Example: LOCKING Request Modifier
Example: LOCKING Request Modifier and Secondary Indexes
Example: LOCKING ROW
Example: NOWAIT Option
MERGE
MERGE Syntax
Usage Notes
Locks and Concurrency
MERGE Statement
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 Within a Single SQL Request
Example: Target Table Composite Primary Index
Example: ON Clause Conditions Must Be ANDed With The Primary Index and Partitioning Column Equality Constraints
Example: For a RPPI Table, the ON Clause Must Specify a Condition on the Partitioning Column and the INSERT Specification Must Match
Example: Incorrect Examples Because of ON Clause Errors or Mismatches Between the ON Clause and the INSERT Specification
Example: MERGE With ON Clause and UPI or USI
Example: MERGE Updating a Primary Index
Example: MERGE and Identity Columns
Example: MERGE and Target Columns
Example: Reference the Source or Target in the ON, WHEN MATCHED, or WHEN NOT MATCHED Clauses
Example: Specifying the Partitioning Column Set in the ON Clause When the Target Relation Has a Row-Partitioned Primary Index
Example: You Cannot Substitute the System-Derived PARTITION Column For the Partitioning Column Set For a MERGE Operation With a RPPI Target Table
Example: With a Guaranteed Single-Row Source Relation, You Do Not Need To Specify the Partitioning Column Set
Example: Using the Target Table as the Source Table
Example: Using the BEGIN Period Bound Function as a Condition When Merging Into a RPPI Table
Example: Using the BEGIN and END Period Bound Functions as Conditions When Merging Into a RPPI Table
Example: Failure Because the Matching Condition is Defined on a PERIOD Bound Function
Example: Invoking an SQL UDF From a MERGE Request
Example: Merging into a Table with an Implicit Isolated Load Operation
Example: Merging into a Table with an Explicit Isolated Load Operation
Example: Executing a MERGE Update Request When Both the Target Table and the Source Table Have Row-Level Security Constraints
Example: Application of Row-Level Security SELECT Constraints When User Lacks Required Privileges (MERGE Request)
ROLLBACK
ROLLBACK Syntax
Usage Notes
Definition and Termination of ANSI Transactions
ROLLBACK Is Explicit
Rules for Embedded SQL
ROLLBACK and ABORT are Synonyms
Actions Performed by ROLLBACK
Actions Performed by ROLLBACK with Embedded SQL
ROLLBACK with a WHERE Clause
ROLLBACK with a UDT in the WHERE Clause
Rules for Using a Scalar UDF in a ROLLBACK Request
Rules for Using Correlated Subqueries in a ROLLBACK Request
Rules for Using Scalar Subqueries in ROLLBACK Requests
Multiple ROLLBACK Requests
Two Types of ROLLBACK Requests
ROLLBACK with BTEQ
ROLLBACK Examples
Example: ROLLBACK with a UDT in the WHERE Clause
Example: Using an SQL UDF in the WHERE Clause of a ROLLBACK Request
UPDATE
UPDATE Syntax (Basic Form, No FROM Clause)
UPDATE Syntax (Basic Form, FROM Clause)
UPDATE Syntax (Joined Tables Form)
Usage Notes
Locks and Concurrency
Activity Count
Duplicate Rows and UPDATE
Duplicate Row Checks
Large Objects and UPDATE
UPDATE Processing Time
Rules for Embedded SQL and Stored Procedures
Queue Tables and UPDATE
Rules for Updating Partitioning Columns of a Row-Partitioned Table
Rules for Updating a Table with a Row-Partitioned Join Index
Rules for Updating Column-Partitioned Tables
Identity Columns and UPDATE
Updating of GENERATED ALWAYS Identity Columns and PARTITION Columns
Updating Distinct UDT Columns
Updating Structured UDTs Using a Mutator SET Clause
Rules for Updating Rows Using Views
Rules for Using Scalar Subqueries in UPDATE Requests
Rules for Using a Scalar UDF in an UPDATE Request
Rules for Using the DEFAULT Function With Update
Rules for Using a PERIOD Value Constructor With UPDATE
Nonvalid Uses of UPDATE
FROM Clause and UPDATE
UPDATEs With a Join
UPDATE Support for Load Isolated Tables
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 its WHERE Clause
Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause
Example: UPDATE With a Scalar Noncorrelated Subquery in its SET Clause
Example: UPDATE With a Scalar Correlated Subquery in its SET Clause
Example: UPDATE With a Correlated Subquery in its WHERE Clause
Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause
Example: UPDATE With a Join
Example: UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses
Example: UPDATE With a Mutator SET Clause
Example: Update Requests Using the DEFAULT Function
Example: UPDATE Using a PERIOD Value Constructor
Example: Updating a NoPI Table
Example: Updating a Table with an Implicit Isolated Load Operation
Example: Updating a Table with an Explicit Isolated Load Operation
Example: Application of Row-Level Security SELECT and UPDATE Constraints When User Lacks Required Privileges (UPDATE Request)
UPDATE (Upsert Form)
UPDATE Syntax (Upsert Form)
Usage Notes
Definition of Upsert
Purpose of the Atomic Upsert Operation
Rules for Using Atomic Upsert
Locks and Concurrency
UPDATE (Upsert Form) Insert Operations and Row-Partitioned Tables
UPDATE (Upsert Form) Update Operations and RPPI Tables
UPDATE (Upsert Form) and Join Indexes
UPDATE (Upsert Form) and Subqueries
Queue Tables and UPDATE (Upsert Form)
UPDATE (Upsert Form) As a Triggering Action
UPDATE (Upsert Form) As a Triggered Action
Rules and Restrictions
Unsupported Syntax or Features
Using UPDATE (Upsert Form) Requests with Scalar Subqueries
Using UPDATE (Upsert Form) Requests on RPPI Tables
Using UPDATE (Upsert Form) with the DEFAULT Function
UDTs and UPDATE (Upsert Form)
UPDATE (Upsert Form) Support for Load Isolated Tables
UPDATE (Upsert Form) Examples
UPDATE Upsert Examples
Example: Upsert Update
Example: Upsert Insert
Example: Upsert Specifying Different Tables
Example: Upsert on Primary Index
Example: Upsert Without Specifying Primary Index
Example: Upsert Without ELSE Clause
Example: Upsert Update Using the DEFAULT Function
Example: Upsert Update Using a Period Bound Function
USING Request Modifier
USING Request Modifier Syntax
Usage Notes
USING Is Not Supported for Embedded SQL
Actions Performed by USING
USING Variables
USING Support for Scalar UDFs
Valid Request Types
USING and the EXPLAIN Request Modifier
USING Support for UDTs
USING Support for Large Objects
Inline Mode
Deferred Mode
Locator Mode
LOB Transfer Mode Comparison
Client Application Restrictions on USING With Large Objects
USING and DateTime System Functions
ANSI DateTime Considerations
Example of ANSI DateTime and Interval With USING
ANSI DateTime and Parameterized Requests
Array Considerations for Specifying TOP n as a USING Parameter
Character String Definitions in a USING Request Modifier
data_type Considerations for a Japanese Character Site
Non-GRAPHIC Character Data Representation
Character String Assignment and GRAPHIC Columns
CHARACTER and GRAPHIC Server Character Set Limitations for INSERT Operations
Character Data Import Process
UPPERCASE Option and Character Parameter Definition in USING
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 Within a Session
Example: Iterated Requests
Example: USING Request Modifier with TOP Operator
Example: Non-Support for Iterated Requests With TOP n
Example: Dynamic UDT Expressions
Example: USING and SQL UDF Invocation
Query and Workload Analysis Statements
COLLECT DEMOGRAPHICS
COLLECT DEMOGRAPHICS Syntax
COLLECT DEMOGRAPHICS Examples
COLLECT STATISTICS (QCD Form)
COLLECT STATISTICS Syntax (QCD Form)
Usage Notes
Invocation
Where Column and Index Statistics Are Stored
Rules and Guidelines for COLLECT STATISTICS (QCD Form)
Collecting QCD Statistics on Multiple Columns
Collecting QCD Statistics on the PARTITION Column of a Table
COLLECT STATISTICS (QCD Form) and UDTs
COLLECT STATISTICS (QCD Form) And Large Objects
COLLECT STATISTICS (QCD Form) Does Not Collect Statistics For The Optimizer
Implicit Recollection of QCD Statistics Is Not Supported
COLLECT STATISTICS (QCD Form) Is Not Treated As DDL By The Transaction Manager
COLLECT STATISTICS (QCD Form) Collects New Statistics Even If Statistics Exist In the Data Dictionary or QCD
Quality of Statistics As a Function of Sample Size
COLLECT STATISTICS (QCD Form) Examples
Example: Collect Statistics on a Single-Column NUSI
Example: Collect Statistics On Index Using an Alternate Syntax
Example: Collecting Single-Column PARTITION Statistics
Example: Collecting Multi-Column PARTITION Statistics
DROP STATISTICS (QCD Form)
DROP STATISTICS Syntax (QCD Form)
DROP STATISTICS Examples
DUMP EXPLAIN
DUMP EXPLAIN Syntax
DUMP EXPLAIN Examples
EXPLAIN Request Modifier
EXPLAIN Request Modifier Syntax
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
INITIATE INDEX ANALYSIS
INITIATE INDEX ANALYSIS Syntax
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
Usage Notes
Examples
RESTART INDEX ANALYSIS
RESTART INDEX ANALYSIS Syntax
RESTART INDEX ANALYSIS Examples
Notation Conventions
How to Read Syntax
Character Shorthand Notation Used in This Document
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
Additional Information
Teradata Links
You can only shred JSON data in text format. You cannot use the INSERT statement to shred JSON data that is in one of the binary formats such as BSON or UBJSON.
The shredded data is in VARCHAR format and implicit casting is used to convert the VARCHAR data to the target table column format. If the VARCHAR data cannot be CAST to the target column format, the insertion fails. For example, if the target column is VARBYTE, casting values other than NULL to VARBYTE returns an error because JSON does not have a matching textual value for binary data.
The INSERT statement processes a single row of JSON data with JSON OBJECT at the root. That is, the JSON data starts with '{' as the first non-white space character. You cannot insert multiple rows of data using the INSERT statement.
The INSERT statement supports Load Isolation options if the target table is an LDI table.
The target table must be a table and not a view.
The column name matching is not case sensitive.
If the same column is matched multiple times, the data stored is the last match.
If any of the target table columns is NOT NULL, and the JSON input data does not contain any data for the column, the following rules apply:If the target column does not have a DEFAULT value, an error is returned.
If the target column has a DEFAULT value and the JSON data is a string literal, the DEFAULT value is inserted into the target table.
If the target column has a DEFAULT value, and INSERT JSON uses parameterized SQL, the DEFAULT value is ignored and an error is returned. In this case, specifying a JSON literal is preferable to using parameterized SQL