>>-DEREF---(--expression--)------------------------------------><
The schema is SYSIBM.
The DEREF function returns an instance of the target type of the argument.
The argument can be any value with a reference data type that has a defined scope (SQLSTATE 428DT).
The static data type of the result is the target type of the argument. The dynamic data type of the result is a subtype of the target type of the argument. The result can be null. The result is the null value if expression is a null value or if expression is a reference that has no matching OID in the target table.
The result is an instance of the subtype of the target type of the reference. The result is determined by finding the row of the target table or target view of the reference that has an object identifier that matches the reference value. The type of this row determines the dynamic type of the result. Since the type of the result can be based on a row of a subtable or subview of the target table or target view, the authorization ID of the statement must have SELECT privilege on the target table and all of its subtables or the target view and all of its subviews (SQLSTATE 42501).
Examples:
Assume that EMPLOYEE is a table of type EMP, and that its object identifier column is named EMPID. Then the following query returns an object of type EMP (or one of its subtypes), for each row of the EMPLOYEE table (and its subtables). This query requires SELECT privilege on EMPLOYEE and all its subtables.
SELECT DEREF(EMPID) FROM EMPLOYEE
For additional examples, see TYPE_NAME.