Neoview SQL Reference Manual (R2.4)

The SELECT query becomes equivalent to:
Select * from t order by a;
ORDER BY in a View Definition With User Override
If a SELECT query contains an explicit ORDER BY clause, it overrides the ORDER BY clause
specified in the view definition.
For example:
Create view v as select a,b from t order by a;
Select * from v order by b;
In this example, order by b overrides the order by a specified in the view definition.
The SELECT query becomes equivalent to:
Select a,b from t order by b;
Nested View Definitions
In case of nested view definitions, the ORDER BY clause in the topmost view definition overrides
the ORDER BY clause of any nested view definitions.
For example:
Create view v1 as select a,b from t1 order by a;
Create view v2 as select a,b from v1 order by b;
Select * from v2;
In this example, the ORDER BY specified in the definition of view v2 overrides the ORDER BY
specified in the definition of view v1.
The SELECT query becomes equivalent to:
Select a,b from (select a, b from t) x order by b;
Examples of CREATE VIEW
• This example creates a view on a single table without a view column list:
CREATE VIEW SALES.MYVIEW1 AS
SELECT ordernum, qty_ordered FROM SALES.ODETAIL;
• This example creates a view with a column list:
CREATE VIEW SALES.MYVIEW2
(v_ordernum, t_partnum) AS
SELECT v.ordernum, t.partnum
FROM SALES.MYVIEW1 v, SALES.ODETAIL t;
• This example creates a view from two tables by using an INNER JOIN:
CREATE VIEW MYVIEW4
(v_ordernum, v_partnum) AS
SELECT od.ordernum, p.partnum
FROM SALES.ODETAIL OD INNER JOIN SALES.PARTS P
ON od.partnum = p.partnum;
Vertical Partition Example
This example creates three logical vertical partitions for a table, vp0, vp1, and vp2 and then
creates a view vp to access them.
A view can be used to obtain a composite representation of a set of closely related tables. In the
following example tables vp0, vp1 and vp2 all have a key column a. This key column is known
to contain identical rows for all three tables. The three tables vp0, vp1 and vp2 also contain
columns b, c and d respectively. We can create a view vp that combines these three tables and
provides the interface of columns a, b, c and d belonging to a single object.
CREATE VIEW Statement 103