SQL Reference

Subtype Treatment

 
subtype-treatment
 
|--TREAT--(--expression--AS--data-type--)-----------------------|
 

The subtype-treatment is used to cast a structured type expression into one of its subtypes. The static type of expression must be a user-defined structured type, and that type must be the same type as, or a supertype of, data-type. If the type name in data-type is unqualified, the SQL path is used to resolve the type reference. The static type of the result of subtype-treatment is data-type, and the value of the subtype-treatment is the value of the expression. At run time, if the dynamic type of the expression is not data-type or a subtype of data-type, an error is returned (SQLSTATE 0D000).

Examples:

• If an application knows that all column object instances in a column CIRCLE_COL have the dynamic type COLOREDCIRCLE, use the following query to invoke the method RGB on such objects. Assume the existence of a table called RINGS, with a column CIRCLE_COL of structured type CIRCLE. Also, assume that COLOREDCIRCLE is a subtype of CIRCLE and that the method RGB has been defined previously for COLOREDCIRCLE as RGB() RETURNS DOUBLE.

     SELECT TREAT (CIRCLE_COL AS COLOREDCIRCLE)..RGB()
        FROM RINGS
  

  At run-time, if there are instances of dynamic type CIRCLE, an error is raised (SQLSTATE 0D000). This error can be avoided by using the TYPE predicate in a CASE expression, as follows:

     SELECT (CASE
        WHEN CIRCLE_COL IS OF (COLOREDCIRCLE)
           THEN TREAT (CIRCLE_COL AS COLOREDCIRCLE)..RGB()
           ELSE NULL
        END)
        FROM RINGS
  

  See TYPE Predicate for more information.