Thanks, Serge. While not the answer I hoped for, at least it's
conclusive. As to my "other kind" comment, sorry--that was a cheap
shot.

BTW, my colleagues who went to your "SQL on Fire" sessions had great
things to say about it. I wish I could have attended the conference,
but going is a privilege we must rotate :-).

--Jeff

Serge Rielau wrote:
> jefftyzzer wrote:
> > 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.
> There is no way around it. The problem lies in the routing. Gets pretty
> pretty with SQL functions. Rather then allowing to create the beast and
> never route to it we thought it better to block up front.
> The "other kind" is: DEFINITION ONLY btw.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> WAIUG Conference
> http://www.iiug.org/waiug/present/Fo...Forum2006.html