Select rows within a table hierarchy

This section describes how to query rows from tables within a table hierarchy. For more information about how to create and use a table hierarchy, see the IBM® Informix® Database Design and Implementation Guide.

The following figure shows the statements that create the type and table hierarchies that the examples in this section use.

Figure 1. Statements that create the type and table hierarchies.

CREATE ROW TYPE address_t
(
   street   VARCHAR (20),
   city     VARCHAR(20),
   state    CHAR(2),
   zip      VARCHAR(9)
)

CREATE ROW TYPE person_t
(
   name     VARCHAR(30),
   address  address_t,
   soc_sec  CHAR(9)
)

CREATE ROW TYPE employee_t 
(   
salary       INTEGER
)
UNDER person_t

CREATE ROW TYPE sales_rep_t
(
   rep_num     SERIAL8, 
   region_num  INTEGER
)
UNDER employee_t

CREATE TABLE person OF TYPE person_t

CREATE TABLE employee OF TYPE employee_t
UNDER person

CREATE TABLE sales_rep OF TYPE sales_rep_t
UNDER employee

The following figure shows the hierarchical relationships of the row types and tables in the previous figure.

Figure 2. Type and table hierarchies

This figure is described in the surrounding text.