16.20 - Related Topics - Advanced SQL Engine - Teradata Database
Teradata Vantage™ - SQL Functions, Expressions, and Predicates
Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2020-03-25
dita:mapPath
xzf1512079057909.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
kby1472250656485
Introduction to SQL Functions, Expressions, and Predicates
Overview
Changes and Additions
Functions, Operators, Expressions, and Predicates
SQL Functions
Types of SQL Functions
Examples of Functions
Embedded Services System Functions
Activating Embedded Services System Functions
Invoking Embedded Services System Functions
Implicit Data Type Conversion Rules
Related Topics
SQL Operators
SQL Expressions
Types of Expressions
Examples of Expressions
SQL Predicates
Types of Logical Predicates
Logical Operators that Operate on Predicates
Predicate Quantifiers
Examples of Predicates
Aggregate Functions
Overview
About 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
Traditional Aggregate Functions that Support Time Series
Related Topics
AVG
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Return Value
Computation of INTEGER or DECIMAL Values
Restrictions
Example: Using the AVG Function
AVG Window Function
Related Topics
CORR
Syntax Elements
value_expression_2/value_expression_1
ANSI Compliance
Definition
Computation
Result Type and Attributes
Support for UDTs
Combination With Other Functions
Example: Querying Data from the HomeSales Table
CORR Window Function
Related Topics
COUNT
Syntax Elements
ALL
DISTINCT
value_expression
*
Usage Notes
Result Type and Attributes
Examples: Using the COUNT Function
Related Topics
COVAR_POP
Syntax Elements
value_expression_1/value_expression_2
ANSI Compliance
Definition
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
COVAR_POP Window Function
COVAR_SAMP
Syntax Elements
value_expression_2/value_expression_1
ANSI Compliance
Definition
Combination with Other Functions
Computation
Result Type and Attributes
Support for UDTs
COVAR_SAMP Window Function
Example: Using the SELECT statement to Return the Sample Covariance of Weight and Height
GROUPING
Syntax Elements
expression
ANSI Compliance
Usage Notes
Result Type and Attributes
Result Value
Example: Viewing Sales Summaries by County and by City
Related Topics
KURTOSIS
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Definition
Return Value
Support for UDTs
Computation
Conditions That Produce a NULL Return Value
MAX
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Result Type and Attributes
Support for UDTs
Usage Notes
MAX Window Function
Examples: Using the MAX Function
MIN
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Result Type and Attributes
Support for UDTs
Usage Notes
MIN Window Function
Examples: Using the MINIMUM Function
PIVOT
Syntax Elements
aggr_fn
pvt_aggr_alias
expr_alias_name
cname
derived_table_name
expr
WITH
Usage Notes
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: The PIVOT Query Response in Different Response Modes
Example: Pivot Query Truncates the Alias Name
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
Related Topics
REGR_AVGX
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_AVGX Window Function
Example: Returning the Mean Height for regrtbl
REGR_AVGY
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_AVGY Window Function
Example: Returning the Mean Weight from regrtbl
Related Topics
REGR_COUNT
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Setting Up Axes for Plotting
Combination With Other Functions
Result Type and Attributes
Support for UDTs
REGR_COUNT Window Function
Example: Returning the Number of Rows in regrtbl
Related Topics
REGR_INTERCEPT
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Definition
Independent and Dependent Variables
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_INTERCEPT Window Function
Example: Returning the Intercept of the Regression Line for NbrSold and SalesPrice
Related Topics
REGR_R2
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_R2 Window Function
Example: Returning the Coefficient of Determination for Height and Weight
Related Topics
REGR_SLOPE
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Definition
Independent and Dependent Variables
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_SLOPE Window Function
Example: Returning the Slope of the Regression Line for NbrSold and SalesPrice
Related Topics
REGR_SXX
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_SXX Window Function
Example: Returning the Sum of Squares for Height
Related Topics
REGR_SXY
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_SXY Window Function
Example: Returning the Sum of Products of Height and Weight
Related Topics
REGR_SYY
Syntax Elements
dependent_variable_expression
independent_variable_expression
ANSI Compliance
Setting Up Axes for Plotting
Combination With Other Functions
Computation
Result Type and Attributes
Support for UDTs
REGR_SYY Window Function
Example: Returning the Sum of Squares for Weight
SKEW
Syntax Elements
ALL
DISTINCT
value_expression
Definition
Return Value
Computation
Conditions That Produce a Null Result
Related Topics
STDDEV_POP
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Definition
Combination With Other Functions
How GROUP BY Affects Report Breaks
Measuring the Standard Deviation of a Population
Computation
Return Values
STDDEV_POP Window Function
Related Topics
STDDEV_SAMP
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Definition
Computation
Return Values
Combination With Other Functions
How GROUP BY Affects Report Breaks
Measuring the Standard Deviation of a Population
STDDEV_SAMP Window Function
Related Topics
SUM
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Return Values
Usage Notes
Possible Result Overflow with SELECT Sum
Examples
Example: Accounts Receivable
Example: Face Value of Inventory
Related Topics
UNPIVOT
Syntax Elements
cname
literal
derived_table_name
Usage Notes
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
Related Topics
VAR_POP
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Definition
Computation
Return Value
Usage Notes
Combination With Other Functions
GROUP BY Affects Report Breaks
Measuring the Standard Deviation of a Population
Related Topics
VAR_SAMP
Syntax Elements
ALL
DISTINCT
value_expression
ANSI Compliance
Definition
Computation
Combination With Other Functions
GROUP BY Affects Report Breaks
Measuring the Variance of a Population
Return Value
Usage Notes
Related Topics
Arithmetic, Trigonometric, Hyperbolic Operators/Functions
Overview
ANSI Compliance
Arithmetic Operators and LOBs
Arithmetic Operators and UDTs
Related Topics
Binary Arithmetic Result Data Types
Result Data Type
DECIMAL Result Data Type
Numeric Results and Rounding
Error Conditions
Integer Division and Truncation
Structure of Arithmetic Expressions
Order of Evaluation
Format
Example: Determining Employee Salary Increases
Arithmetic Functions
ABS
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Examples: Using ABS Arithmetic Function Expressions
CASE_N
Syntax Elements
conditional_expression
NO CASE
OR UNKNOWN
UNKNOWN
ANSI Compliance
Evaluation
Result Type and Attributes
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
Restrictions
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
Related Topics
CEILING
ANSI Compliance
Invocation
Argument Types and Rules
Return Value
Result Type and Attributes
Examples
DEGREES RADIANS
Syntax Elements
arg
ANSI Compliance
Result Title
Result Type and Format
Argument Types and Rules
Usage Notes
Example: Representative DEGREES and RADIANS Function Expressions
EXP
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Usage Notes
Examples: Representative EXP Arithmetic Function Expressions
Related Topics
FLOOR
ANSI Compliance
Invocation
Argument Types and Rules
Return Value
Result Type and Attributes
Examples
Related Topics
HYPERBOLIC
ANSI Compliance
Result Type and Attributes
Result Value
Argument Types and Rules
Related Topics
LN Function
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Examples: Representative LN Arithmetic Function Expressions
Related Topics
LOG Function
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Example: Representative LOG Arithmetic Function Expressions
Related Topics
MOD
Syntax Elements
expr1
expr2
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Example: Using MOD Arithmetic Function Expression
Related Topic
NULLIFZERO
ANSI Compliance
Result Type and Attributes
Result Value
Argument Types and Rules
Examples
Related Topics
POWER
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples: Querying Returns for the Power of exponent_value
Related Topics
RANDOM
Syntax Elements
lower_bound
upper_bound
ANSI Compliance
Result Type and Attributes
Computation
Multiple RANDOM Calls Within a SELECT List
Restrictions
Using RANDOM as a Condition on an Index
Example: Returning Random Integer Numbers as Results
Related Topics
RANGE_N
Syntax Elements
test_expression
start_expression
*
end_expression
*
EACH range_size
NO RANGE
OR UNKNOWN
UNKNOWN
ANSI Compliance
Range Definition
Evaluation
Result Type and Attributes
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
Restrictions
Using a UDT as the Test Expression
Examples
Related Topics
ROUND
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Related Topics
SIGN
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Related Topics
SQRT
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Examples: Representative SQRT Arithmetic Function Expressions
Related Topics
TRIGONOMETRIC
Syntax Elements
arg
x
y
ANSI Compliance
Definitions
Result Type and Attributes
Result Value
Argument Types and Rules
Examples: Representative Function Expressions
Related Topics
TRUNC
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples: Query Returns for SELECT TRUNC
Related Topics
WIDTH BUCKET
Syntax Elements
value_expression
lower_bound
upper_bound
partition_count
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Rules
Example: Creating a Histogram for Employee Salaries within a Range
Related Topics
ZEROIFNULL
ANSI Compliance
Result Type and Attributes
Argument Types and Rules
Example: Testing the Salary Column for Null
Related Topics
Attribute Functions
Overview
ANSI Equivalence of Teradata Attribute Functions
BIT_LENGTH
ANSI Compliance
Argument Types
Example
Related Topics
BYTES
ANSI Compliance
Argument Types
Length Includes Trailing Zeros
Example: Using BYTE to Obtain the Number of Bytes in a Badge Picture
Related Topics
CHARACTER LENGTH
ANSI Compliance
Usage Notes
Argument Types
Result
Suppressing Trailing Pad Characters
Examples
Related Topics
DEFAULT
ANSI Compliance
Result Type and Attributes
Result Value
Omitting the Column Name
Using a Qualified Column Name
Restrictions
Error Conditions
Examples
Example: Inserting the Default Value Under Certain Conditions
Example: Using DEFAULT in a Predicate
Example: Specifying a View Column Name
Related Topics
FORMAT
ANSI Compliance
Result Type and Attributes
Example: Requesting the Format of the Salary Column
Related Topics
OCTET LENGTH
Syntax Elements
string_expression
character_set_name
ANSI Compliance
Argument Types
Usage Notes
Examples: Output from OCTET_LENGTH
Related Topics
TITLE
ANSI Compliance
Result Type and Attributes
Usage Notes
Example: Requesting the title of the Salary Column
Related Topics
TYPE
ANSI Compliance
Result Type and Attributes
Character Type Arguments
Examples
Related Topics
Bit/Byte Manipulation Functions
Overview
Prerequisites
Bit and Byte Numbering Model
BYTEINT
SMALLINT
INTEGER
BIGINT
BYTE and VARBYTE
HEXADECIMAL BYTE LITERALS
Performing Bit-Byte Operations against Arguments with Non-Equal Lengths
Example: Querying the BITAND Operation On An INTEGER
BITAND
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Examples: Querying with the BITAND Function
Related Topics
BITNOT
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the BITNOT Function
BITOR
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the BITOR Function
BITXOR
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the BITXOR Function
Related Topics
COUNTSET
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the COUNTSET Function
Related Topics
GETBIT
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the GETBIT Function
Related Topics
ROTATELEFT
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Examples
Example: Querying Input Argument 16 with the ROTATELEFT Function
Example: Querying Input Argument 64 with the ROTATELEDT Function
Related Topics
ROTATERIGHT
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Examples
Example: Querying Input Argument 32 with the ROTATERIGHT Function
Example: Querying Input Argument 4 with the ROTATERIGHT Function
Related Topics
SETBIT
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Examples
Example: Querying with the SETBIT Function
Example: Querying Input Argument 23 with the ROTATERIGHT Function
Related Topics
SHIFTLEFT
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the SHIFTLEFT Function
Related Topics
SHIFTRIGHT
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the SHIFTRIGHT Function
Related Topics
SUBBITSTR
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the SUBBITSTR Function
Related Topics
TO_BYTE
ANSI Compliance
Description
Argument Types and Rules
Result Type and Attributes
Example: Querying with the TO_BYTE Function
Related Topics
Built-In Functions
Overview
ACCOUNT
ANSI Compliance
Result Type and Attributes
Usage Notes
Example: Requesting Account Strings for a User
CURRENT_DATE/CURDATE
ANSI Compliance
Usage Notes
Result Type and Attributes
CURRENT_DATE versus DATE
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 the Default Output Format
Related Topics
CURRENT_ROLE
ANSI Compliance
Result Type and Attributes
Result Value
Usage Notes
Example: Selecting CURRENT_ROLE
CURRENT_TIME/CURTIME
ANSI Compliance
Usage Notes
Result Type and Attributes
Precision
CURRENT_TIME Fields
CURRENT_TIME versus TIME
Examples
Example: Requesting the Current Time
Example: Requesting the Current Time with a Time Zone String
Example: Changing the Default Output Format
Related Topics
CURRENT_TIMESTAMP
ANSI Compliance
Usage Notes
Result Type and Attributes
Precision
CURRENT_TIMESTAMP Fields
Examples
Example: Requesting the Current Timestamp
Example: CURRENT_TIMESTAMP and the TimeDateWZControl Flag
Example: Requesting the Current Timestamp with a Time Zone String
Example: Changing the Default Output Format
Related Topics
CURRENT_USER
ANSI Compliance
Result Type and Attributes
Result Value
Examples
Example: Identifying the Current User
Example: Selecting the Job Title for the Current User
DATABASE
ANSI Compliance
Result Type and Attributes
Usage Notes
Example: Requesting the Name of the Default Database
DATE
ANSI Compliance
Usage Notes
Result Type and Attributes
DATE versus CURRENT_DATE
Examples
Related Topics
NOW
ANSI Compliance
Result Type and Attributes
Example
Related Topics
PROFILE
ANSI Compliance
Result Type and Attributes
Example
ROLE
ANSI Compliance
Result Type and Attributes
Result Value
Session logon is not directory-based
Session logon is directory-based
Usage Notes
Example: Identifying the Session Current Role
SESSION
ANSI Compliance
Result Type and Attributes
Example: Identifying the Session Number for the Current User
TEMPORAL_DATE
Result Type and Attributes
Usage Notes
Restrictions
TEMPORAL_TIMESTAMP
Syntax Elements
precision
Result Type and Attributes
Usage Notes
Precision
Related Topics
TIME
ANSI Compliance
Usage Notes
Result Type and Attributes
TIME versus CURRENT_TIME
Examples
Related Topics
USER
ANSI Compliance
Result Type and Attributes
Result Value
Examples
Example: Identifying the User Name
Example: Selecting the User Job Title
Related Topics
Comparison Operators and Functions
Overview
Comparison Operators
Syntax Elements
operator
expression_1,
expression_2
quantifier
literal
subquery
scalar_expression
comparison_operator
Results
ANSI Compliance
Supported Comparison Operators
Comparison Operators Using Subqueries
Example: Using the ALL Quantifier to Compare Two Expressions
Related Topics
Comparisons That Produce TRUE Results
Conditions
Null Expressions
Floating Point Expressions
Related Topics
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
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
Related Topics
Comparison of KANJI1 Characters
Equality Comparison
Nonequality Comparison
Related Topics
Comparison Operators and the DEFAULT Function in Predicates
DECODE
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Example: Decoding IDs
Example: Decoding IDs Using NULL
Example: Decoding IDs When ID is Not Equal to 1, 2 or NULL
Related Topics
GREATEST
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Related Topics
LEAST
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Related Topics
CASE Expressions
Overview
CASE
ANSI Compliance
Overview
Two Forms of CASE Expressions
CASE Shorthands for Handling Nulls
Valued CASE Expression
Syntax Elements
value_expression_1
value_expression_n
scalar_expression_n
scalar_expression_m
ANSI Compliance
Usage Notes
Default Title
Restrictions on the Data Types in a CASE Expression
Examples
Example: Calculating the Fraction of Cost
Example: Using a CASE Expression
Example: Combining a CASE Expression with a Concatenation Operator
Example: Using UDT Data Types in Value Expressions
Example 1: Using UDT Data Types in Scalar Expressions
Related Topics
Searched CASE Expression
Syntax Elements
search_condition_n
scalar_expression_n
scalar_expression_m
ANSI Compliance
Usage Notes
Default Title
Rules for WHEN Search Conditions
Restrictions on the Data Types in a CASE Expression
Examples
Example: Evaluating a Search Condition
Example: Using a CASE Expression
Example: Using an ELSE Clause
Example: Using a CASE expression to Enhance Performance
Example: Producing a Report to Show Employee Salary
Related Topics
Error Conditions
Rules for the CASE Expression Result Type
THEN/ELSE Expressions Having the Same Non-Character Data Type
THEN/ELSE Character Type Expressions
Examples
Examples of Character Data in a CASE Expression
Examples of Character Data in a CASE Expression: Example 1
Examples of Character Data in a CASE Expression: Example 2
Examples of Character Data in a CASE Expression: Example 3
Examples of Character Data in a CASE Expression: Example 4
THEN/ELSE Expressions Having Mixed Data Types
Examples of Numeric Data in a CASE Expression
Example: CASE Expression Fails
Example: Shortening the Scale of the Multiplier
Example: Returning a DECIMAL(38,2) Result
Examples of Character and Numeric Data in a CASE Expression
Examples of Character and Numeric Data in a CASE Expression: Example 1
Examples of Character and Numeric Data in a CASE Expression: Example 2
Related Topics
Format for a CASE Expression
Default Format
Using Explicit Type Conversion to Change Format
CASE and Nulls
CASE Shorthands
COALESCE Expression
Syntax Elements
scalar_expression_n
ANSI Compliance
Usage Notes
Default Title
Restrictions on the Data Types in a COALESCE Expression
Examples
Example: Querying for a Phone Number
Example: Using COALESCE with an Arithmetic Operator
Example: Using COALESCE with an Comparison Operator
Related Topics
NULLIF Expression
Syntax Elements
scalar_expression_1
scalar_expression_2
ANSI Compliance
Usage Notes
Default Title
Restrictions on the Data Types in a NULLIF Expression
Examples
Related Topics
Hash-Related Functions
Overview
HASHAMP
Syntax Elements
expression
MAP
Contiguousmap_name
Sparsemap_name
COLOCATE USING
Colocation_name
ANSI Compliance
Usage Notes
Examples
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
Related Topics
HASHBAKAMP
Syntax Elements
expression
MAP
Contiguousmap_name
Sparsemap_name
COLOCATE USING
Colocation_name
ANSI Compliance
Usage Notes
Examples
Example Assumptions
Example: Distributing the Hash Buckets Among the Fallback AMPs
Example: Which Fallback AMPs Contain the Rows of a Table
Related Topics
HASHBUCKET
Syntax Elements
expression
ANSI Compliance
Result
Using HASHBUCKET to Convert a BYTE Type to an INTEGER Type
Examples
***
Example
Example
Example
Example
Example
HASHROW
Syntax Elements
expression
ANSI Compliance
Result
Usage Notes
Examples
Example
Example
Example
Related Topics
Logical Predicates
Overview
Related Topics
About Logical Predicates
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
Where To Use Search Conditions
Rules for Order of Evaluation
Evaluation Results
AND Truth Table
OR Truth Table
NOT Truth Table
Subquery Restrictions
Examples of Logical Operators in Search Conditions
ANY / ALL / SOME Quantifiers
Syntax Elements
expression
comparison_operator
[NOT] IN
literal
subquery
ANSI Compliance
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
Examples
Example: ANY Quantifier
Example: ALL Quantifier
Example: ANY/ALL/SOME
BETWEEN / NOT BETWEEN
ANSI Compliance
Usage Notes
Example
EXISTS / NOT EXISTS
Syntax Elements
subquery
ANSI Compliance
Usage Notes
Subquery Restrictions
Relationship Between EXISTS/NOT EXISTS and IN/NOT IN
Example
EXISTS Predicate Versus NOT IN and Nulls
For ALL
NOT EXISTS Clauses and Stored Procedures
NOT EXISTS and Recursive Queries
Examples
Example: EXISTS with Correlated Subqueries
Example: NOT EXISTS with Correlated Subqueries
Related Topics
IN/NOT IN
ANSI Compliance
Syntax Elements
expression_1
IN
expression_2
literal
signed_literal_1 TO signed_literal_2
datetime_literal
Result
Usage Notes
Relationship Between IN/NOT IN and EXISTS/NOT EXISTS
Equivalences Using IN/NOT IN, NOT, and ANY/ALL/SOME
Syntax Elements
expression
subquery
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 Can Fail
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
Syntax Elements
expression
ANSI Compliance
Examples
Example
Example
Example: Searching for NULL and NOT-NULL in the Same Statement
Example: Searching a Table That Might Contain Nulls
LIKE
Syntax Elements
expression
pattern_expression
ANY
ALL
SOME
subquery
ESCAPE escape_character
ANSI Compliance
Optimized Performance Using a NUSI
Null Expressions
Case Specification
Wildcard Characters
ESCAPE Feature of LIKE
Example
Pad Characters
Examples
Example: LIKE Predicate
Example: Last Name Spelling
Example: % and _ Characters
Example: Pad Characters and Letter
ANY/ALL/SOME Quantifiers
Example
subquery
Behavior of the ESCAPE Character
Examples
Miscellaneous Examples
KanjiEBCDIC Examples
KanjiEUC Examples
KanjiShift-JIS Examples
OVERLAPS
Null-Handling Functions
Overview
NVL
Invocation
Argument Types and Rules
Result Type
Example
Related Topics
NVL2
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Related Topics
Ordered Analytical/Window Aggregate Functions
Overview
Ordered Analytical Functions
Benefits
Ordered Analytical Calculations at the SQL Level
Teradata Warehouse Miner
Example
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 Hash or 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 QUALIFY With RANK
Example: Using QUALIFY With RANK
Related Topics
The Window Feature
PARTITION BY Phrase
ORDER BY Phrase
RESET WHEN Phrase
RESET WHEN Condition Rules
Rules For Using a DEFAULT Function As Part of a RESET WHEN Condition
Examples
Example
Example
Example
Example
ROWS Phrase
Multiple Window Specifications
Related Topics
Window Aggregate Functions
The Window Specification
Syntax Elements
ASC
OVER
PARTITION BY
ORDER BY
DESC
NULLS FIRST
NULLS LAST
RESET WHEN
condition
ROWS
ROWS BETWEEN
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
value PRECEDING
value FOLLOWING
ANSI Compliance
Type of Computation
Arguments to Window Aggregate Functions
Result Type and Format
Result Title
Problems with Missing Data
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 Result Overflow with SELECT Sum
Related Topics
CSUM
Syntax Elements
value_expression
sort_expression
ASC
DESC
ANSI Compliance
Using SUM Instead of CSUM
Meaning of Cumulative Sums
Possible Result Overflow with SELECT Sum
Result Type and Attributes
Examples
Example
Example
CUME_DIST
Syntax Elements
OVER
PARTITION BY
ORDER BY
ASC
DESC
NULLS FIRST
NULLS LAST
RESET WHEN
condition
ANSI Compliance
Using CUME_DIST
Results
Example
DENSE_RANK (ANSI)
Syntax Elements
OVER
PARTITION BY
ORDER BY
ASC
DESC
NULLS FIRST
NULLS LAST
RESET WHEN
condition
ANSI Compliance
Using DENSE_RANK
Result Type
Example
FIRST_VALUE / LAST_VALUE
Syntax Elements
value_expression
IGNORE NULLS
RESPECT NULLS
window
ANSI Compliance
Usage Notes
Example
LAG/LEAD
Syntax Elements
value_expression
offset_value
default_value_expression
RESPECT NULLS
IGNORE NULLS
OVER
PARTITION BY
ORDER BY
ASC
DESC
NULLS FIRST
NULLS LAST
RESET WHEN
ANSI Compliance
Result Type
Usage Notes
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
Syntax Elements
value_expression
width
sort_expression
ASC
DESC
ANSI Compliance
Using AVG Instead of MAVG
Result Type and Attributes
Problems With Missing Data
Computing the Moving Average When Number of Rows < width
Examples
Example
Example
MDIFF
Syntax Elements
value_expression
width
sort_expression
ASC
DESC
ANSI Compliance
Meaning of Moving Difference
Using SUM Instead of MDIFF
Result Type and Attributes
Problems With Missing Data
Computing the Moving Difference When No Preceding Row Exists
Examples
Example
Example
Related Topics
MEDIAN
Syntax Elements
value_expression
ANSI Compliance
Result
Example
Related Topics
MLINREG
Syntax Elements
value_expression
width
sort_expression
ASC
DESC
ANSI Compliance
Using ANSI-Compliant Window Functions Instead of MLINREG
Result Type and Attributes
Default Independent Variable
Computing MLINREG When Preceding Rows < width - 1
MLINREG Report Structure
Example
Related Topics
MSUM
Syntax Elements
value_expression
width
sort_expression
ASC
DESC
ANSI Compliance
Using SUM Instead of MSUM
Result Type and Attributes
Problems With Missing Data
Computing MSUM When Number of Rows < width
Possible Result Overflow with SELECT Sum
PERCENT_RANK
Syntax Elements
OVER
PARTITION BY
ORDER BY
ASC
DESC
NULLS FIRST
NULLS LAST
RESET WHEN
condition
ANSI Compliance
Computation
Result Type and Attributes
Examples
Example: Relative Rank
Example: Rank and Relative Rank
Example: PERCENT_RANK and CUM_DIST
PERCENTILE_CONT / PERCENTILE_DISC
Syntax Elements
value_expression_1
WITHIN GROUP
ORDER BY
value_expression_2
ASC
DESC
NULLS FIRST
NULLS LAST
ANSI Compliance
Result
Usage Notes
Example
QUANTILE
Syntax Elements
quantile_literal
sort_expression
ASC
DESC
ANSI Compliance
Definition
Using ANSI Window Functions Instead of QUANTILE
QUANTILE Report
Quantile Value Range
Result Type and Attributes
Examples
Example
Example
Example
Related Topics
RANK (ANSI)
Syntax Elements
OVER
PARTITION BY
ORDER BY
ASC
DESC
NULLS FIRST
NULLS LAST
RESET WHEN
condition
TIES LOW
TIES HIGH
TIES AVG
TIES DENSE
ANSI Compliance
Meaning of Rank
Result Type and Attributes
Examples
Example: Ranking Salespeople Based on Sales
Example: Finding Differences Between RANK(ANSI) and DENSE_ RANK(ANSI)
Related Topics
RANK (Teradata)
Syntax Elements
sort_expression
ASC
DESC
ANSI Compliance
Using ANSI RANK Instead of Teradata RANK
Meaning of Rank
Computing Top and Bottom Values
Result Type and Attributes
Examples
Example
Example
Example
Related Topics
ROW_NUMBER
Syntax Elements
OVER
PARTITION BY
ORDER BY
ASC
DESC
NULLS FIRST
NULLS LAST
RESET WHEN
condition
ANSI Compliance
Window Aggregate Equivalent
Example
Related Topics
Regular Expression Functions
Overview
Pattern-Matching Limitation
REGEXP_SUBSTR
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Related Topics
REGEXP_REPLACE
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Example
Related Topics
REGEXP_INSTR
Syntax Elements
TD_SYSFNLIB
source_string
regexp_string
position_arg
occurrence_arg
return_opt
match_arg
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Related Topics
REGEXP_SIMILAR
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
Related Topics
REGEXP_SPLIT_TO_TABLE
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
Result Type
String Operators and Functions
Overview
About 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 vs. Physical Characters
Untranslatable KANJI1 Characters
Implicit Server Character Set Translation
Concatenation Operator
Syntax Elements
string_expression_1
ANSI Compliance
Argument Types and Rules
Result Type and Attributes
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
Examples for Japanese Character Sets
Example: KanjiEBCDIC
Example: KanjiEUC
Example: KanjiShift-JIS
Concatenating Character Strings Having Different Server Character Sets
Related Topics
ASCII
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
Related Topics
CHAR2HEXINT
Syntax Elements
character_string_expression
Argument Types
Result Type and Attributes
CHAR2HEXINT and Literal Strings
Examples
Example
Example
Related Topics
CHR
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
Related Topics
CONCAT
Syntax Elements
string_expression_1
string_expression_2
ANSI Compliance
Result Type and Attributes
Argument Type and Rules
Example
Related Topic
CSV
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Related Topics
CSVLD
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
Related Topics
EDITDISTANCE
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Example
Example
Example
Example
Related Topics
INDEX
Syntax Elements
string_expression_1
string_expression_2
ANSI Compliance
Argument Types and Rules
Result Type and Attributes
Expected Values
Rules for Character Type Arguments
Rules for KANJI1 Server Character Set
Relationship Between INDEX and POSITION
Examples
Example: Using Simple INDEX Expressions
Example
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
Related Topics
INITCAP
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
Related Topics
INSTR
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Example
Example
Related Topics
LEFT
Usage Notes
Result Type
Example
LENGTH
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
Related Topics
LOCATE
Syntax Elements
string_expr1
string_expr2
n1
ANSI Compliance
Argument Types and Rules
Result Type and Attributes
Example
LOWER
Syntax Elements
character_string_expression
ANSI Compliance
Argument Types
Result Type and Attributes
Usage Notes
Example
Related Topics
LPAD
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Example
Related Topics
LTRIM
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Related Topics
NGRAM
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Example
Example
Example
Example
Example
Example
Example
Example
Example
Related Topics
NVP
Syntax Elements
TD_SYSFNLIB
instring
name_to_search
name_delimiters
value_delimiters
occurrence
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example: Querying for Entree
Example: Querying for Second Occurrence of 'store'
Example: Querying for Entree (Default Value)
Example: Querying for Entree with 1 Occurrence
Related Topics
OREPLACE
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Example
Example
Example
Example
Example
Related Topics
OTRANSLATE
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Examples
Example: Returning the Current Database Version
Example: Removing Extra Characters from the Query Results
Example: Replacing and Returning Query Characters
Example: Removing Characters without Replacing Them
Related Topics
POSITION
Syntax Elements
string_expression_1
string_expression_2
ANSI Compliance
Argument Types and Rules
Result Type and Attributes
Expected Values
How POSITION and INDEX Differ
Related Topics
REVERSE
Syntax Elements
TD_SYSFNLIB
source_string
Usage Notes
Result Type
Example
RIGHT
Syntax Elements
TD_SYSFNLIB
source_string
length
Usage Notes
Result Type
Example
RPAD
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Example
Related Topics
RTRIM
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Examples
Example
Example
Related Topics
SOUNDEX
Syntax Elements
string_expression
ANSI Compliance
Argument Types
Definition: Simple Latin Characters
Definition: Soundex
Soundex Coding Guide
Examples
Example
Example
Example
Examples of Non Valid Usage
Related Topics
STRING_CS
Syntax Elements
string_expression
ANSI Compliance
Argument Types
Result Value
Usage Notes
Examples
Example: Using STRING_CS to Determine the Client Character Set
Example: Using STRING_CS to Translate a KANJI1 String to UNICODE
Related Topics
STRTOK
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Usage Notes
Example: Using STRTOK
STRTOK_SPLIT_TO_TABLE
ANSI Compliance
Invocation
Argument Types and Rules
Result Type
Example
SUBSTRING/SUBSTR
Syntax Elements
string_expression
n1
FOR
n2
string_expression
n1
n2
ANSI Compliance
Argument Types and Rules
Result Type and Attributes
Result Value
Usage Rules for SUBSTRING and SUBSTR
Difference Between SUBSTRING and SUBSTR
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
Related Topics
TRANSLATE
Syntax Elements
character_string_expression
source_repertoire_name
_encoding
target_repertoire_name
_suffix
WITH ERROR
Argument Types
Result Type and Attributes
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
Related Topics
TRANSLATE_CHK
Syntax Elements
character_string_expression
source_repertoire_name
_encoding
target_repertoire_name
_suffix
ANSI Compliance
Argument Types
Result Type and Attributes
Result Values
Examples
Example
Example
Example
Example
Checking UNICODE Normalization Form Translations
Related Topics
TRIM
Syntax Elements
BOTH
TRAILING
LEADING
trim_expression
FROM
character_set
string_expression
ANSI Compliance
Argument Types and Rules
Result Type and Attributes
Concatenation With TRIM
Examples
Example
Example
Example
Example: Using TRIM Functions
Related Topics
UPPER/UCASE
Syntax Elements
character_string_expression
ANSI Compliance
Argument Types
Result Type and Attributes
Usage Notes
Restrictions
Examples
Example: Using a Table Definition with CASESPECIFIC Attributes
Example: Using UPPER to Store Values
Example: Converting Single Byte Characters to Uppercase
Related Topics
VARGRAPHIC
Syntax Elements
character_string_expression
ANSI Compliance
Argument Types
Result Type and Attributes
Rules
Examples
Example
Example
Related Topics
VARGRAPHIC Function Conversion Tables
User-Defined Functions
SQL UDF
Syntax Elements
udf_name
argument
ANSI Compliance
Restrictions
Authorization
Usage Notes
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
Related Topics
Elastic TCore Functions
GetSystemTCore
SetSystemTCore
Notation Conventions
About Notation Conventions
Syntax Diagram Conventions
Character Shorthand Notation Used in This Document
Additional Information
Teradata Links
For more information, see “DBS Control (dbscontrol)” in
Teradata Vantage™ - Database Utilities
, B035-1102
.
For information about how TIME data is stored internally in UTC, see “ORDER BY Clause” in
Teradata Vantage™ - SQL Data Manipulation Language
, B035-1146
.
For information about time zone strings, see
Teradata Vantage™ Data Types and Literals , B035-1143 .
For information about TIME versus CURRENT_TIME, see CURRENT_TIME/CURTIME .