SQL Reference

TREAT

TREAT is a SQL function used to change a value's data type to a specified type within an object-oriented database. It primarily operates on subtype data within a query, particularly when dealing with inheritance structures. Following successful type conversion, TREAT provides increased accessibility to features specific to the new type.

TREAT(object AS type)

object: This is the expression that you want to convert from its original data type to the target data type specified. Object can be a subquery, column, a function that returns a value, or a value in the valid data type. It works with subtypes of user-defined types including object types and collection types.
as type: This represents the target data type to which you want to convert the object. Type can be an SQL standard data type or a user-defined type including object types and collection types. The database will attempt to convert the object to the specified type and if the conversion is not possible, an error will be raised.

Example

SELECT TREAT(REF(er) AS SPECIALIZED_EMP_T) AS emp
FROM EMP_RECTABLE er
WHERE VALUE(er) IS OF (ONLY SPECIALIZED_EMP_T);

Output

EMP
---
SPECIALIZED_EMP_T('Alex', 'Marketing', 1500, 30)
SPECIALIZED_EMP_T('Melinda', 'HR', 2000, 20)

Explanation

The TREAT function is used in Oracle to treat a substitutable type or a substitutable column as a specific subtype. In the given example, it treats each row of the EMP_RECTABLE as the subtype SPECIALIZED_EMP_T and returns the row only if the row is precisely of the specified subtype.

TREAT(value AS data_type)

value: This parameter refers to the variable to be casted. It is the variable or the value provided by the user that will undergo treatment.
data_type: This parameter specifies the expected SQL data type of the value after treating. One must ensure that the provided value is convertible to the data type mentioned for consistent results.

Example

SELECT TREAT(value AS nvarchar(max)) AS Country
FROM STRING_SPLIT('USA,UK,Australia,India', ',');

Output

Country
----------------
USA
UK
Australia
India

Explanation

The TREAT function is used to convert one data type to another. In the provided example, the string of countries is being split into individual rows. The TREAT function is ensuring the split values are treated as nvarchar(max) data type.