Example 1: Using UDT Data Types in Scalar Expressions - Teradata Vantage - Analytics Database

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.

          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')
          END;

Result:

*** Query completed. 2 rows found. 3 columns returned.
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;