SQL Reference

Dereference Operations

 
dereference-operation
 
|--scoped-ref-expression-- -> --attribute-name------------------|
 

The dereference operation returns the named column value from the target table or a subtable, or the target view or a subview, of the scoped reference expression from the row with the matching object identifier column (OID column). See CREATE TABLE for further information on the OID column. The result can be null, regardless of whether the column corresponding to the attribute-name can be null. If there is no row in the target table with a matching OID, the result is null. The dereference operation takes precedence over all other operators.

scoped-ref-expression

An expression that is a reference type that has a scope (SQLSTATE 428DT). If the expression is a host variable, parameter marker or other unscoped reference type value, a CAST specification with a SCOPE clause is required to give the reference a scope.

attribute-name

Specifies the name of an attribute of the target type (column in the target table or view) of the scoped-ref-expression (SQLSTATE 42704). The attribute-name must be an attribute of the target type of the reference. The data type of the attribute determines the data type of the result of the dereference operation. When the dereference operation is used in a select list and is not included as part of an expression, the attribute name becomes the result column name.

The authorization ID of the statement that uses a dereference operation must have SELECT privilege on the target table of the scoped-ref-expression (SQLSTATE 42501).

Examples:

• Assume the existence of an EMPLOYEE table that contains a column called DEPTREF which is a reference type scoped to a typed table based on a type that includes the attribute DEPTNAME. The values of DEPTREF in the table EMPLOYEE should correspond to the OID column values in the target table of DEPTREF column.

    SELECT EMPNO, DEPTREF->DEPTNAME FROM EMPLOYEE
  

[ DB2 List of Books | Search the DB2 Books ]