Neoview SQL Reference Manual (R2.3)

[GROUP BY [col-expr]{colname | colnum} [,{colname |
colnum}]...]
[HAVING search-condition]
[[FOR] access-option ACCESS]
[IN {SHARE | EXCLUSIVE} MODE]
A simple-table can be a table value constructor. It starts with the VALUES keyword
followed by a sequence of row value constructors, each of which is enclosed in parentheses.
A row-value-const is a list of expressions (or NULL) or a row subquery (a subquery
that returns a single row of column values). An operand of an expression cannot reference
a column (except when the operand is a scalar subquery returning a single column value
in its result table).
The use of NULL as a row-value-const element is a Neoview SQL extension.
A simple-table can be specified by using the TABLE keyword followed by a table
name, which is equivalent to the query specification SELECT * FROM table.
A simple-table can be a query specification—that is, a SELECT statement consisting
of SELECT ... FROM ... with optionally the WHERE, SAMPLE, TRANSPOSE, SEQUENCE
BY, GROUP BY, and HAVING clauses. This form of a simple table is typically used in
an INSERT, CREATE VIEW, or DECLARE CURSOR statement.
WHERE search-condition
specifies a search-condition for selecting rows. See “Search Condition” (page 258). The
WHERE clause cannot contain an aggregate (set) function.
The search-condition is applied to each row of the result table derived from the table
reference in the FROM clause or, in the case of multiple table references, the cross-product
of result tables derived from the individual table references.
Each column you specify in search-condition is typically a column in this intermediate
result table. In the case of nested subqueries used to provide comparison values, the column
can also be an outer reference. See“Subquery” (page 260).
To comply with ANSI standards, Neoview SQL does not move aggregate predicates from
the WHERE clause to a HAVING clause and does not move non-aggregate predicates from
the HAVING clause to the WHERE clause.
SAMPLE sampling-method
specifies the sampling method used to select a subset of the intermediate result table of a
SELECT statement. Each of the methods uses a sampling size. The three sampling
methods—random, first, and periodic—are specified as:
RANDOM percent-size
directs Neoview SQL to choose rows randomly (each row having an unbiased probability
of being chosen) without replacement from the result table. The sampling size is
determined by using a percent of the result table.
FIRST rows-size [SORT BY colname [,colname]...]
directs Neoview SQL to choose the first rows-size rows from the sorted result table.
The sampling size is determined by using the specified number of rows.
PERIODIC rows-size EVERY number-rows ROWS [SORT BY colname [,colname]...]
directs Neoview SQL to choose the first rows from each block (period) of contiguous
sorted rows. The sampling size is determined by using the specified number of rows
chosen from each block.
SAMPLE is a Neoview SQL extension.
TRANSPOSE transpose-sett [transpose-set]... [KEY BY key-colname]
specifies the transpose-sets and an optional key clause within a TRANSPOSE clause.
You can use multiple TRANSPOSE clauses in a SELECT statement.
SELECT Statement 151