DB2 accepts a materialized view based on an "OUTER JOIN" query, as long it doesn't refresh automatically (REFRESH IMMEDIATE).
I cannot find a simple solution to get round this problem. Perhaps with (a lot of) triggers, but it isn't really nice...
As example, my database is
CREATE TABLE PHONEUSER (USERID INT NOT NULL PRIMARY KEY, NAME VARCHAR(30)); CREATE TABLE PHONECALL (CALLID INT NOT NULL PRIMARY KEY, USERID INT, HOUR TIMESTAMP); INSERT INTO PHONEUSER VALUES (1, 'Olivier'); INSERT INTO PHONEUSER VALUES (2, 'Arthur'); INSERT INTO PHONECALL VALUES (1,1,CURRENT_TIMESTAMP);
and I want each call of each user. If an user has never called, I want a
null value for him (it has sense in my application):
SELECT PU.USERID, CALLID, HOUR FROM PHONEUSER PU LEFT JOIN PHONECALL PC ON PC.USERID=PU.USERID; USERID CALLID HOUR ------ ------ ----------------------- 2 null null 1 1 2013-04-09 16:36:08.952
This query runs well, but cannot be used for an MQT with
CREATE TABLE CALLLIST AS (SELECT PU.USERID, CALLID, HOUR FROM PHONEUSER PU LEFT JOIN PHONECALL PC ON PC.USERID=PU.USERID) DATA INITIALLY DEFERRED REFRESH IMMEDIATE ; The fullselect specified for the materialized query table CALLLIST is not valid. Reason code = "10".. SQLCODE=-20058, SQLSTATE=428EC, DRIVER=4.7.85