Example 1: Using UDT Data Types in Scalar Expressions - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

You use scalar_expression_nand scalar_expression_mas the expressions to return on when the equality comparison on a valued or searched CASE expression evaluates to TRUE, or the value to return on in an ELSE condition.

For these examples, the table is defined as follows:

create table udtval038_t1(id integer, udt1 testcircleudt, udt2 testrectangleudt) PRIMARY INDEX (id);

Following is an example of a searched CASE Expression where all scalar expressions are of the same UDT data type.

The search_condition_ncan be a different UDT data type than the scalar_expression_n.   SELECT * FROM udtval038_t1
          WHERE udt1 = CASE
          WHEN udt2 <> new testrectangleudt('2,2,4,4,pink,rect')
          THEN new testcircleudt('1,1,2,blue,circ')
          ELSE new testcircleudt('2,2,4,purple,circ')
*** Query completed. 2 rows found. 3 columns returned.
          END;
id udt1
----------- -----------------------------------------------
          1 1, 1, 2, yellow, circ
          2 2, 2, 4, purple, circ

However, the following example does not complete successfully because the scalar expressions are of different data types.

   SELECT * FROM udtval038_t1
          WHERE udt1 = CASE
          WHEN udt2 <> new testrectangleudt('2,2,4,4,pink,rect')
          THEN new testcircleudt('1,1,2,blue,circ')
          ELSE new testrectangleudt('2,2,4,4,purple,rect')
          END;