Getting Started › DATACOM VIEWs › Redefinitions › Example of Multiple Record Types

Example of Multiple Record Types

In the following example, the ORDERS table contains orders for both parts and service calls. The table has the following set of column definitions:

• order_id is INTEGER
• order_type is CHAR(7)
• order_detail is CHAR(200)

The order_type column defines the type of row. When order_type is PARTS, order_detail is an array of part numbers, followed by an array of quantities. When the order_type is SERVICE, order_detail contains a textual description of the requested service.

Assume that order_detail is redefined for PARTS orders as follows:

• Part_number is an array of zoned-decimal (NUMERIC(5,0)) part numbers, followed by quantity, an array of INTEGER quantities for each part ordered.
• Order_detail is again redefined for SERVICE orders as follows: service_description is CHAR(199), followed by on_site, which is CHAR(1).

Given the previously stated conditions, the following views can be defined:

 CREATE DATACOM VIEW part_orders
        (order_id, order_type, part_1, quantity_1,
         part_2, quantity_2, part_3, quantity_3)
  AS
  SELECT order_id, order_type, part_number[1], quantity[1]
         part_number[2], quantity[2], part_number[3],quantity[3]
  FROM orders
  WHERE order_type = 'PARTS'
 CREATE DATACOM VIEW service_orders (order_id, order_type, on_site, description)
  AS
  SELECT order_id, order_type, on_site, service_description
  FROM orders
  WHERE order_type = 'SERVICE'

The views may then be manipulated almost as if they were database tables, with the restrictions previously noted as well as restrictions applicable to any view. If we had omitted the WHERE clauses, then view part_orders might try to read the service description of a service order and interpret it as an array of zoned-decimal part numbers.

Note: The WITH CHECK OPTION is supported.