Sample Queries - Analytics Database - Teradata Vantage

SQL External Routine Programming

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
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

Consider the following table that defines a circle_t column:

CREATE TABLE circleTbl( c_id INTEGER, circles circle_t);

To create and initialize an instance of the circle_t UDT using the circle_t constructor method defined previously in this example, you can use the NEW expression:

INSERT circleTbl( 1001, NEW circle_t(512, 512, 36, 'RED') );

You can also create and initialize an instance of the circle_t UDT like this:

INSERT circleTbl( 1002, NEW circle_t().circle_t(256, 128, 78, 'BLUE') );

The preceding statement invokes the circle_t constructor UDF that Vantage automatically generates for a structured UDT to create an instance of the circle_t UDT that has all of the attributes set to NULL. Then, the statement invokes the circle_t constructor method on the newly created instance of circle_t to initialize the attributes.

Here is an example where Vantage invokes the cast method to convert the UDT to a VARCHAR(80) type:

SELECT c_id
FROM circleTbl
WHERE CAST (circles AS VARCHAR(80)) = '256:128:78:BLUE';

Here is an example where Vantage invokes the transform method to export the data in the circles column from the server:

SELECT * FROM circleTbl;
Here is an example where Vantage invokes the following methods:
  • Transform method to export the data in the circles column from the server
  • Ordering method to compare the UDTs in the circles column
  • Constructor method to create an instance of a circle_t UDT to use in the comparison
    SELECT * FROM circleTbl WHERE circles < NEW circle_t(0,0,20,'RED');