TREAT

Syntax

treat::=

Text description of treat

Purpose

TREAT changes the declared type of an expression.

You must have the EXECUTE object privilege on type to use this function.

• If the declared type of expr is source_type, then type must be some supertype or subtype of source_type. If the most specific type of expr is type (or some subtype of type), then TREAT returns expr. If the most specific type of expr is not type (or some subtype of type), then TREAT returns NULL.
• If the declared type of expr is REF source_type, then type must be some subtype or supertype of source_type. If the most specific type of DEREF(expr) is type (or a subtype of type), then TREAT returns expr. If the most specific type of DEREF(expr) is not type (or a subtype of type), then TREAT returns NULL.

  Note: This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Please refer to "Datatype Comparison Rules" for more information.

Examples

The following statement uses the table oe.persons, which is created in "Substitutable Table and Column Examples". That table is based on the person_t type, which is created in "Type Hierarchy Example". The example retrieves the salary attribute of all people in the persons table, the value being null for instances of people that are not employees.

SELECT name, TREAT(VALUE(p) AS employee_t).salary salary 
   FROM persons p;

NAME                          SALARY
------------------------- ----------
Bob
Joe                           100000
Tim                             1000

You can use the TREAT function to create an index on the subtype attributes of a substitutable column. For an example, see "Indexing on Substitutable Columns: Examples".