Accessing the attributes of subtypes

In the Employee table, addresses can be of 4 different types: Address_t, US_addr_t, Brazil_addr_t, and Germany_addr_t. To access attributes of values from one of the subtypes of Address_t, you must use the TREAT expression to indicate to Db2® that a particular object can be of the US_addr_t, Germany_addr_t, or Brazil_addr_t types. The TREAT expression casts a structured type expression into one of its subtypes, as shown in the following query:

   SELECT Name, Dept, Address..street, Address..number, Address..city,
      Address..state,
      CASE
         WHEN Address IS OF (US_addr_t) 
         THEN TREAT(Address AS US_addr_t)..zip
         WHEN Address IS OF (Germany_addr_t) 
         THEN TREAT (Address AS Germany_addr_t)..family_name
         WHEN Address IS OF (Brazil_addr_t)
         THEN TREAT (Address AS Brazil_addr_t)..neighborhood
      ELSE NULL END
      FROM Employee
      WHERE Salary > 20000;