15.00 - SQL Functions - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

SQL Functions

SQL functions return information about some aspect of the database, depending on the arguments specified at the time the function is invoked.

Functions provide a single result by accepting input arguments and returning an output value. Some SQL functions, referred to as niladic functions, do not have arguments, but they do return values. An example of a niladic SQL function is CURRENT_DATE.

Types of SQL Functions

 

Function Type

Definition

Scalar

The arguments are individual scalar values of either the same or mixed type that can have different meanings.

The result is a single value or null.

Scalar functions can be used in any SQL statement where an expression can be used.

Aggregate

The argument is a group of rows.

The result is a single value or null.

Normally aggregate functions are used in the expression list of a SELECT statement and in the summary list of a WITH clause.

Table

The arguments are individual scalar values of either same or mixed type that can have different meanings.

The result is a table.

Table functions can be used only within the FROM clause of a SELECT statement.

Table functions are a form of user-defined functions and are described in SQL External Routine Programming.

Ordered Analytical

The arguments are any normal SQL expression.

The result is handled the same way as any other SQL expression. It can be a result column or part of a more complex arithmetic expression.

Ordered analytical functions are used in operations that require an ordered set of results rows or that depend on values in a previous row.

Examples of Functions

 

Function

Description

SELECT CHARACTER_LENGTH(Details)
FROM Orders;

Scalar function taking the character or CLOB value in the Details column and returning a numeric value for each row in the Orders table.

SELECT AVG(Salary)
FROM Employee;

Aggregate function returning a single numeric value for the group of numeric values specified by the Salary column in the Employee table.

For examples of table functions, see SQL External Routine Programming.

Embedded Services System Functions

Teradata Database provides a set of system functions that support a range of functionality such as string handling, DateTime operations, byte/bit manipulation, and more.

Activating Embedded Services System Functions

Before you can use the embedded services functions, you must run the Database Initialization Program (DIP) utility and execute the DIPALL or DIPSYSFNC script. DIPALL is executed as part of system installation.

The DIP scripts create the TD_SYSFNLIB database which should be used only by the system to support the embedded services functions. Do not store any database objects in this database. Doing so may interfere with the proper operation of the embedded services functions.

If you perform a BAR operation that involves the TD_SYSFNLIB database or the DBC dictionary tables, you must re-execute the DIPALL or the DIPSYSFNC script to reactivate the embedded services functions.

Invoking Embedded Services System Functions

You can invoke an embedded services function using the function name alone. For example, CEILING (arg).

You can also qualify the function name by adding the TD_SYSFNLIB database name. For example, you can invoke the CEILING function using the fully qualified syntax, TD_SYSFNLIB.CEILING(arg).

Note: If you try to invoke an embedded services function using the function name alone, but you also have a customer-developed UDF with the same name in the current database or in the SYSLIB database, Teradata Database will execute the customer-developed UDF instead of the embedded services function.

Therefore, to ensure that you are invoking the embedded services function, do one of the following:

  • Remove any customer-developed UDFs with the same name from the normal UDF search path; that is, from the current database and from the SYSLIB database. For detailed information, see “Locations Where Teradata Database Looks for Functions” in SQL External Routine Programming.
  • Use the fully qualified syntax to invoke the embedded services function. For example, TD_SYSFNLIB.embedded_services_function.
  • Implicit Data Type Conversion Rules

    Embedded services functions follow the implicit data type conversion rules that apply to UDFs. The UDF implicit type conversion rules are more restrictive than the implicit type conversion Teradata Database normally uses. If a function argument cannot be converted to the required data type by following the UDF implicit conversion rules, it must be explicitly cast. For details, see “Compatible Types” in SQL External Routine Programming.