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:
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.