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

You use value_expression_1through value_expression_nto test for equality in a valued CASE expression.

For these examples, the table is defined as follows:

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

The following example shows a valued CASE expression, where all value expressions are of the same UDT data type:

   SELECT CASE udt1
                    WHEN new testcircleudt('1,1,2,yellow,circ')
                    THEN 'Row 1'
                    WHEN new testcircleudt('2,2,4,purple,circ')
                    THEN 'Row 2'
                    WHEN new testcircleudt('3,3,9,green,circ')
                    THEN 'Row 3'
                    ELSE 'Row is NULL'
                    END
   FROM t1;

Result:

*** Query completed. 4 rows found. One column returned.
<CASE  expression>
------------------
Row 3
Row 1
Row is NULL
Row 2

However, the following example does not complete successfully because testrectangleudt does not match the other UDT data types:

   SELECT CASE udt1
                    WHEN new testcircleudt('1,1,2,yellow,circ')
                    THEN 'Row 1'
                    WHEN new testrectangleudt('2,2,4,4,purple,rect')
                    THEN 'Row 2'
                    WHEN new testcircleudt('3,3,9,green,circ')
                    THEN 'Row 3'
                    ELSE 'Row is NULL'
                    END
   FROM t1;