REMOTE subquery

Syntax

( REMOTE QUERY "service"  schema_clause 
( [ {value [AS] param} [, ...] ] )    [cache_clause] ) [AS] alias
                

Usage

A remote subquery is used in the FROM Clause: Database and Remote Subquery Syntax variation of the FROM clause. This specialized subquery invokes a Remote Function Call (RFC) that has been previously defined in the file c8-services.xml. The remote call is passed to one of a variety of remote services and returns data that is used within the CCL query. Remote calls can include a variety of protocols and interfaces, depending on your configuration and requirements. Examples of supported remote subquery configuration include SOAP and RMI.

The service parameter specifies the name of the service defined in c8-services.xml. This service definition contains all the information necessary to initialize, execute and shut down the service. The remote subquery simply executes the service whenever a row arrives in the stream to which the remote subquery is joined. Each time the service is executed it returns zero or more rows. For more information about configuring services in c8-services.xml, see the Sybase CEP Installation Guide .

The remote subquery is only used for function calls that retrieve data into Sybase CEP Engine. Calls that write data to external services must use the Remote Procedure statement.

A SCHEMA clause provides a CCL schema definition for the retrieved data and allows it to be used in CCL queries. CCL clauses within the CCL statement that refer to messages retrieved via the remote subquery must do so using the column names defined by the SCHEMA clause. Since the remote service cannot be preconfigured to send data using a specific data type, the CCL schema must be configured to receive any input, although it is allowed to generate errors when unexpected input is received.

The internal set of parentheses of a remote subquery contains a comma-separated sequence of one or more value expressions and parameter references. The parameter references are the parameters associated with the service, while the CCL values on the left specify the parameter value passed to the service. values can contain literals, column names from the data stream that is joined to the remote subquery, operators, scalar and other (but not aggregate) functions, and parentheses. If the external service takes no values, the remote subquery includes an empty set of parentheses.

An optional CACHE clause sets the criteria for caching rows and clearing the cache. See CACHE Clause for more information.

The entire remote subquery must also be aliased with an AS clause. This alias is used by any clauses in the CCL statement that refer to the remote subquery.

Within the FROM clause the remote subquery is always defined in a join with a single data stream. This join can be either an inner or outer join. Inner joins can be defined using either a comma-separated syntax, or with the use of the JOIN keyword. When the comma-separated syntax is used, the join condition s defined in a WHERE clause. When the JOIN keyword syntax is used, the use of the CCL ON clause with non-remote-subquery joins is not permitted.

Use the ON ERROR extension to handle errors that may cause Sybase CEP Engine to stop subquery execution.

( REMOTE QUERY "service"  schema_clause
( [ {value [AS] param} [, ...] ] )  [cache_clause] ON ERROR [error_insert_clause] CONTINUE ) 
[AS] alias

error_insert_clause: INSERT INTO error_stream_name error_select_list
error_stream_name: Name of an output stream or local stream.
error_select_list: SELECT {error_expression} [,...]
error_expression: A CCL expression.

The ON ERROR extension is optional. The error_insert_clause for the ON ERROR extension is also optional. If the ON ERROR extension is not provided and errors occur, the behavior of this clause is determined by the setting of the IgnoreErrors property in the c8-services.xml service configuration file. By default, this property is set to 'false', which means that if an error occurs, the query is aborted.

When the ON ERROR extension is provided without the error_insert_clause and there is an error executing the subquery, the error is ignored by the Sybase CEP Engine and the subquery execution is considered as having returned no results. Subsequent tuples arriving in the joining stream continue to trigger execution of the subquery. If these executions generate errors then these errors are also ignored by the Sybase CEP Engine.

When the ON ERROR extension is provided with the error_insert_clause and there is an error executing the subquery, a new tuple is inserted into the errorstream (specified by error_stream_name). This tuple contains values of the fields selected in the error_select_list clause. The timestamp of this tuple is the same as the timestamp of the tuple in the joining stream that triggered the execution of this subquery. These tuples are inserted in the errorstream in the same order as that of the original tuples in the joining stream that triggered the execution of this subquery. When there is no error, no tuples are inserted in the errorstream. The responses containing errors are not cached. If there is a subsequent tuple with the same binding key, it triggers another execution of the subquery.

The ON ERROR extension cannot be used with both an EXECUTE STATEMENT DATABASE/EXECUTE REMOTE PROCEDURE clause and a REMOTE SUBQUERY/PROCEDURE clause in the same CCL statement. This causes errors. To avoid errors, the CCL statements must be broken into separate statements using an intermediate local stream.

Set the MaxRetries parameter in the service definitions for RemoteService to determine how many times Sybase CEP Engine should retry statement execution after experiencing errors.

<Param Name="MaxRetries">some_number</Param>

If the MaxRetries parameter is set to a value greater than 0, say N, then every time there is an error in an Execute Statement or Subquery statement that subscribes to this service definition, the CEP Engine automatically retries the execution of that statement up to a maximum of N number of times. If the execution of the statement fails for all the N number of times, an error tuple is inserted into the errorstream.

The default value for MaxRetries is 0, that is, by default there is no retry.

Restrictions

Queries that include remote subqueries are subject to the following restrictions:

• A query that includes a remote subquery must have exactly one other data source.

• The non-remote-call data source must not be windowed.

• A left outer join that joins a remote subquery to a data stream must list the data stream on the left; a right outer join that joins a remote subquery to a data stream must list the data stream on the right.

• No full outer joins are allowed with remote subqueries.

• The use of the ON clause is not allowed with remote subqueries.

• CCL value expressions within the remote subquery cannot contain aggregate functions.

See Also

• Create Schema Statement

• Remote Procedure Statement

• CACHE

• SCHEMA

Example

This example executes a service called GetManager. Column values from the InOrders stream are passed to the EmployeeID and EmployeeName parameters of the service. Once executed, the joined results of the remote subquery and the data stream are passed to the OrdersWithManager stream.

INSERT INTO OrdersWithManager
SELECT *
FROM InOrders LEFT OUTER JOIN
     (REMOTE QUERY "GetManager" SCHEMA GetManagerRes
         (InOrders.EmployeeID AS EmployeeID,
          InOrders.EmployeeName AS EmployeeName)
     ) AS Manager;

INSERT INTO OutTradesWithVwap
SELECT    InTrades.*,    Vwap.*
FROM
    InTrades,
    (REMOTE QUERY "ExamplesDB"
     	SCHEMA (Vwap FLOAT)
     	[[
     		SELECT Vwap
        	FROM Vwap
        	WHERE Symbol = ?InTrades.Symbol
        	ORDER BY Ts DESC
        	LIMIT 1
     	]]
    		CACHE
        	MAXIMUM AGE 1 minute,
        ON ERROR
        INSERT INTO errorstream
        SELECT InTrades.*, ERROR_MESSAGE()
        CONTINUE;
) as Vwap