Invocation of a User-Defined Function can be included in the generated SQL by using this element. SQL expressions can be passed as arguments to the function by dragging them into the Arguments folder underneath it in the tree.
When dragging a User-Defined Function element into a variable, the following tree element is created.
Double-click on User Defined Function, or highlight it and click Properties.
-
Database Name — Name of the database where the User Defined Function is installed. The desired value may either be typed into this field or selected from the pull-down list.The pull-down list for this field contains all of the Source Databases defined on the Connection Properties dialog (accessed from the Tools menu of the main application). Values may be added or removed there.
- Function Name — Name of the User Defined Function to be invoked. The desired value may either be typed into this field or selected from the pull-down list. The pull-down list is populated by issuing the appropriate Teradata Help command to locate User Defined Functions in the database specified above.
- Description — If Teradata Help information is available for the requested User Defined Function, a description of the arguments and result type of the function are given in this field. In addition, if a Teradata Comment is available for the function, it is displayed here.
-
Return-Type — A Return-Type clause may be specified by the user if and only if the routine has a return type of TD_ANYTYPE, as displayed in the Description text box. This clause is required if the routine has a return type of TD_ANYTYPE and does not contain an input parameter of type TD_ANYTYPE. Otherwise, the data type of the first TD_ANYTYPE input column is used in the absence of the Returns clause. The Return-Type clause may take one of two forms:
- <data type>
- STYLE <column expression>
An example of the first form is “INTEGER”, and of the second form, “STYLE table1.columnA”. Note that parentheses are automatically added around the routine invocation when either phrase is specified, as required by the Teradata SQL syntax.
- Aggregation Function — This box will be checked if the function is an aggregation function and Teradata Help information is available. Otherwise, this information should be provided by the user.
-
Number of Arguments — The number of arguments defined for the function is displayed here if Teradata Help information is available. Otherwise, this information should be provided by the user.
If the Arguments folder contains fewer arguments than is specified here, the unspecified arguments will be set to NULL.
- Omit Database In Call — When checked, this option causes the function name to appear without the containing database name preceding it in the SQL generated for this function. In this case, upon invocation of the function in generated SQL, the database software will first search for the function in the current database (either in the default database of the data source connection or the last issued DATABASE command) and, if necessary, in standard system databases (for example, SYSLIB, TD_SYSFNLIB and SYSUDTLIB).