SELECT clause

Syntax

SELECT { expression [AS column | alias | parameter ] } [, ...]

Usage

The SELECT clause inside a Query statement, Database statement, or Remote Procedure statement specifies a select list of one or more items. Rows from the data sources listed in the FROM clause are passed to the SELECT clause after being filtered by the WHERE clause, if one is specified. The results of the expressions in the list are processed by other clauses (if any). If, after this processing, the CCL statement generates results, the results are handled differently, depending on the type of CCL statement in which they are used:

• In a Query statement, the results are published to the destinations specified in the query's INSERT clause.

• In a Database statement, the SQL statements specified in the EXECUTE STATEMENT DATABASE clause are executed against the external database. The SQL statements usually use the processed select list results as their input.

• In a Remote Procedure statement, the results are passed to the specified parameters (as specified in the AS clause of each expression) in the external service indicated by the EXECUTE REMOTE PROCEDURE clause.

The following rules apply to the select list:

• The expression within each select list item can contain literals, column names from one of the statement's data sources listed in the FROM clause, operators, scalar and miscellaneous functions, and parentheses. Query statement and Database statement select list expressions can also include aggregate functions, but Remote Procedure statement select lists cannot.

  Alternately, expression can be specified with a "select all" asterisk (*) character, which is equivalent to a list of all column values from all data sources listed in the statement's FROM clause, listed in order from left to right, or as data-source.*, which is equivalent to a list of all column values from the specified data source (where data-source is the name or alias of one of the data sources listed in the FROM clause). Note that expressions using the asterisk involve certain limitations when used with the Database statement or Remote Procedure statement, as discussed in the RESTRICTIONS section.

• The following rules apply to all expressions that do not include an asterisk:

  • When used in a Query statement, each list item can specify an AS output column reference subclause indicating the column within the destination, to which the select list item should be published. (The results of the AS subclause here are the same as the usage of explicit column specification in the INSERT clause.) The AS subclause must be used either for all or for none of the non-asterisk items in the select list. In the absence of an AS subclause, or a list of column names in the INSERT clause, items are published from left to right.

  • When used in a Database statement, each non-asterisk list item must specify an AS output-alias subclause. When the SQL query in the EXECUTE STATEMENT DATABASE clause refers to a column in the CCL SELECT clause, the SQL reference must use the column's alias, in the form ?output-alias.

  • When used in a Remote Procedure statement, each non-asterisk list item must specify an AS parameter subclause. The result of each expression is then passed to the specified parameter in the external service.

Automatic Schema Creation

Under certain circumstances the SELECT clause of a Query statement can be used to create a schema for a previously undefined data stream. The data stream can then be used in subsequent CCL statements in the current module. The automatic stream and schema creation can only be performed on a data stream that does not receive data from outside the current project.

The first Query statement that invokes the data stream must list the desired stream name in its INSERT clause, and then list the desired columns for the stream either in the INSERT clause, or in the SELECT clause. When the SELECT clause is used to define the stream's columns the following syntax is used:

SELECT { expression AS column } [, ...]

The use of the "select all" asterisk (*) is an exception to this syntactical requirement. When the first Query statement that included the data stream uses the "select all" asterisk (*), the column names for the destination stream are automatically inferred from the query's data source.

The stream schema and data types are then automatically created by Sybase CEP Engine. The data types for the columns of the automatic schema are determined by the data types of the data sources in the first Query statement that uses the stream as its destination.

Restrictions

• If the SELECT clause of a Database Statement or Remote Procedure Statement uses the "select all" expression (*) and the FROM clause of the statement lists multiple data sources, the data sources cannot share any column names.

• When the select list of a Database statement or Remote Procedure statement contains a list of expressions separated by commas, all non-asterisk expressions must include an AS subclause. In a Query statement containing a comma-separated list of expressions, either all or none of the non-asterisk expressions must contain an AS subclause.

• CCL expressions within the select list of a Remote Procedure statement cannot contain aggregate functions.

See Also

• Query Statement

• Database Statement

• Remote Procedure Statement

• FROM Clause: Comma-Separated Syntax

• FROM Clause: Database and Remote Subquery Syntax

• FROM Clause: Join Syntax

• INSERT

Examples

Here is an example of the simplest SELECT clause, used by a Query statement that selects all the columns from its data source:

INSERT INTO OutStream
SELECT *
FROM Trades
WHERE Trades.Price = 100;

The following example includes a more complex Query statement SELECT clause:

INSERT INTO StepCompletionCounts
SELECT 
    COUNT(C.ScenarioID) AS TheCount,
    C.ScenarioID AS ScenarioID,
    C.StepID AS StepID
FROM 
    CompletedSteps AS C KEEP 10 SECONDS
GROUP BY
    C.ScenarioID, C.StepID
OUTPUT
    EVERY 10 SECONDS;

Here is an example of a SELECT clause in a Database statement:

EXECUTE STATEMENT DATABASE "MyDB"
  [[UPDATE Inventory 
    SET Inventory.Quantity = ?Quantity 
    WHERE Inventory.ItemID =?ID]]
SELECT
  StreamIn.ItemID AS ID, StreamIn.Quantity AS Quantity
FROM StreamIn;

Here is an example of a SELECT clause in a Remote Procedure statement:

EXECUTE REMOTE PROCEDURE "Search"
SELECT Name AS SearchString
FROM Clients;