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

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
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;

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;