Example of creating a virtual table

This example shows how to create a virtual table on a table that contains time series data and the difference between querying the base table and the virtual table.

To improve clarity, these examples use values t1 through t6 to indicate DATETIME values, rather than showing complete DATETIME strings.

Query the base table

The base table, daily_stocks, was created with the following statements:

create row type stock_bar(
   timestamp      datetime year to fraction(5),
   high      real,
   low      real,
   final      real,
   vol      real
);

create table daily_stocks (
   stock_id         int,
   stock_name         lvarchar,
   stock_data         TimeSeries(stock_bar)
);

The daily_stocks base table contains the following data.

Table 1. The **daily_stocks** base table
stock_id	stock_name	stock_data
900	AA01	(`t1`, 7.25, 6.75, 7, 1000000), (`t2`, 7.5, 6.875, 7.125, 1500000), ...
901	IBM®	(`t1`, 97, 94.25, 95, 2000000), (`t2`, 97, 95.5, 96, 3000000), ...
905	FNM	(`t1`, 49.25, 47.75, 48, 2500000), (`t2`, 48.75, 48, 48.25, 3000000), ...

To query on the stock_data column, you must use time series functions. For example, the following query uses the Apply function to obtain the closing price:

select stock_id,  
Apply('$final', stock_data)::TimeSeries(one_real) 
from daily_stocks;

In this query, one_real is a row type that is created to hold the results of the query and is created with this statement:

create row type one_real(
   timestamp datetime year to fraction(5), 
   result real);

To obtain price and volume information within a specific time range, use a query that has the following format:

select stock_id, Clip(stock_data, t1, t2) from daily_stocks;

Create the virtual table

The following statement uses the TSCreateVirtualTab procedure to create a virtual table, called daily_stocks_no_ts, based on daily_stocks:

execute procedure  
TSCreateVirtualTab('daily_stocks_no_ts', 'daily_stocks');

Because the statement does not specify the NewTimeSeries parameter, daily_stocks_no_ts does not allow inserts of elements that do not have a corresponding time series in daily_stocks.

Also, the statement omits the TSVTMode parameter, so TSVTMode assumes its default value of 0. Therefore, if you insert data into daily_stocks_no_ts, the database server uses PutElemNoDups to add an element to the underlying time series in daily_stocks.

The following table illustrates the virtual table, daily_stocks_no_ts.

Table 2. The daily_stocks_no_ts virtual table
stock_id	stock_name	timestamp*	high	low	final	vol
900	AA01	`t1`	7.25	6.75	7	1000000
900	AA01	`t2`	7.5	6.875	7.125	1500000
...	...	...			...	...
901	IBM®	`t1`	97	94.25	95	2000000
901	IBM®	`t2`	97	95.5	96	3000000
...	...	...			...	...
905	FNM	`t1`	49.25	47.75	48	2500000
905	FNM	`t2`	48.75	48	48.25	3000000
...	...	...			...

* In this column, t1 and t2 are DATETIME values.

Query the virtual table

Certain SQL queries are much easier to write for a virtual table than for a base table. For example, the query to obtain the closing price is much simpler:

select stock_id, final from daily_stocks_no_ts;

The query to obtain price and volume within a specific time range is:

select * from daily_stocks_no_ts 
where timestamp between t1 and t5;

Some tasks that are complex for time series functions to accomplish, such as use of the ORDER BY clause, are now simple:

select * from daily_stocks_no_ts
where timestamp between t1 and t5 
order by volume;

Inserting data into the virtual table is also simple. To add an element to the IBM® stock, use the following query:

insert into daily_stock_no_ts 
values('IBM', t6, 55, 53, 54, 2000000);

The element (t6, 55, 53, 54, 2000000) is added to daily_stocks.