ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 515
SQL Statements S - Z
SELECT
• The select list aggregate functions are computed for each group.
• ALLBASE/SQL allocates sort file space in /tmp, by default, or in the space specified
using the CREATE TEMPSPACE statement. The space is deallocated once the statement
completes.
• The query block is considered updatable if, and only if, it satisfies the following
conditions:
— No DISTINCT, GROUP BY, or HAVING clause is specified in the outermost SELECT
clause, and no aggregates appear in the select list.
— No INTO clause is specified.
— The FROM clause specifies exactly one table or view (contains no inner or outer
joins) and if a view is specified, it is an updatable view.
— For INSERT and UPDATE through views, the select list in the view definition must
not contain any arithmetic expressions. It must contain only column names.
— For DELETE WHERE CURRENT and UPDATE WHERE CURRENT operations,
the cursor definition must not contain subqueries.
— For noncursor UPDATE, DELETE, or INSERT, the view definition, or the WHERE
clause must not contain any subqueries referencing the target table in their FROM
clause.
Authorization
If you specify the name of a table, you must have SELECT or OWNER authority for the
table, or you must have DBA authority.
If you specify the name of a view, you must have SELECT or OWNER authority for the
view, or you must have DBA authority. Also, the owner of the view must have SELECT or
OWNER authority with respect to the view's definition, or the owner must have DBA
authority.
Examples
1. Simple queries
One value, the average number of days you wait for a part, is returned.
SELECT AVG(DeliveryDays)
FROM PurchDB.SupplyPrice
The part number and delivery time for all parts that take fewer than 20 days to deliver
are returned. Multiple rows may be returned for a single part.
SELECT PartNumber, DeliveryDays
FROM PurchDB.SupplyPrice
WHERE DeliveryDays < 20
2. Grouping
The part number and average price of each part are returned.