SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-198
Considerations for Select List
permitted on different columns. Exceptions to this rule are SQL/MX extensions for
which DISTINCT is unnecessary and include:
•
MIN and MAX functions
•
Aggregate functions with unique columns or expressions, such as primary keys or
UNIQUE constraints
These aggregate functions do not contribute to the count of DISTINCT aggregate
functions in the query, thus permitting you to specify them more than once or in
addition to another DISTINCT aggregate function in a query.
Considerations for Select List
•
If a column in a select list has datetime or interval data type, you must use the
CAST function to convert the column to a character string in an embedded SQL
program. You must also specify the length of the target host variable (or the length
– 1 in the case of a C program) as part of the CAST conversion.
•
The * and corr.* forms of a select-list specification are convenient for use
in MXCI. However, such specifications make the order of columns in the SELECT
result table dependent on the order of columns in the current definition of the
referenced tables or views.
•
A col-expr is a single column name or a derived column. A derived column is an
SQL value expression; its operands can be numeric, string, datetime, or interval
literals, columns, functions (including aggregate functions) defined on columns,
scalar subqueries, CASE expressions, or CAST expressions. Any single columns
named in col-expr must be from tables or views specified in the FROM clause.
For a list of aggregate functions, see Aggregate (Set) Functions on page 9-1.
•
If col-expr is a single column name, that column of the SELECT result table is a
named column. All other columns are unnamed columns in the result table (and
have the (EXPR) heading) unless you use the AS clause to specify a name for a
derived column.
•
You can specify SYSKEY as an item in the select-list. A SYSKEY is a primary
key defined by NonStop SQL/MX rather than by the user; it is the first column in a
table, and its data type depends on the organization of the table’s underlying file:
key-sequenced or entry-sequenced. (NonStop SQL/MX supports only key-
sequenced tables.) If you want to select the SYSKEY column from more than one
result table, you must qualify SYSKEY; for example, EMPLOYEE.SYSKEY.
Considerations for SEQUENCE BY
If you include both SEQUENCE BY and GROUP BY clauses in the same SELECT
statement, the values of the sequence functions must be computed first and then
become input for the aggregate functions in the statement. For more information, see
SEQUENCE BY Clause on page 7-18.
C/COBOL