User Defined Method (UDM) (Teradata Database) - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.4
Published
July 2017
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
fcf1492702067123.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

This function is only available when connected to a Teradata system.

A User Defined Method or UDM is a special kind of User Defined Function that is associated with a User Defined Type or UDT. Invocation of a User Defined Method can be included in the generated SQL by using this element. SQL expressions can be passed as arguments to the method by dragging them into the Arguments folder underneath the UDM element in the tree.

An additional expression node underneath the Arguments folder should be used to contain the column or expression to apply the method to, unless the method is a ‘Constructor’, in which case it must be left empty. If used, the type of this column or expression must be the same User Defined Type that the method belongs to. It may be useful to note that ‘method chaining’ (i.e., something like “object.method().method()”) may be accomplished by placing a User Defined Method SQL element in this node, provided this method returns the User Defined Type of the UDM element above it in the tree.

In order to execute a User Defined Method, it must first have been compiled and installed in the Teradata RDBMS. Note, however, that many User Defined Methods and the User Defined Types they are associated with are provided by the Teradata RDBMS. For example, the majority of the geospatial functions available in Teradata are provided as methods of the User Defined Type “ST_Geometry”, beginning with Teradata Database 13.00.

When dragging a User Defined Method element into a variable, the following tree element is created.

Variable Creation > Input > Variables > SQL Elements Pane > Other > User Defined Method

Double-click on User Defined Method, or highlight it and click Properties.

Variable Creation > Input > Variables > SQL Elements Pane > Other > UDM Properties

The following is a description of the properties of a User Defined Method that may be set using the Properties dialog.
  • UDT Name — Name of the User Defined Type (UDT) that the User Defined Method belongs to. This field is initially set to the UDT associated with the UDT Expression field underneath the User Defined Method SQL tree element. If not preset, the desired value must be typed into this field.
  • Method Name — Name of the User Defined Method 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 identify the User Defined Methods available for the UDT specified above.

    It is not necessary to specify the database that contains the methods because they are always stored in the SYSUDTLIB database.

  • Description — If Teradata Help information is available for the requested User Defined Method, a description of the arguments and result type of the method are given in this field. In addition, if a Teradata Comment is available for the method, it is displayed here. Further, if the method is a constructor (i.e., builds a new instance of the User Defined Type) an indication is given here. Finally, for certain methods associated with the ST_Geometry User Defined Type, a note is given stating limitations in the objects that the method may operate on, such as points, lines, etc. Note also that similar limitations in the type of object or objects returned may be described next to the result type.
  • 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.

  • Number of Arguments — The number of arguments defined for the method 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.