Using fromsql Transform Routines | CREATE TRANSFORM | Teradata Vantage - Rules For fromsql Transform Routines - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

A fromsql routine transforms a UDT to a predefined data type.

The fromsql transforms are commonly used both utilities and by some commonly invoked SQL statements.

The following example shows an operation that causes implicit invocation of the fromsql transform routine.

Suppose a client application executes a SELECT statement whose column list contains a UDT column or expression. The following sequence of events occurs.

  1. The system implicitly invokes the fromsql transform routine of the defined transform group for that UDT to map the UDT value to a predefined data type value.
  2. The client application receives the predefined type value.

For example, consider the following example:

SELECT udt_column
FROM table1;

The fromsql transform routine converts the values of udt_column into the corresponding predefined data type values before returning them to the client application. If the UDT does not have a defined transform or if its transform group does not have a defined fromsql routine, the request aborts and the system returns an error to the requestor.

The rules for fromsql routines are as follows:
  • The fromsql transform routine can be either a method or a UDF.
  • The fromsql transform routine must be defined as DETERMINISTIC.
  • If the fromsql transform routine is a UDF, then function_name or specific_function_name must specify a UDF contained within the SYSUDTLIB database.
  • The fromsql transform routine must have the following declared parameter list and result data type:
    IF the fromsql transform routine is a … THEN the …
    UDF UDF must have one declared parameter whose type is the UDT.

    The RESULT data type must be a predefined data type.

    method method cannot have a declared parameter.

    In this case, the UDT instance is an implicit parameter.

    The RESULT data type must be a predefined data type.

  • If you specify both tosql and fromsql transform routines, then the declared parameter type of the tosql routine must be the same as the RESULT data type for the fromsql routine.