Restrictions with the Collection-Expression Format

When you use 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.
• If the collection is a LIST data type, 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 a reference to a collection on a remote database server.
• 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 Projection clause:

  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 previously described restrictions, the following restrictions also apply when you use the collection-expression format with ESQL/C:

• You cannot specify an untyped COLLECTION as the host-variable data type.
• You cannot use the format TABLE(?).

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

  TABLE(CAST(:hostvar AS type))