CONNECT 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 are associated with his/her mother by creating an XCOL table by:

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

The requested view:

Several things should be noted here:

• When the original children 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 are 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

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

This will return:

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

This will just return all the mothers:

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

Grouping also gives different result:

Replies:

While the query:

Gives the more interesting result:

Some more options are available for this table type:

Using Special Columns with XCOL

Special columns can be used in XCOL tables. The mostly useful one is ROWNUM that gives the rank of the value in the list of values. For instance:

This table are displayed as:

To list only the first child of each mother you can do:

returning:

However, note the following pitfall: trying to get the names of all mothers having more than 2 children cannot be done by:

This is because with no row multiplication being done, the rank value is always 1. The correct way to obtain this result is longer but cannot use the ROWNUM column:

XCOL tables based on specified views

Instead of specifying a source table name via the TABNAME option, it is possible to retrieve data from a “view” whose definition is given in a new option SRCDEF . For instance:

Then, for instance:

This will display something like:

Note: All XCOL tables are read only.

_{This page is licensed: CC BY-SA / Gnu FDL}