Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 12: Accessing Complex Data Types

Home Contents Index Master Index New Book

Accessing Rows from Tables in a Table Hierarchy

This section describes how to query and modify rows from tables in a table hierarchy. Figure 12-4 shows the statements that create the type hierarchy and corresponding table hierarchy that are used in examples throughout this section:

Figure 12-4

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;

Selecting Rows from a Supertable

A table hierarchy allows you to construct a query whose scope is a supertable and its subtables, in a single SQL statement. A query on a supertable in a table hierarchy returns rows from both the supertable and the subtables that inherit from the supertable. Query 12-15 shows a query on the person table, which is the root supertable in the table hierarchy.

Query 12-15

SELECT * FROM person

The preceding query on a supertable returns all columns in the supertable and those columns in subtables that are inherited from the supertable. A query on a supertable does not return columns from subtables that are not in the supertable. Query Result 12-15 shows the name, address, and soc_sec columns in the person, employee, and sales_rep tables.


	Query Result 12-15

Although a SELECT statement on a supertable returns rows from both the supertable and its subtables, you cannot tell which rows come from the supertable and which rows come from the subtables. To limit the results of a query to the supertable only, you must include the ONLY keyword in the SELECT statement. For example, Query 12-16 returns rows in the person table only.

Query 12-16

SELECT * FROM ONLY(person)


	Query Result 12-16

Using an Alias for a Supertable

You can specify an alias for a typed table in a SELECT or UPDATE statement and then use the alias as an expression by itself. If you create an alias for a supertable, the alias can represent values from the supertable or the subtables that inherit from the supertable. For example, suppose you define a function foo() that accepts an argument of type person_t and returns a Boolean value. Query 12-17 can return row values from the person, employee, and sales_rep tables. More specifically, the query returns values for all instances of the person, employee, and sales_rep table that foo() evaluates as TRUE.

Query 12-17

SELECT p FROM person p
WHERE foo(p)

Inserting Rows into a Supertable

There are no special considerations for inserting a row into a supertable. An INSERT statement applies only to the supertable that is specified in the statement. For example, the following statement inserts values into the supertable but does not insert values into any subtables:

INSERT INTO person
VALUES ('Poole, John', 
ROW('402 Saphire St.', 'Elmondo', 'CA', 69055'),
345605900)

Updating Rows from a Supertable

When you update the rows of a supertable, the scope of the update is a supertable and its subtables.

When you construct an UPDATE statement on a supertable, you can update all columns in the supertable and columns of subtables that are inherited from the supertable. For example, the following statement updates rows from the employee and sales_rep tables:

UPDATE employee
SET salary=65000
WHERE address.state = 'CA'

However, an update on a supertable does not allow you to update columns from subtables that are not in the supertable. For example, in the previous update statement, you cannot update the region_num column of the sales_rep table because the region_num column does not occur in the employee table.

When performing updates on supertables, be aware of the scope of the update. For example, an UPDATE statement on the person table that does not include a WHERE clause to restrict which rows to update, modifies all rows of the person, employee, and sales_rep table.

To limit an update to rows of the supertable only, you must use the ONLY keyword in the UPDATE statement. For example, the following statement updates rows of the person table only:

UPDATE ONLY(person)
SET address = ROW('14 Jackson St', 'Berkeley', address.state, 
address.zip)
WHERE name = 'Sallie, A.'

Warning: Use caution when you update rows of a supertable because the scope of an update on a supertable includes the supertable and all its subtables.

Deleting Rows from a Supertable

When you delete the rows of a supertable, the scope of the delete is a supertable and its subtables. For example, a DELETE statement on the person table deletes rows of the person, employee, and sales_rep table. To limit a delete to rows of the supertable only, you must use the ONLY keyword in the DELETE statement. For example, the following statement deletes rows of the employee table only:

DELETE FROM ONLY(employee)
WHERE name ='Walker'

Warning: Use caution when you delete rows from a supertable because the scope of a delete on a supertable includes the supertable and all its subtables.