Neoview SQL Reference Manual (R2.4)

• Must be a single block SELECT-FROM-WHERE-GROUPBY query (for example, ORDER
BY is not allowed). There is one exception to this rule; nested blocks are allowed when the
inner block’s purpose is to extract a group by column (using a built-in function like substring
or extract).
• Non-repeatable expressions (for example, current_time) are not supported for all types
of aggregate materialized views.
DML Operations on Materialized Views
All update operations (INSERT, UPDATE, DELETE) are blocked on all materialized views except
for the DELETE operation on ON REQUEST materialized views.
Examples of CREATE MATERIALIZED VIEW
• This is an example of an MAV:
CREATE MATERIALIZED VIEW PUBSCH.DETAIL_MV
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT PUBSCH.DETAIL_TABLE.ORDERNUM, SUM(PUBSCH.DETAIL_TABLE.QUANTITY) AS TOTAL_AVAIL
FROM PUBSCH.DETAIL_TABLE
WHERE PUBSCH.DETAIL_TABLE.ORDERNUM > 1
GROUP BY PUBSCH.DETAIL_TABLE.ORDERNUM;
• This is an example of an MJV:
CREATE MATERIALIZED VIEW sales_store
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT price, partkey, timekey, store.nam
FROM sales, store
WHERE sales.storekey = store.storekey;
• This is an example of a MAJV:
CREATE MATERIALIZED VIEW sales_by_day_category
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT part.category, time.day, SUM(price) sum_price
FROM sales, part, time
WHERE sales.timekey = time.timekey
AND sales.partkey = part.partkey
GROUP BY part.category, time.day;
66 SQL Statements