Oops. Freudian slip - I read this as MDC (Multi-dimensional Cluster) tables. For MQTs, you cannot create an MQT based on another MQT. The following sample code:
- create table dept_sum(deptno,
-
employees,
-
total_salary) as
-
(select workdept, count(*), sum(salary)
-
from colin.employee
-
group by workdept)
-
data initially deferred refresh deferred;
-
-
create table dept_avgsal(deptno, avg_salary) as
-
(select deptno,
-
total_salary/employees
-
from dept_sum)
-
data initially deferred refresh deferred;
yields error message:
SQL20058N The fullselect specified for the materialized query table
"COLIN.DEPT_AVGSAL" is not valid. Reason code = "2". SQLSTATE=428EC
2 The fullselect must not reference any of the following object
types:
* materialized query table
* staging table
* declared global temporary table
* typed table
* system catalog table
* view that violates any of the materialized query table
restrictions
* A protected table
* A nickname that was created using the DISALLOW CACHING
clause in the CREATE NICKNAME or ALTER NICKNAME statement
* A view that depends, either directly or indirectly, on a
protected table.
This is the behaviour for V9.5 and V9.7.
