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 HCL OneDB™ 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.