Issuing queries to dereference references

Consider the Dept attribute of the Employee table, and subtables of Employee, which is scoped to the BusinessUnit table. The following example returns the names, salaries, and department names, or NULL values, where applicable, of all the employees in the database; that means the query returns these values for every row in the Employee table and the Employee subtables. You could write a similar query using a correlated subquery or an outer join. However, it is easier to use the dereference operator (->) to traverse the path from the reference column in the Employee table and subtables to the BusinessUnit table, and to return the result from the Name column of the BusinessUnit table.

The simple format of the dereference operation is as follows:

   scoped-reference-expression->column-in-target-typed-table

The following query uses the dereference operator to obtain the Name column from the BusinessUnit table:

   SELECT Name, Salary, Dept->Name
      FROM Employee

The result of the query is as follows:

   NAME                 SALARY      NAME
   -------------------- ----------- --------------------
   Dennis               30000       Toy
   Eva                  45000       Shoe
   Franky               39000       Shoe
   Iris                 55000       Toy
   Christina            85000       Toy
   Ken                  105000      Shoe
   Leo                  92000       Shoe
   Brian                112000      Toy
   Susan                37000.48    ---

You can dereference self-referencing references as well. Consider the Parts table. The following query lists the parts directly used in a wing with the locations of the suppliers of the parts:

   SELECT P.Descript, P.Supplied_by->Location
      FROM Parts P
      WHERE P.Used_in->Descript='Wing';

DEREF Built-in Function

You can also dereference references to obtain entire structured objects as a single value by using the DEREF built-in function. The simple form of DEREF is as follows:

   DEREF (scoped-reference-expression)

DEREF is usually used in the context of other built-in functions, such as TYPE_NAME, or to obtain a whole structured object for the purposes of binding out to an application.

Other Type-Related Built-in Functions

The DEREF function is often invoked as part of the TYPE_NAME, TYPE_ID, or TYPE_SCHEMA built-in functions. The purpose of these functions is to return the name, internal ID, and schema name of the dynamic type of an expression. For example, the following example creates a Project typed table with an attribute called Responsible:

   CREATE TYPE Project_t
      AS (Projid INT, Responsible REF(Employee_t))
      MODE DB2SQL;

   CREATE TABLE Project 
      OF Project_t (REF IS Oid USER GENERATED, 
      Responsible WITH OPTIONS SCOPE Employee);

The Responsible attribute is defined as a reference to the Employee table, so that it can refer to instances of managers and architects as well as employees. If your application needs to know the name of the dynamic type of every row, you can use a query like the following:

   SELECT Projid, Responsible->Name,
      TYPE_NAME(DEREF(Responsible))
      FROM PROJECT;

The preceding example uses the dereference operator to return the value of Name from the Employee table, and invokes the DEREF function to return the dynamic type for the instance of Employee_t.

Authorization requirement: To use the DEREF function, you must have SELECT authority on every table and subtable in the referenced portion of the table hierarchy. In the preceding query, for example, you need SELECT privileges on the Employee, Manager, and Architect typed tables.