CREATE/REPLACE TRANSFORM Examples | Teradata Vantage - CREATE TRANSFORM and REPLACE TRANSFORM Examples - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

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
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

Example: Creating a Transform Request

The following CREATE TRANSFORM request creates the transform group named client_IO, which is composed of the tosql transform stringToAddress and the fromsql transform toString for the UDT named address.

CREATE TRANSFORM FOR address client_IO (
  TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress,
  FROM SQL WITH SPECIFIC METHOD toString);

Example: Creating Multiple Transform Groups

Create the functions for the FROM SQL WITH and TO SQL WITH clauses. This example uses functions for integer, character, and binary values.

CREATE FUNCTION SYSUDTLIB.xmld_struct2int_fromsql(xmld_struct2)
RETURNS INTEGER
NO SQL
CALLED ON NULL INPUT
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!xmld_struct2int_fromsql!xmld_struct2int_fromsql.c!F!xmld_struct2int_fromsql';
CREATE FUNCTION SYSUDTLIB.xmld_struct2int_tosql(INTEGER)
RETURNS xmld_struct2
NO SQL
PARAMETER STYLE TD_GENERAL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!xmld_struct2int_tosql!xmld_struct2int_tosql.c!F!xmld_struct2int_tosql';
CREATE FUNCTION SYSUDTLIB.xmld_struct2char_fromsql(xmld_struct2)
RETURNS CHAR
NO SQL
CALLED ON NULL INPUT
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!xmld_struct2char_fromsql!xmld_struct2char_fromsql.c!F!xmld_struct2char_fromsql';
CREATE FUNCTION SYSUDTLIB.xmld_struct2char_tosql(CHAR)
RETURNS xmld_struct2
NO SQL
PARAMETER STYLE TD_GENERAL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!xmld_struct2char_tosql!xmld_struct2char_tosql.c!F!xmld_struct2char_tosql';
CREATE FUNCTION SYSUDTLIB.xmld_struct2byte_fromsql(xmld_struct2)
RETURNS BYTE
NO SQL
CALLED ON NULL INPUT
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!xmld_struct2byte_fromsql!xmld_struct2byte_fromsql.c!F!xmld_struct2byte_fromsql';
CREATE FUNCTION SYSUDTLIB.xmld_struct2byte_tosql(BYTE)
RETURNS xmld_struct2
NO SQL
PARAMETER STYLE TD_GENERAL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!xmld_struct2byte_tosql!xmld_struct2byte_tosql.c!F!xmld_struct2byte_tosql';
This statement creates three transform groups:
CREATE TRANSFORM FOR XMLD_STRUCT2
XMLD_STRUCT2INT (TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.XMLD_STRUCT2INT_TOSQL,
                 FROM SQL WITH SPECIFIC FUNCTION SYSUDTLIB.XMLD_STRUCT2INT_FROMSQL)
XMLD_STRUCT2CHAR(TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.XMLD_STRUCT2CHAR_TOSQL,
                 FROM SQL WITH SPECIFIC FUNCTION SYSUDTLIB.XMLD_STRUCT2CHAR_FROMSQL)
XMLD_STRUCT2BYTE(TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.XMLD_STRUCT2BYTE_TOSQL,
                 FROM SQL WITH SPECIFIC FUNCTION SYSUDTLIB.XMLD_STRUCT2BYTE_FROMSQL);