SELECT clause

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > SELECT statement  >

SELECT clause

Previous pageReturn to chapter overviewNext page

SELECT clause

As shown in the chart below, the <select-clause> uses the following items after the STREAM or TABLE keyword:

 <select-list> :=

   <select-item> { , <select-item> }...

<select-item> :=

   <select-expression> [ [ AS ] <simple-identifier> ]

<simple-identifier> :=

   <identifier> | <quoted-identifier>

<select-expression> :=

   <identifier> . *  | *  | <expression>

 

The select list can include the system-generated ROWTIME. As a system-generated column, ROWTIME behaves as a stream column, but is not stored in the stream.

Note: Unadorned ROWTIME (not qualified with a stream name) only be specified in the SELECT list or the ORDER BY clause.

SELECT clause syntax chart

select_06_seln_list_w_stream_or_table_source_w_ref

Syntax Chart for the SELECT-Clause: Sources and References

(To see where this clause fits, see the Select chart; for "window-specification", see window-stmt specification; for "join-reference," see the chart at JOIN clause.)

The body of the SELECT clause, after the STREAM, DISTINCT, ALL keywords if present, is a list of expressions.

Expressions

Each of these expressions may be:

a scalar expression
a call to an aggregate function, if this is an aggregating query (see GROUP BY)
a call to an analytic function, if this is not an aggregating query
the wildcard expression * expands to all columns of all relations in the FROM clause
the wildcard expression alias.* expands to all columns of the relation named alias
the ROWTIME operator
a CASE expression

Each expression may be assigned an alias, using the AS column_name syntax. This is the name of the column in the result set of this query. If this query is in the FROM clause of an enclosing query, this will be the name that will be used to reference the column. The number of columns specified in the AS clause of a stream_or_table_reference must match the number of columns defined in the original stream or table.

SQLstream has a few simple rules to derive the alias of an expression that does not have an alias. The default alias of a column expression is the name of the column: for example, EMPS.DEPTNO is aliased DEPTNO by default. Other expressions are given an alias like EXPR$0. You should not assume that the system will generate the same alias each time.

In a streaming query, aliasing a column AS ROWTIME has a special meaning: See Setting a row's timestamp.

Note: All streams have an implicit column called ROWTIME. This column may impact your use of the syntax 'AS t(c1, c2, ...)' that is now supported by SQL:2008. Previously in a FROM clause you could only write

SELECT ... FROM r1 AS t1 JOIN r2 as t2

 

but t1 and t2 would have the same columns as r1 and t2. The AS syntax enables you to rename r1's columns by writing the following:

SELECT ... FROM r1 AS t1(a, b, c)

 

(r1 must have precisely 3 columns for this syntax to work).

If r1 is a stream, then ROWTIME is implicitly included, but it doesn't count as a column. As a result, if a stream has 3 columns without including ROWTIME, you cannot rename ROWTIME by specifying 4 columns. For example, if the stream Bids has three columns, the following code is invalid.

SELECT STREAM * FROM Bids (a, b, c, d)

 

It is also invalid to rename another column ROWTIME, as in the following example.

SELECT STREAM * FROM Bids (ROWTIME, a, b)

 

because that would imply renaming another column to ROWTIME.

Note: Since SELECT can refer to a TABLE or a STREAM, a minor confusion can arise from a standard error message that occurs when the object can't be found: Even when the missing object is a stream, this standard message includes the phrase " ... Table '<name>' not found".

Syntax Chart for Expressions

expression

Syntax Chart for Expressions

See also Expressions and Literals.

CASE expression

The CASE expression enables you to specify a set of discrete test expressions and a specific return-value (expression) for each such test. Each test expression is specified in a WHEN clause; each return-value expression is specified in the corresponding THEN clause. Multiple such WHEN-THEN pairs can be specified.

CASE Syntax Chart

If you specify a comparison-test-expression before the first WHEN clause, then each expression in a WHEN clause is compared to that comparison-test-expression. The first one to match the comparison-test-expression causes the return-value from its corresponding THEN clause to be returned. If no WHEN clause expression matches the comparison-test-expression, the return-value is null unless an ELSE clause is specified, in which case the return-value in that ELSE clause is returned.

If you do not specify a comparison-test-expression before the first WHEN clause, then each expression in a WHEN clause is evaluated (left to right) and the first one to be true causes the return-value from its corresponding THEN clause to be returned. If no WHEN clause expression is true, the return-value is null unless an ELSE clause is specified, in which case the return-value in that ELSE clause is returned.

case

VALUES

VALUES uses expressions to calculate one or more row values, and is often used within a larger command. When creating more than one row, the VALUES clause must specify the same number of elements for every row. The resulting table-columns data-types are derived from the explicit or inferred types of the expressions appearing in that column. VALUES is allowed syntactically wherever SELECT is permitted. See also the discussion of VALUES as an operator, in the topic Query in this guide.

SYNTAX

VALUES ( expression [, ...] ) [, ...]

   [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]

 

VALUES is a SQL operator, on a par with SELECT and UNION, enabling the following types of actions:

You can write VALUES (1), (2) to return two rows each with a single anonymous column.
You can write VALUES (1, 'a'), (2, 'b') to return two rows of two columns.
You can name the columns using AS, as in the following example:

SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(x, y)

 

The most important use of VALUES is in an INSERT statement, to insert a single row:

 INSERT INTO emps (empno, name, deptno, gender)

   VALUES (107, 'Jane Costa', 22, 'F');

 

However, you can also insert multiple rows:

  INSERT INTO Trades (ticker, price, amount)

    VALUES ('MSFT', 30.5, 1000),

           ('ORCL', 20.25, 2000);

 

When you use VALUES in the FROM clause of a SELECT statement, the entire VALUES clause must be enclosed in parentheses, consistent with the fact that it operates as a query, not a table expression. See additional examples in the topic Relations in this guide.

Note: Using INSERT with streams engages some additional considerations as to rowtimes, pumps, and INSERT EXPEDITED. See the topic Insert in this guide.