Related Information - Advanced SQL Engine - Teradata Database
SQL Operators and User-Defined Functions
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
fsi1592016213432.ditamap
dita:ditavalPath
fsi1592016213432.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™
Introduction to SQL Operators and User-Defined Functions
Changes and Additions
Compression/Decompression Functions
CAMSET
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 Information
CAMSET_L
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 Information
DECAMSET
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Unicode Values
Related Information
DECAMSET_L
Argument Type and Rules
Result Type
Usage Notes
Example: DECAMSET_L Function
Related Information
JSON_COMPRESS
Argument Type and Rules
Result Type
Example: Compressing JSON Data Types
Related Information
JSON_DECOMPRESS
Argument Type and Rules
Result Type
Example: Decompressing JSON Data Types
Related Information
LZCOMP
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 Information
LZCOMP_L
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 Information
LZDECOMP
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Unicode Values with LZCOMP
Related Information
LZDECOMP_L
Argument Type and Rules
Result Type
Usage Notes
Example: Compressing Latin Values with LZCOMP_L
Related Information
TD_LZ_COMPRESS
Argument Type and Rules
Result Type
Usage Notes
Uncompressing Data Compressed with TD_LZ_COMPRESS
Related Information
TD_LZ_DECOMPRESS
Argument Type and Rules
Result Type
Usage Notes
Related Information
TS_COMPRESS
Argument Type and Rules
Result Type
Usage Notes
Examples
Example: Compressing the TIME Data Type
Example: Compressing the TIMESTAMP Data Type
Related Information
TS_DECOMPRESS
Argument Type and Rules
Result Type
Example: Creating a Compressed TIME Column
Related Information
TransUnicodeToUTF8
Argument Type and Rules
Result Type
Usage Notes
Restrictions
Example: Uncompressing Data Compressed with TransUnicodeToUTF8
Related Information
TransUTF8ToUnicode
Argument Type and Rules
Result Type
Usage Notes
Example: Uncompressing Unicode Values with TransUTF8ToUnicode
Related Information
Export Width Procedures
Using Export Width
ReplaceExportDefinition
Argument Types
Usage Notes
Export Width Rules
Example: ReplaceExportDefinition
RemoveExportDefinition
Argument Types
Usage Notes
Example: RemoveExportDefinition
File System Information Macros and Functions
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
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
AlterFsysInfoTable_TD16/AlterFsysInfoTable_ANSI_TD16
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
Returned Columns
Usage Notes
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
Related Information
Map Functions, Macros, and Procedures
Map Functions
SYSLIB.ContiguousMapAMPs
Usage Notes
Example: Identifying Which AMPs are in a Contiguous Map
SYSLIB.SparseMapAMPs
Usage Notes
Example: Identifying an AMP with a Specific Table
SYSLIB.SparseTableAMPs
Usage Notes
Example: Identifying AMPs for a Table with a Sparse Map
Map Macros
Map Procedures
AdjustSpace
Argument Types
Result Type
Usage Notes
Examples
Example: Adjusting the Space Settings After a System Expansion
Example: Using AdjustSpace
Advisor Procedures
AddExclusionListEntrySP(X)
Argument Type
Usage Notes
Examples
Example: Creating an Exclusion List Named ‘MyExclusions’
Example: Analyzing Exclusions Defined in 'MyExclusions'
AddMapListEntrySP(X)
Argument Types
Usage Notes
Example: Calling AddMapListEntrySP(X)
AddObjectListEntrySP(X)
Argument Types
Usage Notes
Examples
Example: Creating an Object List
Example: Analyzing Objects Defined in 'MyObjects'
Example: Creating Object List ‘ObjNotInMap2’
AnalyzeSP
Argument Types
Result Type
Usage Notes
Example: Using AnalyzeSP
CleanUpAnalyzerSP
Argument Types
Example: Calling CleanUpAnalyzerSP
CreateExclusionListSP
Argument Types
Result Type
Usage Notes
Example: Calling CreateExclusionListSP
CreateExpansionMaps
Example: Using CreateExpansionMaps
CreateMapListSP
Argument Types
Result Type
Usage Notes
Example: Calling CreateMapListSP
CreateObjectListSP
Argument Types
Result Type
Usage Notes
Example: Calling CreateObjectListSP
MonitorAnalyzeSP
Argument Types
Result Types
Example: Calling MonitorAnalyzeSP
PostExpansionAction
Example: Using PostExpansionAction
Mover Procedures
ManageMoveTablesSP
Argument Types
Result Type
Usage Notes
Example: Executing the Actions in the 'MyNewMapActions' List
MoveTablesSP
Argument Types
Usage Notes
Examples
Example: Using BTEQ to Execute Queued Actions
Example: Move a Table to a Map
MonitorMoveTablesSP
Argument Types
Result Type
Example: Using MonitorMoveTablesSP
StopMoveTablesSP
Argument Types
Result Type
Usage Notes
Example: Using StopMoveTablesSP
StopMoverSP
Argument Types
Example: Using StopMoverSP
StopSerialWorkerSP
Example: Using StopSerialWorkerSP
CleanUpMoveTablesSP
Usage Notes
Example: Calling CleanUpMoveTablesSP
MoveTDMapsTablesSP
Argument Types
Result Type
Example: Calling MoveTDMapsTablesSP
TruncateHistorySP
Argument Types
Result Type
Usage Notes
Example: Calling TruncateHistorySP
Table Operators
CALCMATRIX
Data Type
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 Information
Cogroups
Example: Cogroup Used for a Table Operator with Multiple Inputs
Related Information
FeatureNames_TBF
Columns
Example: Generating a Report on Feature Usage
R Table Operator
Installation of R Components and Packages
Using ExecR to Run R Scripts and Table Operators
Related Information
READ_NOS
READ_NOS Syntax
READ_NOS Syntax Elements
Setting Up an Object Store 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 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 CSV File
Example: Using READ_NOS to Query a File in Google Cloud Storage
SCRIPT
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 Information
TD_DBQLParam
Output
Usage Notes
Example: TD_DBQLParam
TD_DBQLFUL
Output
Usage Notes
Examples
TD_UNPIVOT
Data Type
Examples
Example: Twelve Columns Unpivoted to One
Example: More Than One Value Column
Usage Notes for Examples
WRITE_NOS
WRITE_NOS Syntax
WRITE_NOS Examples
User-Defined Functions
Scalar UDF
Restrictions
UDF Arguments
Result Data Type
Default Title
Examples
Example 1
Example 2
Related Information
Aggregate UDF
Restrictions
Usage Notes
Example: Aggregate UDF
Related Information
Window Aggregate UDF
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: Window Aggregate
Related Information
User-Defined Functions
Restrictions
Usage Notes
Related Information
UDF Invocation
User-Defined Type Expressions/Methods
UDT Expression
Usage Notes
Example: Methods and Functions
Examples
Example: Column Name
Example: CAST
Example: NEW
Related Information
NEW
Usage Notes
Default Constructor
Determining Which Constructor is Invoked
Examples
Example 1
Example 2
Related Information
NEW JSON
Related Information
NEW VARIANT_TYPE
Restrictions
Usage Notes
Examples
Example 1
Example 2
Related Information
NEW XML
Related Information
Method Invocation
Observer and Mutator Methods
Usage Notes
Restrictions
Example: UDT Expression Method
Related Information
Script Installation Procedures
Execution Rules
Installing and Registering External Language Scripts
SYSUIF.INSTALL_FILE
Example: Calling SYSUIF.INSTALL_FILE to Install a Python Script
Related Information
Replacing External Language Scripts
Before You Begin
SYSUIF.REPLACE_FILE
Example: Replacing an Existing File Mapper
Redistributing External Language Scripts
Before You Begin
SYSUIF.REDISTRIBUTE_FILE
Example: Redistributing External Language Scripts Argument
Displaying the User-Installed File
Related Information
Removing External Language Scripts
Before You Begin
SYSUIF.REMOVE_FILE
Example: Removing External Language Script
Notation Conventions
How to Read Syntax
Character Shorthand Notation Used in This Document
Additional Information
Teradata Links
For more information on dynamic UDTs, see VARIANT_TYPE in Teradata Vantage™ - Data Types and Literals , B035-1143
For information on constructing a new instance of a structured UDT that is not a dynamic UDT, see NEW .
For information on writing UDFs which use input parameters of VARIANT_TYPE data type, see Teradata Vantage™ - SQL External Routine Programming , B035-1147