CCL Expressions in the SELECT Clause

The SELECT clause may contain a list of one or more columns, which are published to the destination stream specified in the INSERT INTO clause. Each of the items in the select list may also be an expression.

While select list expressions typically reference column names, they are not limited to column names, and may not contain column names at all.

Select list expressions may include one or more of the expression elements listed in the previous sections. An item in the select list may be a simple literal, column name or a complex expression containing a mix of literals, column names, operators and functions.

Here is an example of an expression used in a select list. This query publishes the total dollar amount for each trade, and the trade identification number associated with the trade, to the TradeValues stream:

INSERT INTO TradeValues(id, tradevalue) 
SELECT tradeid, volume*price 
FROM StockTrades;

The TradeValues stream has the following schema:

Column	Datatype	Description
id	String	A unique sequence number that identifies each trade.
commission	Float	The commission value of this trade.
tradevalue	Float	The total value of this trade (volume * price).

Note the following important points about this query:

The first item in the select list selects the tradeid column of StockTrades and publishes it to the id column of TradeValues.
The second item in the select list of the query is an expression that multiplies the volume and price columns of the StockTrades stream to calculate the total dollar amount of the stock trade. This total value is written to the tradevalue column of the TradeValues stream.
The INSERT INTO clause can include a list of columns after the stream name enclosed in parentheses, which explicitly matches the items in the select list with corresponding columns in the TradeValues stream. This overrides the default order which publishes items in the select list to the destination stream left to right.
The TradeValues schema has an extra column (commission) which is not referenced in the query. Ignoring for the moment why this column might be present in this schema, note that if there are extra columns in the schema, they do not need to be referenced by the query as long as the query uses the explicit column list in the INSERT INTO clause. If there are extra unreferenced columns in the schema (such as commission), the query fills those columns with the NULL value.

Here is an example of what the input and output streams would look like for this query: