Friends,
Say I have the following UDF:
CREATE FUNCTION GET_MONTH(P_DATE DATE)
RETURNS INTEGER
INHERIT SPECIAL REGISTERS
SPECIFIC GET_MONTH
DETERMINISTIC
BEGIN ATOMIC
RETURN MONTH(P_DATE);--
END;
and that I'd like to use it in the following MQT:
CREATE TABLE
UDF_MQT
AS
(
SELECT
GET_MONTH()
FROM
SYSIBM.SYSDUMMY1
)
DATA INITIALLY DEFERRED REFRESH DEFERRED
NOT LOGGED INITIALLY;
When I try to create the MQT, I get an error SQL20058N, reason code 4:
(SQL20058N The fullselect specified for the materialized query table
<is not valid. Reason code = "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
Volume 2 of the SQL Reference has the following under CREATE TABLE:
When REFRESH DEFERRED or REFRESH IMMEDIATE is specified (as if,
according to the syntax diagram under refreshable-table-options, there
are any other kinds :-), the fullselect cannot include functions that
have any of the following attributes:
EXTERNAL ACTION
LANGUAGE SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA
Anyone know of a way around this, while still using a SQL scalar
function? This limitation seems very...limiting.
Thanks,
--Jeff
