Related Topics - Advanced SQL Engine - Teradata Database
SQL Operators and User-Defined Functions
Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
qqu1556127655717.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™
Introduction to SQL Operators and User-Defined Functions
Overview
Changes and Additions
Compression/Decompression Functions
Overview
CAMSET
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Restrictions
Uncompressing Data Compressed with CAMSET
Examples
Example: Compressing Unicode Values
Example: Querying for Compressed Values
Related Topics
CAMSET_L
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with CAMSET_L
Examples
Example: Compressing Latin Values
Example: Querying for Compressed Latin Values
Related Topics
DECAMSET
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Unicode Values
DECAMSET functions Related Topics
DECAMSET_L
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Example: DECAMSET_L Function
Related Topics
JSON_COMPRESS
ANSI Compliance
Argument Type and Rules
Result Type
Example: Compressing JSON Data Types
Related Topics
JSON_DECOMPRESS
ANSI Compliance
Argument Type and Rules
Result Type
Example: Decompressing JSON Data Types
Related Topics
LZCOMP
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with LZCOMP
Examples
Example: Compressing Unicode Values
Example: Querying for the Compressed Unicode Values of the Description column
Related Topics
LZCOMP_L
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with LZCOMP_L
Examples
Example: Compressing Latin Values
Example: Querying for the Compressed Latin Values of the Description column
Related Topics
LZDECOMP
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Unicode Values with LZCOMP
Related Topics
LZDECOMP_L
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Latin Values with LZCOMP_L
Related Topics
TD_LZ_COMPRESS
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with TD_LZ_COMPRESS
Related Topics
TD_LZ_DECOMPRESS
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Related Topics
TS_COMPRESS
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Examples
Example: Compressing the TIME Data Type
Example: Compressing the TIMESTAMP Data Type
Related Topics
TS_DECOMPRESS
ANSI Compliance
Argument Type and Rules
Result Type
Example: Creating a Compressed TIME Column
Related Topics
TransUnicodeToUTF8
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Restrictions
Example: Uncompressing Data Compressed with TransUnicodeToUTF8
Related Topics
TransUTF8ToUnicode
ANSI Compliance
Argument Type and Rules
Result Type
Usage Notes
Example: Uncompressing Unicode Values with TransUTF8ToUnicode
Related Topics
Export Width Procedures
Overview
About Export Width
ReplaceExportDefinition
Authorization
Argument Types
Usage Notes
Export Width Rules
Example: ReplaceExportDefinition
RemoveExportDefinition
Authorization
Argument Types
Usage Notes
Example: RemoveExportDefinition
File System Information Macros and Functions
Overview
Related Topics
CreateFsysInfoTable/CreateFsysInfoTable_ANSI
Authorization
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
PopulateFsysInfoTable/PopulateFsysInfoTable_ANSI
Authorization
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'
AlterFsysInfoTable_TD16/AlterFsysInfoTable_ANSI_TD16
Authorization
Examples
Example: Using the PopulateFsysInfoTable Macro to Populate a Pre-Existing Table
Example: Migrating Tables to a Prior Release
Heatmap Table Function and Macro (tdheatmap and tdheatmap_m)
Differences Between the tdheatmap Function, Macro and View
Example: Using the tdheatmap Function
Example: Using the tdheatmap_m Macro
Example: Using tdheatmap to Create a View
Returned Columns
Usage Notes
Examples
Example: Data Temperature Report Showing a Specific Table
Example: Showing Cylinders in TIM Cache
Example: Heatmap Report Showing Tables with Different Subtable Types
Related Topics
Map Functions, Macros, and Procedures
Overview
Map Functions
SYSLIB.ContiguousMapAMPs
ANSI Compliance
Usage Notes
Example: Identifying Which AMPs are in a Contiguous Map
SYSLIB.SparseMapAMPs
ANSI Compliance
Usage Notes
Example: Identifying an AMP with a Specific Table
SYSLIB.SparseTableAMPs
ANSI Compliance
Usage Notes
Example: Identifying AMPs for a Table with a Sparse Map
Map Macros
Map Procedures
AdjustSpace
ANSI Compliance
Argument Types
Result Type
Examples
Example: Adjusting the Space Settings After a System Expansion
Example: Using AdjustSpace
Usage Notes
Advisor Procedures
AddExclusionListEntrySP(X)
ANSI Compliance
Argument Type
Usage Notes
Examples
Example: Creating an Exclusion List Named ‘MyExclusions’
Example: Analyzing Exclusions Defined in 'MyExclusions'
AddMapListEntrySP(X)
ANSI Compliance
Argument Types
Usage Notes
Example: Calling AddMapListEntrySP(X)
AddObjectListEntrySP(X)
ANSI Compliance
Argument Types
Usage Notes
Examples
Example: Creating an Object List
Example: Analyzing Objects Defined in 'MyObjects'
Example: Creating Object List ‘ObjNotInMap2’
AnalyzeSP
ANSI Compliance
Argument Types
Result Type
Usage Notes
Example: Using AnalyzeSP
CleanUpAnalyzerSP
ANSI Compliance
Argument Types
Example: Calling CleanUpAnalyzerSP
CreateExclusionListSP
ANSI Compliance
Argument Types
Result Type
Usage Notes
Example: Calling CreateExclusionListSP
CreateExpansionMaps
Example: Using CreateExpansionMaps
CreateMapListSP
ANSI Compliance
Argument Types
Result Type
Usage Notes
Example: Calling CreateMapListSP
CreateObjectListSP
ANSI Compliance
Argument Types
Result Type
Usage Notes
Example: Calling CreateObjectListSP
MonitorAnalyzeSP
ANSI Compliance
Argument Types
Result Types
Example: Calling MonitorAnalyzeSP
PostExpansionAction
Example: Using PostExpansionAction
Mover Procedures
ManageMoveTablesSP
ANSI Compliance
Argument Types
Result Type
Usage Notes
Example: Executing the Actions in the 'MyNewMapActions' List
MoveTablesSP
ANSI Compliance
Argument Types
Usage Notes
Examples
Example: Using BTEQ to Execute Queued Actions
Example: Move a Table to a Map
MonitorMoveTablesSP
ANSI Compliance
Argument Types
Result Type
Example: Using MonitorMoveTablesSP
StopMoveTablesSP
ANSI Compliance
Argument Types
Result Type
Usage Notes
Example: Using StopMoveTablesSP
StopMoverSP
ANSI Compliance
Argument Types
Example: Using StopMoverSP
StopSerialWorkerSP
Example: Using StopSerialWorkerSP
CleanUpMoveTablesSP
ANSI Compliance
Usage Notes
Example: Calling CleanUpMoveTablesSP
MoveTDMapsTablesSP
ANSI Compliance
Argument Types
Result Type
Example: Calling MoveTDMapsTablesSP
TruncateHistorySP
ANSI Compliance
Argument Types
Result Type
Usage Notes
Example: Calling TruncateHistorySP
Table Operators
Overview
CALCMATRIX
ANSI Compliance
Data Type
Authorization
Usage Notes
Examples
Example: One Input Matrix, Single SQL Statement
Example: One Input Matrix, Multiple SQL Statements
Example: Multiple Input Matrixes, One SQL Statement
Related Topics
Cogroups
Example: Cogroup Used for a Table Operator with Multiple Inputs
Related Topics
FeatureNames_TBF
Columns
Example: Generating a Report on Feature Usage
R Table Operator
Installation of R Components and Packages
Using ExecR to Execute R Scripts and Table Operators
Related Topics
READ_NOS
READ_NOS Syntax
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Examples
Setting Up an Object Store for River Flow Data
Define a Function Mapping for READ_NOS
Example: Creating an Authorization Object
Example: Function Mapping Definition Using EXTERNAL SECURITY Clause for JSON Data Files
Example: Function Mapping Definition Using ANY IN TABLE for JSON Data Files
Example: Using READ_NOS with NOSREAD_KEYS Return Type
Example: Using READ_NOS with NOSREAD_RECORD Return Type
Example: Using READ_NOS with NOSREAD_RAW Return Type
Example: Using READ_NOS to List Files in a Location
Example: Using READ_NOS to Display Keys in a JSON File without an Extension
Example: Using READ_NOS to Query an External JSON File
Example: Using READ_NOS to Query an External JSON File Without an Extension
Example: Displaying Keys in a CSV File Using READ_NOS
Example: Using READ_NOS to Query an External CSV File
Example: Using READ_NOS to Query an External CSV File Without an Extension
SCRIPT
ANSI Compliance
Input and Output to the Script File
Data Types
Setting the Database Search Path
Executing LINUX Commands
Executing Scripts
Script Files and Security
Setting Memory Limits
Troubleshooting
Examples
Example: SCRIPT Table Operator Function
Example: Use SYSUIF.INSTALL_FILE to Install Files and then Run the SCRIPT Table Operator
Example: Use the SCRIPT Table Operator to Invoke a Python Script
Example: Creating JSON Output by Using the SCRIPT Table Operator to Invoke Python Modules
Example: SCRIPT Calls Python for Data Type Conversion
Related Topics
TD_DBQLParam
ANSI Compliance
Invocation
Argument Types
Result Type
Usage Notes
Example: TD_DBQLParam
TD_DBQLFUL
ANSI Compliance
Invocation
Argument Types
Result Types
Usage Notes
Examples
TD_UNPIVOT
ANSI Compliance
Data Type
Authorization
Examples
Example: Twelve Columns Unpivoted to One
Example: More Than One Value Column
Usage Notes for Examples
User-Defined Functions
Scalar UDF
ANSI Compliance
Restrictions
Authorization
UDF Arguments
Result Data Type
Default Title
Examples
Example 1
Example 2
Related Topics
Aggregate UDF
ANSI Compliance
Restrictions
Authorization
Usage Notes
Example: Aggregate UDF
Related Topics
Window Aggregate UDF
ANSI Compliance
Authorization
Arguments to Window Aggregate UDFs
Supported Window Types for Aggregate UDFs
Unsupported Window Types for Aggregate UDFs
Partitioning
Result Type and Format
Usage Notes
Problems With Missing Data
Restrictions
Example
Related Topics
User-Defined Functions
ANSI Compliance
Restrictions
Authorization
Usage Notes
Related Topics
UDF Invocation
User-Defined Type Expressions/Methods
Overview
UDT Expression
ANSI Compliance
Authorization
Usage Notes
Example: Methods and Functions
Examples
Example: Column Name
Example: CAST
Example: NEW
Related Topics
NEW
ANSI Compliance
Usage Notes
Default Constructor
Determining Which Constructor is Invoked
Examples
Example
Example 2
Related Topics
NEW JSON
Related Topics
NEW VARIANT_TYPE
ANSI Compliance
Usage Notes
Restrictions
Examples
Example
Example 2
Related Topics
NEW XML
Related Topics
Method Invocation
ANSI Compliance
Observer and Mutator Methods
Usage Notes
Restrictions
Example: UDT Expression Method
Related Topics
Script Installation Procedures
Overview
Execution Rules
Installing and Registering External Language Scripts
SYSUIF.INSTALL_FILE
DROP TYPE Syntax Elements
uif_name
uif_filename
locspec
Example: Calling SYSUIF.INSTALL_FILE to Install a Python Script
Related Topics
Replacing External Language Scripts
Before You Begin
SYSUIF.REPLACE_FILE
DROP TYPE Syntax Elements
uif_name
uif_filename
locspec
override_lock
Example: Replacing an Existing File Mapper
Redistributing External Language Scripts
Before You Begin
SYSUIF.REDISTRIBUTE_FILE
DROP TYPE Syntax Elements
uif_name
Example: Redistributing External Language Scripts Argument
Displaying the User-Installed File
Related Topics
Removing External Language Scripts
Before You Begin
SYSUIF.REMOVE_FILE
DROP TYPE Syntax Elements
uif_name
override_lock
Example: Removing External Language Script
Notation Conventions
How to Read Syntax
Character Shorthand Notation Used in This Document
Additional Information
Teradata Links
For information on SQL aggregate functions, see "Aggregate Functions" in Teradata Vantage™ - SQL Functions, Expressions, and Predicates , B035-1145
For more information on window aggregate UDFs, see Window Aggregate UDF .
For more information on implementing aggregate UDFs, see Teradata Vantage™ - SQL External Routine Programming , B035-1147
For more information on CREATE FUNCTION AND REPLACE FUNCTION, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples , B035-1144Teradata Vantage™ - Database Administration , B035-1093
For more information on EXECUTE FUNCTION and UDTUSAGE privileges, see Teradata Vantage™ - SQL Data Control Language , B035-1149
For more information on the TD_ANYTYPE data type, see Teradata Vantage™ - Data Types and Literals , B035-1143