ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 369
SQL Statements A - D
CREATE VIEW
constraint.
• You cannot use an ORDER BY clause when defining a view.
• If the IN
DBEFileSetName clause is specified, but the view owner does not have
SECTIONSPACE authority for the specified DBEFileSet, a warning is issued and the
default SECTIONSPACE DBEFileSet is used instead. (Refer to syntax for the GRANT
statement and the SET DBEFILESET statement.)
Authorization
You can create a view if you have SELECT or OWNER authority for the tables and views
mentioned in the FROM clause of the SELECT statement or if you have DBA authority. To
operate on a table on which the view is based, the authority you need depends on whether
or not you own the view. The authority needed in either case is specified as follows:
• If you own the view, you need authority for the table(s) or view(s) on which the view is
based.
• If you do not own the view, you need authority granted specifically for the view. To
specify a
DBEFileSetName for a view, the view owner must have SECTIONSPACE
authority on the referenced DBEFileSet.
Examples
1. The following view provides information on the value of current orders for each vendor.
Because the view is derived by joining tables, the base tables cannot be updated via this
view.
CREATE VIEW PurchDB.VendorStatistics
(VendorNumber,
VendorName,
OrderDate,
OrderQuantity,
TotalPrice)
AS SELECT PurchDB.Vendors.VendorNumber,
PurchDB.Vendors.VendorName,
OrderDate,
OrderQty,
OrderQty*PurchasePrice
FROM PurchDB.Vendors,
PurchDB.Orders,
PurchDB.OrderItems
WHERE PurchDB.Vendors.VendorNumber =
PurchDB.Orders.VendorNumber
AND PurchDB.Orders.OrderNumber =
PurchDB.OrderItems.OrderNumber
IN PurchDBFileSet
2. The following view is updatable because it is created from one table. When the table is
updated through the view, column values in the SET or VALUES clause are checked
against the WHERE clause in the view definitions.