Example 1: Using UDT Data Types in Scalar Expressions

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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;