Informix Guide to SQL: Syntax Segments

Use the Collection Derived Table segment to:

• access the elements of a collection as you would the rows of a table.
• specify a collection variable to access instead of a table name.
• specify a row variable to access instead of a table name.

A collection derived table is a virtual table in which the values in the rows of the table are equivalent to the elements of a collection. In other words, a collection derived table appears as a table in an SQL statement.

The TABLE keyword table converts a collection into a virtual table. You can use the collection expression format to query a collection column or you can use the collection variable or row variable format to manipulate the data in a collection column.

When you use the collection expression format of the Collection Derived Table segment to access the elements of a collection, you can select elements of the collection directly through a virtual table. You can use this format only in the FROM clause of a SELECT statement. The FROM clause can be in either a query or a subquery.

With this format you can use joins, aggregates, WHERE clause, expressions, the ORDER BY clause and other operations not available to you when you use the collection-variable format. This format reduces the need for multiple cursors and temporary tables.

When you uses the collection-expression format, certain restrictions apply:

• A collection derived table is read-only.
• It cannot be the target of insert, update, or delete statements.

To perform insert, update, and delete operations you must use the collection-variable format.

• It cannot be the underlying table of an updatable cursor or view.
• You cannot use the WITH ORDINALITY keywords to introduce a new column whose value is the ordinality of a row in the list expression.
• If the collection is a list, the resulting collection derived table does not preserve the order of the elements in the list.
• The underlying collection expression cannot evaluate to null.
• The collection expression cannot contain column references to tables that appear in the same FROM clause. That is, the collection derived table must be independent of other tables in the from clause.

For example, the following statement returns an error because the collection derived table, table (parents.children), refers to the table parents, which is also referenced in the FROM clause:

SELECT COUNT(*)

FROM parents, TABLE(parents.children) c_table

WHERE parents.id = 1001

To counter this restriction, you might write a query that contains a subquery in the select list:

SELECT (SELECT COUNT(*)

FROM TABLE(parents.children) c_table)

FROM parents

WHERE parents.id = 1001

Additional Restrictions that Apply to ESQL/C

In addition to the restrictions outlined in the previous section, the following restrictions apply when you use the collection-expression format with ESQL/C.

• You cannot use the format TABLE(?)

The type of the underlying collection variable must be determined statically. To counter this restriction, you can explicitly cast the variable to a typed collection type (SET, MULTISET, or LIST) that the database server recognizes. For example,

TABLE(CAST(? AS type))

• You cannot use the format TABLE(:hostvar)

To counter this restriction, you must explicitly cast the variable to a typed collection type (SET, MULTISET, or LIST) that the database server recognizes. For example

TABLE(CAST(:hostvar AS type))

• You cannot use the untyped collection-host variable, COLLECTION.

Although a collection derived table appears to contain columns of individual data types, the resulting columns are, in fact, fields of a row type. The type of row type as well as the column name depends on several factors.

If the data type of the elements of the underlying collection expression is type, the database server determines the row type of the collection derived table by the following rules:

• If type is a row type, and no derived column list is specified, then the row type of the collection derived table is type.
• If type is a row type and a derived column list is specified, then the row type of the collection derived table is an unnamed row type whose column types are the same as those of type and whose column names are taken from the derived column list.
• If type is not a row type, the row type of the collection derived table is an unnamed row type that contains one column of type, and whose name is specified in the derived column list. If a name is not specified, the database server assigns an implementation-dependent name to the column.

The extended examples shown in the following table illustrate these rules. The table uses the following schema for its examples.

CREATE ROW TYPE person (name CHAR(255), id INT);
CREATE TABLE parents
	(
	name CHAR(255), 
	id INT,
	children LIST (person NOT NULL)
	);
CREATE TABLE parents2
	(
	name CHAR(255), 
	id INT,
	children_ids LIST (INT NOT NULL)
	);

 





Type

Derived-Column List Specified

