Access a collection derived table

When the SELECT statement for a collection does not reference the Informix® ESQL/C collection variable, the database server performs the query.

Consider, for example, the following schema: 
create row type person(name char(255), id int);
create table parents(name char(255), id int,
   children list(person not null));
You can select the names of children and IDs from the table parent by using the following SELECT statement: 
select name, id from table(select children from parents
   where parents.id = 1001) c_table(name, id);

To execute the query, the database server creates a virtual table (c_table) from the list children in the row of the parents table where parents.id equals 1001.