CONNECT Table Types - XCOL Table Type

XCOL tables are based on another table or view, like PROXY tables. This type can be used when the object table has a column that contains a list of values.

Suppose we have a 'children' table that can be displayed as:

We can have a different view on these data, where each child will be associated with his/her mother by creating an XCOL table by:

CREATE TABLE xchild (
  mother char(12) NOT NULL flag=1,
  child varchar(30) DEFAULT NULL flag=2
  ) ENGINE=CONNECT table_type=XCOL tabname='children'
option_list='colname=child';

The COLNAME option specifies the name of the column receiving the list items. This will return from:

select * from xchild;

The requested view:

Several things should be noted here:

• When the original childlist field is void, what happens depends on the NULL specification of the "multiple" column. If it is nullable, like here, a void string will generate a NULL value. However, if the column is not nullable, no row will be generated at all.

• Blanks after the separator are ignored.

• No copy of the original data was done. Both tables use the same source data.

• Specifying the column definitions in the CREATE TABLE statement is optional.

The "multiple" column child can be used as any other column. For instance:

select * from xchild where substr(child,1,1) = 'A';

This will return:

If a query does not involve the "multiple" column, no row multiplication will be done. For instance:

select mother from xchild;

The same occurs with other types of select statements, for instance:

select count(*) from xchild;      -- returns 11
select count(child) from xchild;  -- returns 10
select count(mother) from xchild; -- returns 5

Grouping also gives different result:

select mother, count(*) from xchild group by mother;

Replies:

While the query:

select mother, count(child) from xchild group by mother;

Gives the more interesting result:

Some more options are available for this table type: