NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-24
Considerations—SELECT
FOR UPDATE OF column [ , column ] ...
(only for dynamic SQL statements that are not subqueries) associates a list of
updateable columns with the statement so that a cursor can be declared for the
statement.
Considerations—SELECT
•
Authorization requirements
SELECT requires authority to read all views and tables referred to in the statement,
including the underlying tables of all shorthand views referred to in the statement.
•
Transactions
Queries on audited tables or on audited or mixed views must be performed in a TMF
transaction unless the SELECT statement specifies BROWSE ACCESS. See TMF
Transactions on page T-5 for more information.
•
Using views with SELECT
A view can be considered a select specification saved in a catalog. When a view is
referenced in a SELECT statement or a subquery, the select specification that
defines the view is combined with the statement or subquery. The combination can
cause the SELECT statement or subquery to be invalid.
If you receive an error message that indicates a problem but your SELECT
statement or subquery appears valid, check the view definition. For example, a view
named AVESAL includes column A defined as AVG(X). A SELECT statement that
contains MAX(A) in its select list is invalid because the select list actually contains
MAX (AVG(X)), and a function cannot have an argument that includes another
function.
To determine if a query using functions and GROUP BY clauses is valid, use
SQLCI to query the view definition in the TEXT column of the VIEWS catalog
table, as shown:
>> SET VARCHAR_WIDTH 225; --Sets wide report line
>> SELECT TEXT FROM $v.sv.VIEWS --From VIEWS table
+> WHERE VIEWNAME = "view-name"; --Fully qualified name
--in uppercase letters
A grouped view is a view defined with a CREATE VIEW AS clause that contains a
GROUP BY or HAVING clause that is not in a subquery, contains an aggregate
function in the select list, or refers to a grouped view in the FROM clause.
A shorthand view whose definition is based on a union of SELECT statements or
that contains a LEFT JOIN operator, cannot participate in another join operation. A
shorthand view whose definition contains any join operation cannot be the inner
table of a LEFT JOIN.
•
GROUP BY clause and the select list