16.20 - Sample Queries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

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 Teradata Database 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 Teradata Database 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 Teradata Database invokes the transform method to export the data in the circles column from the server:

SELECT * FROM circleTbl;
Here is an example where Teradata Database 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');