Neoview SQL Reference Manual (R2.3)

transpose-item-list AS transpose-col-list
specifies a transpose-set. You can use multiple transpose sets within a TRANSPOSE
clause. The TRANSPOSE clause generates, for each row of the source table derived from
the table reference or references in the FROM clause, a row for each item in each
transpose-item-list of all the transpose sets.
The result table of a TRANSPOSE clause has all the columns of the source table plus a
value column or columns, as specified in each transpose-col-list of all the transpose
sets, and an optional key column key-colname.
KEY BY key-colname
optionally specifies an optional key column key-colname. It identifies which expression
the value in the transpose column list corresponds to by its position in the
transpose-item-list. key-colname is an SQL identifier. The data type is exact
numeric, and the value is NOT NULL.
TRANSPOSE is a Neoview SQL extension. See “TRANSPOSE Clause” (page 281).
SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]] [,colname [ASC[ENDING] |
DESC[ENDING]]]...
specifies the order in which to sort the rows of the intermediate result table for calculating
sequence functions. You must include a SEQUENCE BY clause if you include a sequence
function in select-list. Otherwise, Neoview SQL returns an error. Further, you cannot
include a SEQUENCE BY clause if no sequence function is in select-list.
colname
names a column in select-list or a column in a table reference in the FROM clause
of the SELECT statement. colname is optionally qualified by a table, view, or correlation
name; for example, CUSTOMER.CITY.
ASC | DESC
specifies the sort order. The default is ASC. When Neoview SQL orders an intermediate
result table on a column that can contain null, nulls are considered equal to one another
but greater than all other nonnull values.
GROUP BY [col-expr] {colname | colnum} [,{colname | colnum}]...]
specifies grouping columns that define a set of groups for the result table of the SELECT
statement. The expression in the GROUP BY clause must be exactly the same as the expression
in the select list. These columns must appear in the list of columns in the table references in
the FROM clause of the SELECT statement.
If you include a GROUP BY clause, the columns you refer to in the select-list must be
grouping columns or arguments of an aggregate (or set) function.
The grouping columns define a set of groups in which each group consists of rows with
identical values in the specified columns. The column names can be qualified by a table or
view name or a correlation name; for example, CUSTOMER.CITY.
For example, if you specify AGE, the result table contains one group of rows with AGE equal
to 40 and one group of rows with AGE equal to 50. If you specify AGE and then JOB, the
result table contains one group for each age and, within each age group, subgroups for each
job code.
You can specify GROUP BY using ordinals to refer to the relative position within the SELECT
list. For example, GROUP BY 3, 2, 1.
For grouping purposes, all nulls are considered equal to one another. The result table of a
GROUP BY clause can have only one null group.
See “Considerations for GROUP BY” (page 156).
152 SQL Statements