Identifies a single destination for the results of a continuous query created by a Query statement.
This form of the INSERT clause is used as the first clause of a Query statement to create a branch, in which rows from the query are inserted into one of several destinations, depending on the specified conditions. (For queries where no branching is required, use the INSERT INTO form of the INSERT clause instead.) Rows are published to one of the specified destinations any time the query executes and produces output. All destinations must be named windows, or local or output data streams.
INSERT WHEN contains one or more WHEN subclauses, each of which specifies a condition tested against the query's select list for every incoming row. The specified conditions are similar to CASE expressions, and are subject to similar restrictions.
The select list of every incoming row is tested against the condition of the first (left-most) WHEN subclause. If the condition is not met, and other WHEN subclauses are present, the row is tested against the conditions of the subsequent WHEN subclauses from left to right, until a condition is found to be true. Once a condition is met, the row is inserted into the destination specified by the corresponding WHEN subclause. The conditions of any subsequent WHEN subclauses are not considered for the row in question. If the incoming row meets none of the specified conditions, and an ELSE subclause is present, the row is published to the destination specified by the ELSE subclause. If no ELSE subclause is specified, the row is discarded. As with the INSERT INTO form, INSERT WHEN publishes results only if the query as a whole generates output.
A destination is specified by every WHEN subclause and by the ELSE subclause (if one is present) of the INSERT WHEN. The same destination can be associated with multiple WHEN subclauses, and/or with the ELSE subclause.
Every destination specified by the INSERT WHEN can also optionally include one or more column names referring to the schema of a destination stream or window. As in the case of INSERT INTO, these specify the columns to which data should be published from left to right. Alternately, as in the case of INSERT INTO, select list items can be explicitly matched with the destination's column names by using the AS output column reference syntax in the SELECT clause.
The usage and restrictions on the use of destination schemas and specified column names in the WHEN and ELSE subclauses of the INSERT WHEN is the same as for INSERT INTO, except for the following differences:
Every item in the select list of the query's SELECT clause must have a corresponding column in the schema of at least one (but not necessarily all) of the destinations specified by the INSERT WHEN.
The schema corresponding to a destination specified by a given WHEN or ELSE subclause, must have columns corresponding to some, but not necessarily all, items in the query's select list. Any select list items that do not correspond to a column in the destination are discarded.
If a given WHEN or ELSE subclause in an INSERT WHEN includes a list of column names referring to its destination, the number of specified columns cannot exceed the number of items in the query's select list, but can contain fewer columns than items in the select list. Any select list items that do not correspond to a column in the destination are discarded.
As with INSERT INTO, the WHEN and ELSE subclauses of the INSERT WHEN can be used to create names and schemas for previously undefined data streams. See the AUTOMATIC SCHEMA CREATION USING INSERT INTO section under INSERT INTO for more information about the usage and restrictions of automatic schema creation with the INSERT clause.
The automatic schema feature can be used in either the WHEN subclauses or the ELSE subclause of INSERT WHEN. When used in a WHEN subclause, the automatic schema feature uses the following syntax:
WHEN condition THEN stream ( column [, ...] )In the ELSE subclause, the following syntax is used:
ELSE stream ( column [, ...] )Input data streams cannot be designated as query destinations. An input stream is a type of data stream that receives data from outside the current query module, either by way of bindings, or through a connection to an input adapter.
No duplicate column references are permitted inside a single WHEN or ELSE subclause of the INSERT WHEN.
The query that defines the automatic schema for a stream must appear in the query module before any CCL statement that uses the stream, either as a data source or destination.
Query Statement
The following example separates the Dept stream into three destinations, based on the values in the Loc column.
INSERT
WHEN Loc IN ('New York', 'Boston') THEN
Dep_east
WHEN Loc IN ('Chicago') THEN
Dep_mid
ELSE
Dep_west
SELECT Deptno, Dname, Loc
FROM Dept;