Database Subqueries

A CCL query statement can incorporate data directly from an external database by means of a database subquery, which is nested in the CCL query as part of the FROM clause.

When the CCL query is running, the database subquery is passed to an external database server, such as Oracle. The external database executes the subquery, selects the appropriate data, and passes the data back to the CCL query. Once the Oracle data is returned to Sybase CEP and incorporated into the output data published by the query to its output stream, it can be processed by other CCL queries in the same manner as all other Sybase CEP data.

A database subquery is always joined to exactly one Sybase CEP data stream (not to a window). Database subqueries are quite different from regular subqueries (which are simply CCL queries nested within other CCL statements). Database subqueries use special syntax and follow very restricted rules.

A database subquery is continuous and conceptually executes every time its associated Sybase CEP query executes. However, as database information is often relatively static and may not require repetitious execution, performance can be improved by reducing the frequency of database subquery execution by the use of caching. For a discussion of caching external database data, please see the Sybase CEP Installation Guide.

Let's assume that there is an external SQL database called StockDatabase which has a table of historical stock prices called StockHistory with the following schema:

Column	Datatype	Description
symbol	String	The symbol of the stock.
avgprice	Float	The average historical price of the stock.

Note:

The type of SQL database is not important to this discussion because the SQL query involved will be common to all normal databases such as Oracle, MySQL, and so on. Whatever database is available at your installation can be accessed with a database subquery, as long as it has the necessary interface driver.

Here is an example of a simple database subquery using the StockTrades stream to pick up historical data from the StockHistory table:

INSERT INTO StockWithHistory 
SELECT current.symbol, current.price, history.avgprice 
FROM StockTrades AS current,
(DATABASE 'StockDatabase' SCHEMA (avgprice FLOAT)
[[SELECT avgprice FROM StockHistory
 WHERE symbol = ?current.symbol]]) AS history;

Notice the following things about a CCL query that contains a database subquery:

The database subquery is contained inside parentheses like any other subquery, but it starts with the word DATABASE followed by the name of the database as it is known to the interface software (for example, ODBC or, in the case of Oracle, the Oracle driver). The appropriate security for the external database must be provided in the interface software (see the Sybase CEP Administration Guide for details).
The schema of the returned data must be provided as the next part of the database subquery. The schema definition starts with the word SCHEMA and is followed either by a Sybase CEP schema filename or an internal schema definition enclosed in parentheses. The internal schema definition is simply a set of column names and data types separated by commas. In the example, only one column, avgprice, is returned from the historical database and so only one column is named in the schema.
The appropriate SQL statement that is needed to extract the data from the external database is enclosed in doubled square brackets. The syntax used here is standard SQL and may contain any valid SQL for the database that is being accessed.

Note:
Sybase CEP does no syntax checking of the SQL statement. If there are any errors in the statement, that will not be detected until the query is actually executed.
In order to provide the necessary retrieval condition for the data in the database (in this case, the value of the stock symbol to be looked up), values from the Sybase CEP data stream can be used inside the SQL query. The value of current.symbol (where current is the alias for the StockTrades stream) is passed to the subquery and it is inserted in the WHERE clause of the SQL statement. Substituted columns from the data stream must be preceded with a question mark (?) in order to be identified properly. Sybase CEP substitutes the value at run-time from the current row in the data stream before invoking the SQL query in the external database.
The database subquery is given an alias in the same way as other data streams and windows, using the AS clause, so that its schema columns can be referenced in the CCL query.
If the datatypes used by the external database do not exactly match the datatypes used by Sybase CEP, you may need to modify the datatypes of Sybase CEP data and/or external database data, before retrieving the database from a CCL query. For more specific information about CCL datatype conversion between CCL and other external databases, please see the Sybase CEP Reference.