Restricting returned types using a TYPE predicate

If you want a more general way to restrict what rows are returned or affected by an SQL statement, you can use the type predicate. The type predicate enables you to compare the dynamic type of an expression to one or more named types.

About this task

A simple version of the type predicate is:

   expression IS OF (type_name[, ...])

where expression represents an SQL expression that returns an instance of a structured type, and type_name represents one or more structured types with which the instance is compared.

Example

For example, the following query returns people who are greater than 35 years old, and who are either managers or architects:

   SELECT Name
      FROM Employee E
      WHERE E.Age > 35 AND
      DEREF(E.Oid) IS OF (Manager_t, Architect_t);

The previous query returns the following result:

   NAME
   --------------------
   Ken