Using fromsql Transform Routines | CREATE TRANSFORM | Teradata Vantage - Rules For fromsql Transform Routines - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
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.