1.1.1

Note: The query that you provide in a CREATE VIEW statement cannot use any of the following SQL
clauses:
• ORDER BY Clause
• OFFSET and FETCH FIRST Clauses
The view owner automatically gains the SELECT privilege on the view. The SELECT privilege cannot be
revoked from the view owner. The distributed member owner booting up the process automatically gains the
SELECT privilege on the view and is able to grant this privilege to other users. The SELECT privilege cannot
be revoked from the distributed member owner. CREATE SCHEMA on page 490 provides additional ownership
details.
The view owner can only grant the SELECT privilege to other users if the view owner also owns the underlying
objects.
If the underlying objects that the view references are not owned by the view owner, the view owner must be
granted the appropriate privileges. For example, if the authorization ID user2 attempts to create a view called
user2.v2 that references table user1.t1 and function user1.f_abs(), then user2 must have the SELECT privilege
on table user1.t1 and the EXECUTE privilege on function user1.f_abs().
The privilege to grant the SELECT privilege cannot be revoked. If a required privilege on one of the underlying
objects that the view references is revoked, then the view is dropped across the distributed system.
A view definition can contain an optional view column list to explicitly name the columns in the view. If there
is no column list, the view inherits the column names from the underlying query. All columns in a view must
be uniquely named.
Views and Colocation Requirements
If you create a view using a query on partitioned tables, any columns used in joins or subqueries must be colocated
on the same SQLFire members. See Query Capabilities and Limitations on page 710.
Note that SQLFire checks for colocation requirements only when a query is executed against the view, rather
than when the view is created. It is possible to successfully create a view that cannot be used due to colocation
requirements of the underlying query.
Statement dependency system
View definitions are dependent on the tables and views referenced within the view definition. DML statements
that contain view references depend on those views, as well as the objects in the view definitions that the views
are dependent on. Statements that reference the view depend on indexes the view uses; which index a view uses
can change from statement to statement based on how the query is optimized.
Example
CREATE VIEW SAMPLE.V1 (COL_SUM, COL_DIFF)
AS SELECT COMM + BONUS, COMM - BONUS
FROM SAMPLE.EMPLOYEE;
CREATE VIEW SAMPLE.EMP_RES (RESUME)
AS VALUES 'Delores M. Quintana', 'Heather A. Nicholls',
'Bruce Adamson';
DECLARE GLOBAL TEMPORARY TABLE
Defines a temporary table for the current connection uniquely identified within the local member.
511
SQL Language Reference