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;