Example of creating a virtual 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	BZ03	(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;

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');

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	BZ03	t1	97	94.25	95	2000000
901	BZ03	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.

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 BZ03 stock, use the following query:

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