Tuple Join

Syntax 

SELECT target_list
  FROM stream_identifier_1 '[' {window_identifier_1|window_specification} ']' [[AS] stream_alias_1], 
       stream_identifier_2 '[' {window_identifier_2|window_specification} ']' [[AS] stream_alias_2]
  WHERE predicate
  [ERROR INTO stream_identifier];

Substitutable Fields

target_list

One or more entries, separated by commas, of the format target_list_entry.

target_list_entry

A value, of the format 

scalar_expression [[AS] output_field_identifier]

to be included in the result set returned by the statement. Values can be obtained from a tuple field or from a simple function. Optionally, the name for a value can be modified through an alias.

scalar_expression

A value that will be included in the output tuple. Fields from either input tuple can be included in the output tuple. If desired, the output field can be renamed using an alias.

output_field_identifier

An optional name (alias) for a value in the result set. Alias names must be used for values derived from functions.

stream_identifier

The streams containing the input tuples. Each stream must be windowed; the corresponding window is identified within the required brackets that follow each stream_identifier.

window_identifier

A named window specification previously declared with a CREATE WINDOW statement. Both streams can use the same window specification, or they can be associated with different window specifications. Note that the square braces are a required part of the syntax.

window_specification

A window specification defined within the FROM clause. Both streams can use the same window specification, or they can be associated with different window specifications. Note that the square braces are a required part of the syntax.

stream_alias

An optional alias name for a stream.

predicate

A conditional statement that establishes the constraints for the join, for example, stream_alias_1.field_identifier==stream_alias_2.field_identifier.

ERROR INTO Clause

You can append an ERROR INTO clause just before the closing semicolon. The StreamSQL ERROR INTO clause is analogous to the Enable Error Output Port check box for operators and adapters in EventFlow applications.

Use ERROR INTO with the name of a stream, which must already exist. This sets up an Error Port for this operator, which is much like a local catch mechanism for errors from this operator.

See Using Error Ports and Error Streams for a discussion of StreamBase error handling mechanisms.

Discussion

A tuple-based join is a SELECT statement in which the FROM clause includes two windowed stream entries. Within the window specifications, ADVANCE must have the value one. The WHERE clause establishes the constraints that will be used to select tuples for joining.

If the window specification associated with a stream includes a TIMEOUT clause, tuples will be dropped from the stream's buffer after the timeout period has elapsed and will no longer be available for matching.

A tuple-based join generates a stream and can be used anywhere a stream expression is acceptable. As an alternative, the output could be captured in a stream, as illustrated in the following code fragments. 

CREATE [OUTPUT] STREAM stream_identifier;
SELECT ... INTO stream_identifier;
CREATE [OUTPUT] STREAM stream_identifier AS
  SELECT ...;

Or, for an OUTPUT STREAM

SELECT ... => CREATE OUTPUT STREAM stream_identifier;

Related Topics

Back to Top ^