MQT作成失敗

REFRESH IMMEDIATEのMQTを作成しようとすると

SQL20058N  マテリアライズ照会表 "SCS1.CUSTOMER_ORDER_SUMMER"
に指定された全選択は無効です。  SQLSTATE=428EC

が発生して原因がわからない。


RUNSTATS ON TABLE scs1.CUSTOMER_ORDER WITH DISTRIBUTION AND DETAILED INDEXES ALL



SELECT SUM(AMOUNT) AS TOTAL_SUM,
ORDER_DT
FROM CUSTOMER_ORDER
WHERE ORDER_DT BETWEEN '7/1/2003' AND '9/30/2003'
GROUP BY ORDER_DT


REFRESH DEFERREDのMQTは作成することができる。

CREATE TABLE CUSTOMER_ORDER_SUMMER AS
(
SELECT COUNT(*) AS CNT,
SUM(AMOUNT) AS TOTAL_SUM,
ORDER_DT
FROM CUSTOMER_ORDER
WHERE ORDER_DT BETWEEN '7/1/2003' AND '9/30/2003'
GROUP BY ORDER_DT
)
DATA INITIALLY DEFERRED REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION








CREATE TABLE CUSTOMER_ORDER_SUMMER AS
(
SELECT
COUNT(*) AS CNT_ALL,
COUNT(AMOUNT) AS CNT,
SUM(AMOUNT) AS TOTAL_SUM,
ORDER_DT
FROM CUSTOMER_ORDER
WHERE ORDER_DT BETWEEN '7/1/2003' AND '9/30/2003'
GROUP BY ORDER_DT
)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE ENABLE QUERY OPTIMIZATION



REFRESH IMMEDIATEのMQTは、めっちゃ条件が厳しい。

少しでも条件をはみ出ると
「SQL20058N  マテリアライズ照会表 "SCS1.CUSTOMER_ORDER_SUMMER"に指定された全選択は無効です。  SQLSTATE=428EC」
が発生する。


http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v8document.d2w/report?fn=r0000927.htm

When REFRESH IMMEDIATE is specified:

    * the fullselect must be a subselect
    * the fullselect cannot include a reference to a nickname (SQLSTATE 428EC)
    * the subselect cannot include:
          o functions that are not deterministic
          o scalar fullselects
          o predicates with fullselects
          o special registers 
    * a GROUP BY clause must be included in the subselect unless REPLICATED is specified, in which case a GROUP BY clause is not allowed.
    * The supported column functions are SUM, COUNT, COUNT_BIG and GROUPING (without DISTINCT). The select list must contain a COUNT(*) or COUNT_BIG(*) column. If the materialized query table select list contains SUM(X), where X is a nullable argument, the materialized query table must also have COUNT(X) in its select list. These column functions cannot be part of any expressions.
    * if the FROM clause references more than one table or view, it can only define an inner join without using the explicit INNER JOIN syntax
    * all GROUP BY items must be included in the select list
    * GROUPING SETS, CUBE and ROLLUP are supported. The GROUP BY items and associated GROUPING column functions in the select list must form a unique key of the result set. Thus, the following restrictions must be satisfied:
          o no grouping sets may be repeated. For example, ROLLUP(X,Y), X is not allowed because it is equivalent to GROUPING SETS((X,Y),(X),(X))
          o if X is a nullable GROUP BY item that appears within GROUPING SETS, CUBE, or ROLLUP, then GROUPING(X) must appear in the select list
          o grouping on constants is not allowed 
    * a HAVING clause is not allowed
    * if in a multiple partition database partition group, then either the partitioning key must be a subset of the GROUP BY items, or REPLICATED must be specified
    * if REPLICATED is specified, the table must have a unique key 





REFRESH IMMEDIATE materialized query table, 
the fullselect must conform to certain rules in addition to those already described. The fullselect must:

    * be a subselect with a GROUP BY clause or a subselect with a single table reference
    * not include DISTINCT anywhere in the select list
    * not include any special registers
    * not include functions that are not deterministic. 

If the query specified when creating a materialized query table does not conform to these rules, a warning is returned (SQLSTATE 01633).

End of FILE.