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

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

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;
*** 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;