Resulting Row Type of the Collection Derived Table

Example


Row Type

No

Type

SELECT (SELECT c_table.name 
FROM TABLE(parents.children c_table 
WHERE c_table.id = 1002)

FROM parents
WHERE parents.id = 1001

In this example, the row type of c_table is parents.


Row Type 

Yes

Unnamed row type of which the column type is Type and the column name is the name provided in the derived-column list

SELECT (SELECT c_table.c_name
FROM TABLE(parents.children) c_table(c._name, c_id)
WHERE c_table.c_id = 1002)

FROM parents
WHERE parents.id = 1001

In this example, the row type of c_table is 
row(c_name CHAR(255), c_id INT).


Not a row type

No

Unnamed row type that contains one column of Type that is assigned an implementation-dependent name

See the following example. 

Using the following example, if you do not specify c_id, the database server assigns a name to the derived column. Thus the row type of c_table is row(server_defined_name INT).


Not a row type

Yes

Unnamed row type that contains one column of Type whose name is the name provided in the derived-column list.

SELECT(SELECT c_table.c_id
FROM TABLE(parents2.child_ids) c_table (c_id)
WHERE c_table.c_id = 1002)

FROM parents
WHERE parents.id = 1001

In this example, the row type of c_table is 
row(c_id INT).

The following example uses a collection expression to create a collection derived table:

CREATE TABLE wanted(person_id int);
CREATE FUNCTION wanted_person_count
(person_set SET(person NOT NULL))
RETURNS INT;
RETURN( SELECT COUNT (*)
FROM TABLE (person_set) c_table, wanted
WHERE c_tabel.id = wanted.person_id);
END FUNCTION;

When you use the collection-variable format of the Collection Derived Table segment, you use a host or program variable to access and manipulate the elements of a collection.

This format allows you to modify the contents of a variable as you would a table in the database and then update the actual table with the contents of the collection variable.

You can use the collection-variable format (the TABLE keyword preceding a collection variable) in place of the name of a table, synonym, or view name in the following SQL statements:

• The FROM clause of the SELECT statement to access an element of the collection variable
• The INTO clause of the INSERT statement to add a new element to the collection variable
• The DELETE statement to remove an element from the collection variable
• The UPDATE statement to modify an existing element in the collection variable
• The DECLARE statement to declare a select or insert cursor to access multiple elements of an ESQL/C collection-host variable.
• The FETCH statement to retrieve a single element from a collection-host variable that is associated with a select cursor.
• The PUT statement to retrieve a single element from a collection-host variable that is associated with an insert cursor.
• The FOREACH statement to declare a cursor to access multiple elements of an SPL collection variable and to retrieve a single element from this collection variable.

When you use data manipulation statements (SELECT, INSERT, UPDATE or DELETE) in conjunction with a collection variable you can modify one or more elements in a collection.

To modify elements in a collection, follow these general steps:

1. Create a collection variable in your SPL routine or ESQL/C program.

For information on how to declare a collection variable in ESQL/C, see the Informix ESQL/C Programmer's Manual. For information on how to define a collection variable in SPL, see DEFINE.

2. In ESQL/C, allocate memory for the collection; see ALLOCATE COLLECTION.
3. Optionally, use a SELECT statement to select a collection column into the collection variable.

If the collection variable is an untyped COLLECTION variable, you must perform a SELECT from the collection column before you use the variable in the Collection Derived Table segment. The SELECT statement allows the database server to obtain the collection type.

4. Use the appropriate data manipulation statement with the Collection Derived-Table segment to add, delete, or update collection elements in the collection variable.

To insert more than one element or to update or delete a particular element or in the collection, you must use a cursor for the collection variable.

• For more information on how to use an update cursor with ESQL/C, see DECLARE.
• For more information on how to use an update cursor with SPL, see FOREACH.

5. After the collection variable contains the correct elements, use an INSERT or UPDATE statement on the table or view that holds the actual collection column to save the changes that the collection variable holds.
• With the UPDATE statement, specify the collection variable in the SET clause.
• With the INSERT statement, specify the collection variable in the VALUES clause.

The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the variable into the actual collection column of the table with either an INSERT or an UPDATE statement.

Suppose that the set_col column of a row in the table1 table is defined as a SET and for one row contains the values {1,8,4,5,2}. The following ESQL/C code fragment uses an update cursor and a DELETE statement with a WHERE CURRENT OF clause to delete the element whose value is 4:

EXEC SQL BEGIN DECLARE SECTION;

	client collection set(smallint not null) a_set;

	int an_int;

EXEC SQL END DECLARE SECTION;
...
EXEC SQL allocate collection :a_set; 
EXEC SQL select set_col into :a_set from table1

	where int_col = 6;

EXEC SQL declare set_curs cursor for

	select * from table(:a_set)

	for update;

EXEC SQL open set_curs;
while (i<coll_size)

{

	EXEC SQL fetch set_curs into :an_int;

	if (an_int = 4)

	{

		EXEC SQL delete from table(:a_set) 

			where current of set_curs;

		break;

	}

	i++;

}

EXEC SQL update table1 set set_col = :a_set

	where int_col = 6;

EXEC SQL deallocate collection :a_set;

EXEC SQL close set_curs;

EXEC SQL free set_curs;

After the DELETE statement executes, this collection variable contains the elements {1,8,5,2}. The UPDATE statement at the end of this code fragment saves the modified collection into the set_col column of the database. Without this UPDATE statement, the collection column never has element 4 deleted.

For information on how to use collection-host variables in an ESQL/C program, see the discussion of complex data types in the Informix ESQL/C Programmer's Manual.

Suppose that the set_col column of a row in the table1 table is defined as a SET and one row contains the values {1,8,4,5,2}. The following SPL code fragment uses a FOREACH loop and a DELETE statement with a WHERE CURRENT OF clause to delete the element whose value is 4:

CREATE_PROCEDURE test6()

	DEFINE a SMALLINT;
	DEFINE b SET(SMALLINT NOT NULL);

	SELECT set_col INTO b FROM table1

		WHERE id = 6;
		-- Select the set in one row from the table
		-- into a collection variable

	FOREACH cursor1 FOR
		SELECT * INTO a FROM TABLE(b);
			-- Select each element one at a time from
			-- the collection derived table b into a
		IF a = 4 THEN
			DELETE FROM TABLE(b) 
				WHERE CURRENT OF cursor1;
				-- Delete the element if it has the value 4
			EXIT FOREACH;
		END IF;
	END FOREACH;

	UPDATE table1 SET set_col = b

		WHERE id = 6;
		-- Update the base table with the new collection

END PROCEDURE;

This SPL routine defines two variables, a and b, each to hold a set of SMALLINT values. The first SELECT statement selects a SET column from one row of table1 into b. Then, the routine declares a cursor that selects one element at a time from b into a. When the cursor is positioned on the element with the value 4, the DELETE statement deletes that element from b. Last, the UPDATE statement updates the row of table1 with the new collection that is stored in b.

For information on how to use collection variables in an SPL routine, see the Informix Guide to SQL: Tutorial.

Suppose that the set_col column of a table called table1 is defined as a SET and that it contains the values {1,8,4,5,2}. The following ESQL/C program changes the element whose value is 4 to a value of 10.

main

{
	EXEC SQL BEGIN DECLARE SECTION;

		int a;

		collection b;

	EXEC SQL END DECLARE SECTION;

	EXEC SQL allocate collection :b;
	EXEC SQL select set_col into :b from table1

		where int_col = 6;



	EXEC SQL declare set_curs cursor for

		select * from table(:b)

		for update;

	EXEC SQL open set_curs;

	while (SQLCODE != SQLNOTFOUND)

	{

		EXEC SQL fetch set_curs into :a;

		if (a = 4)

		{

			EXEC SQL update table(:b)(x)

				set x = 10

				where current of set_curs;

			break;

		}

	}

	EXEC SQL update table1 set set_col = :b

		where int_col = 6;

	EXEC SQL deallocate collection :b;

	EXEC SQL close set_curs;

	EXEC SQL free set_curs;

}

After you execute this ESQL/C program, the set_col column in table1 contains the values {1,8,10,5,2}.

This ESQL/C program defines two collection variables, a and b, and selects a SET from table1 into b. The WHERE clause ensures that only one row is returned. Then, the program defines a collection cursor, which selects elements one at a time from b into a. When the program locates the element with the value 4, the first UPDATE statement changes that element value to 10 and exits the loop.

In the first UPDATE statement, x is a derived column name used to update the current element in the collection derived table. The second UPDATE statement updates the base table table1 with the new collection. For information on how to use collection host variables in an ESQL/C program, see the discussion of complex data types in the Informix ESQL/C Programmer's Manual.

Suppose the ESQL/C host variable a_multiset has the following declaration:

EXEC SQL BEGIN DECLARE SECTION;

	client collection multiset(integer not null) a_multiset;

EXEC SQL END DECLARE SECTION;

The following INSERT statement adds a new MULTISET element of 142,323 to a_multiset:

EXEC SQL allocate collection :a_multiset;

EXEC SQL select multiset_col into :a_multiset from table1

	where id = 107;

EXEC SQL insert into table(:a_multiset) values (142323);
EXEC SQL update table1 set multiset_col = :a_multiset

	where id = 107;

EXEC SQL deallocate collection :a_multiset;

When you insert elements into a client-collection variable, you cannot specify a SELECT statement or an EXECUTE FUNCTION statement in the VALUES clause of the INSERT. However, when you insert elements into a server-collection variable, the SELECT and EXECUTE FUNCTION statements are valid in the VALUES clause. For more information on client- and server-collection variables, see the Informix ESQL/C Programmer's Manual.

If the element of the collection is itself a complex type (collection or row type), the collection is a nested collection. For example, suppose the ESQL/C collection variable, a_set, is a nested collection that is defined as follows:

EXEC SQL BEGIN DECLARE SECTION;

	client collection set(list(integer not null)) a_set;

	client collection list(integer not null) a_list;
	int an_int;

EXEC SQL END DECLARE SECTION;

To access the elements (or fields) of a nested collection, use a collection or row variable that matches the element type (a_list and an_int in the preceding code fragment) and a select cursor.

The TABLE keyword can make an ESQL/C row variable a collection derived table, that is, a row appears as a table in an SQL statement. For a row variable, you can think of the collection derived table as a table of one row, with each field of the row type being a column of the table row.

Use the TABLE keyword in place of the name of a table, synonym, or view name in the following SQL statements:

• The FROM clause of the SELECT statement to access a field of the row variable
• The UPDATE statement to modify an existing field in the row variable

The DELETE and INSERT statements do not support a row variable in the Collection Derived Table segment.

For example, suppose an ESQL/C host variable a_row has the following declaration:

EXEC SQL BEGIN DECLARE SECTION;
	row(x int, y int, length float, width float) a_row;
EXEC SQL END DECLARE SECTION;

The following ESQL/C code fragment adds the fields in the a_row variable to the row_col column of the tab_row table:

EXEC SQL update table(:a_row)
	set x=0, y=0, length=10, width=20;
EXEC SQL update rectangles set rect = :a_row;

Related statements: DECLARE, DELETE, DESCRIBE, FETCH, INSERT, PUT, SELECT, UPDATE, DEFINE, and FOREACH

For information on how to use collection variables in an SPL routine, see the Informix Guide to SQL: Tutorial.

For information on how to use collection or row variables in an ESQL/C program, see the chapter on complex data types in the Informix ESQL/C Programmer's Manual.