Informix Guide to SQL: Tutorial

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

Home Contents Index Master Index New Book

Accessing Collection Type Data

This section describes how to use DB-Access and the SQL Editor to query and modify columns that are defined on collection types. The only way to select, insert, update, or delete individual elements in a collection is through an external or SPL routine. In addition, you cannot perform subqueries on a column that is a collection type.

For information on how to create an ESQL/C program to modify collection type data, see the INFORMIX-ESQL/C Programmer's Manual. For information on how to create a stored procedure to modify collection type data, see Chapter 14, "Creating and Using SPL Routines."

Figure 12-3 shows the manager table, which is used in examples throughout this section. The manager table contains examples of simple and nested collection types. A simple collection is a collection type that does not contain any fields that are themselves collection types. The direct_reports column of the manager table is an example of a simple collection. A nested collection is a collection type that contains another collection type. The projects column of the manager table is an example of a nested collection.

Figure 12-3

CREATE TABLE manager
(
	mgr_name				VARCHAR(30),
	department				VARCHAR(12),
	direct_reports				SET(VARCHAR(30) NOT NULL),
	projects				LIST(ROW(pro_name VARCHAR(15),
					pro_members SET(VARCHAR(20)
					NOT NULL) ) NOT NULL)
);

Selecting Collections

A query on a column that is a collection type returns, for each row in the table, all the elements that the particular collection contains. For example, Query 12-8 shows a query that returns data in the department column and all elements in the direct_reports column, for each row of the manager table.

Query 12-8

SELECT department, direct_reports 
FROM manager 





Query Result 12-8

The output of a query on a collection type always includes the type constructor that specifies whether the collection is a SET, MULTISET, or LIST. For example, in Query Result 12-8, the SET constructor precedes the elements of each collection. Braces ({}) demarcate the elements of a collection; commas separate individual elements of a collection.

Selecting Nested Collections

The projects column of the manager table (see Figure 12-3) is a nested collection. A query on a nested collection type returns all the elements that the particular collection contains. Query 12-9 shows a query that returns all elements from the projects column for a specified row. The WHERE clause limits the query to a single row in which the value in the mgr_name column is Sayles.

Query 12-9

SELECT projects 

FROM manager

WHERE mgr_name = 'Sayles'

Query Result 12-9 shows a project column collection for a single row of the manager table. The collection contains, for each element in the LIST, the project name (pro_name) and the SET of individuals (pro_members) who are assigned to each project.


	Query Result 12-9

Using the IN Keyword to Search for Elements in a Collection

You can use the IN keyword in the WHERE clause of an SQL statement to determine whether a collection contains a certain element. For example, Query 12-10 shows how to construct a query that returns values for mgr_name and department where Adams is an element of a collection in the direct_reports column.

Query 12-10

SELECT mgr_name, department 
FROM manager
WHERE 'Adams' IN (direct_reports) 





Query Result 12-10

Although you can use a WHERE clause with the IN keyword to search for a particular element in a simple collection, the query always returns the complete collection. For example, Query 12-12 returns all the elements of the collection where Adams is an element of a collection in the direct_reports column.

Query 12-11

SELECT mgr_name, direct_reports

FROM manager

WHERE 'Adams' IN (direct_reports) 





Query Result 12-11

As Query Result 12-11 shows, the query returns the entire collection, never a particular element within the collection.

You can use the IN keyword in a WHERE clause to reference a simple collection only. You cannot use the IN keyword to reference a collection that contains fields that are themselves collections. For example, you cannot use the IN keyword to reference the projects column in the manager table because projects is a nested collection.

You can combine the NOT and IN keywords in the WHERE clause of a SELECT statement to search for collections that do not contain a certain element. For example, Query 12-12 shows a query that returns values for mgr_name and department where Adams is not an element of a collection in the direct_reports column.

Query 12-12

SELECT mgr_name, department 
FROM manager
WHERE 'Adams' NOT IN (direct_reports) 





Query Result 12-12

Using the CARDINALITY() Function to Count the Elements in a Collection

The CARDINALITY() function counts the number of elements that a collection contains. Any duplicates in a collection are counted as individual elements. Query 12-13 shows a query that returns, for every row in the manager table, department values and the number of elements in each direct_reports collection.

Query 12-13

SELECT department, CARDINALITY(direct_reports)
FROM manager 





Query Result 12-13

You can also evaluate the number of elements in a collection from within a predicate expression, as Query 12-14 shows.

Query 12-14

SELECT department, cardinality(direct_reports)
FROM manager
WHERE CARDINALITY(direct_reports) < 6 
GROUP BY department 





Query Result 12-14

Modifying Collections

This section describes how to insert, update, and delete rows that contain collection-type data.

Inserting Rows That Contain Collection Types

When you use DB-Access or the SQL Editor to insert values into a row that contain a collection-type column, you insert the values of all the elements that the particular collection contains as well as values for the other columns. For example, the following statement inserts a single row into the manager table, which includes columns for both simple collections and nested collections:

INSERT INTO manager(mgr_name, department, direct_reports, 
projects)
VALUES 

(
'Sayles', 'marketing', 
"SET{'Simonian', 'Waters', 'Adams', 'Davis', 'Jones'}", 
"LIST{

	ROW('voyager_project', SET{'Simonian', 'Waters',
	'Adams', 'Davis'}), 

	ROW ('horizon_project', SET{'Freeman', 'Jacobs',
	'Walker', 'Smith', 'Cannan'}), 

	ROW ('saphire_project', SET{'Villers', 'Reeves',

	'Doyle', 'Strongin'})
	}"
)

To insert values into a collection that is a row type, you must specify a value for each field in the row type. You can insert null values into the fields of a collection row type, provided that at least one of the fields of the row type is not null. In other words, for a specific element of a collection, you can insert values for some fields of the collection row and specify null values for other fields.

You also can specify an empty collection. An empty collection is a collection that contains no elements. To specify an empty collection, use the braces ({}). For example, the following statement inserts data into a row in the manager table but specifies that the direct_reports and projects columns are empty collections:

INSERT INTO manager
VALUES ('Sayles', 'marketing', "SET{}", "LIST{ROW(SET{})}")

A collection column cannot contain null elements. The following statement returns an error because the direct_reports column and all fields of the projects column specify null elements:

INSERT INTO manager
VALUES ('Cole', 'accounting', "SET{NULL}", "LIST{ROW(NULL, 
""SET{NULL}"")}"

The following syntax rules apply for performing inserts and updates on collection types:

Use braces ({}) to demarcate the elements that each collection contains
If the collection is a nested collection, use braces ({}) to demarcate the elements of both the inner and outer collections

Updating Collection Types

When you use DB-Access or the SQL Editor to update a collection type, you must update the entire collection. The following statement shows how to update the projects column. To locate the row that needs to be updated, use the IN keyword to perform a search on the direct_reports column.

UPDATE manager
SET projects = "LIST 
{
	ROW('brazil_project', SET{'Pryor', 'Murphy', 'Kinsley',
		'Bryant'}), 
	ROW ('cuba_project', SET{'Forester', 'Barth', 'Lewis',
		'Leonard'})
}"
WHERE 'Williams' IN direct_reports

The first occurrence of the SET keyword in the preceding statement is part of the UPDATE statement syntax. Do not confuse it with the SET constructor which indicates that a collection is a SET.

Although you can use the IN keyword to locate specific elements of a simple collection, you cannot update individual elements of a collection column from DB-Access or the SQL Editor.

Deleting Rows That Contain Collection Types

When a row contains a column that is defined on a collection type, you can search for a particular element in a collection and delete the row or rows in which that element is found. For example, the following statement deletes rows in which the direct_reports column contains a collection with the element Baker:

DELETE FROM manager
WHERE 'Baker' IN direct_reports

Informix Guide to SQL: TutorialChapter 12: Accessing Complex Data Types Home Contents Index Master Index New Book