Raj wrote:
I am trying to create an MQT using the following select statement and
refresh immediate option
SELECT c1,
c2,
MIN(c3) AS c3,
MIN(c4) AS c4
FROM t1
WHERE c5= 'xyz'
GROUP BY c1,c2
error:
----------------------------------------------------------------------
---------------------------------------------------------------- The
command was processed as an SQL statement because it was not a valid
Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
is not valid. Reason code = "4". SQLSTATE=428EC
db2 "? SQL20058N "
----------------------------------------------------------------------
---------------------------------------------------------------- 4
The fullselect must not contain references to functions that:
o depend on physical characteristics of the data, for example
DBPARTITIONNUM, HASHEDVALUE
o are defined as EXTERNAL ACTION
o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
MODIFIES SQL DATA
Response
----------------------------------------------------------------------
---------------------------------------------------------------- 4
Correct the CREATE TABLE statement to ensure that no unsupported
functions are referenced.
----------------------------------------------------------------------
----------------------------------------------------------------
Refresh deferred completes successfully.. Is there a limitation in db2
in selecting min's in a refresh immediate MQT?
Indeed there is. From the CREATE TABLE documentation:
When a GROUP BY clause is specified, the following considerations apply:
When REFRESH IMMEDIATE is specified:
[... lots of other rules ...]
* 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.
* A HAVING clause is not allowed.
[... lots more rules ...]
Sorry! Looks like you'll have to stick with DEFERRED unless there's
another way around this?
Dave.
--
