TYPE predicate

A TYPE predicate compares the type of an expression with one or more user-defined structured types.

The dynamic type of an expression involving the dereferencing of a reference type is the actual type of the referenced row from the target typed table or view. This might differ from the target type of an expression involving the reference, which is called the static type of the expression.

If the value of expression is null, the result of the predicate is unknown. The result of the predicate is true if the dynamic type of the expression is a subtype of one of the structured types specified by typename, otherwise the result is false. If ONLY precedes any typename the proper subtypes of that type are not considered.

If typename is not qualified, it is resolved using the SQL path. Each typename must identify a user-defined type that is in the type hierarchy of the static type of expression (SQLSTATE 428DU).

The DEREF function should be used whenever the TYPE predicate has an expression involving a reference type value. The static type for this form of expression is the target type of the reference.

The syntax IS OF and OF DYNAMIC TYPE are equivalent alternatives for the TYPE predicate. Similarly, IS NOT OF and NOT OF DYNAMIC TYPE are equivalent alternatives.

Examples

A table hierarchy exists with root table EMPLOYEE of type EMP and subtable MANAGER of type MGR. Another table, ACTIVITIES, includes a column called WHO_RESPONSIBLE that is defined as REF(EMP) SCOPE EMPLOYEE. The following example shows a type predicate that evaluates to true when a row corresponding to WHO_RESPONSIBLE is a manager:

   DEREF (WHO_RESPONSIBLE) IS OF (MGR)

If a table contains a column EMPLOYEE of type EMP, EMPLOYEE may contain values of type EMP as well as values of its subtypes like MGR. The following predicate

   EMPL IS OF (MGR)

returns true when EMPL is not null and is actually a manager.