On Jan 7, 12:51 pm, amitabh.me...@gmail.com wrote:

If you use a staging table you must obey the same rules as for an MQT
refresh immediate:

db2 "? SQL20058N"

SQL20058N The fullselect specified for the materialized query
         table "<table-name >" is not valid. Reason code =
         "<reason-code>".

Explanation:

The materialized query table definition has specific rules
regarding the contents of the fullselect. Some rules are based on
the materialized query table options (REFRESH DEFERRED or REFRESH
IMMEDIATE) while others are based on whether or not the table is
replicated. The fullselect in the CREATE TABLE statement that
returned this condition violates at least one of the rules as
described in the SQL Reference.

This error may occur during the creation of a staging table. In
such a case, the error applies to the query used in the
definition of the materialized query table with which the staging
table is associated.

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:
[...]
7 When REFRESH IMMEDIATE is specified:

o   the materialized query table must not contain duplicate rows

o   when a GROUP BY clause is specified, all GROUP BY items must
   be included in the select list

o   when a GROUP BY clause is specified which contains GROUPING
   SETS, CUBE, or ROLLUP, then no grouping sets can be repeated,
   and if C is a nullable GROUP BY item that appears within
   GROUPING SETS, CUBE, or ROLLUP, then GROUPING(C) must appear
   in the select list

o   when no GROUP BY clause is present, then the underlying
   tables must each have at least one unique key defined, and
   all columns of these keys must appear in the select list of
   the materialized query table definition
[...]
7 Create the materialized query table as REFRESH DEFERRED, or

o   correct the CREATE TABLE statement to ensure all GROUP BY
   items are in the select list

o   correct the GROUP BY clause to ensure there are no duplicate
   grouping sets

o   remove the nullable column, C, or add GROUPING(C) in the
   select list

o   correct the CREATE TABLE statement to ensure at least one
   unique key from each table referenced in the query appears in
   the select list

Why do you need an MQT in the first place, can't you use a view
instead?

CREATE VIEW OUT_table AS
   SELECT CASE DT WHEN '1900-01-01-00.00.00.000000'  THEN null ELSE
DT END DT,
                 CASE NUM WHEN  -999999 THEN null ELSE NUM END  NUM,
                 CASE ST WHEN  'my_null'  THEN null ELSE ST END ST
   FROM base_table;

Another option is to add generated columns for the transformations,
but I would aim for the view

/Lennart