Solved

UDF in MQT definition

Posted on 2008-10-06
3
748 Views
Last Modified: 2013-12-06
Can an MQT definition include a UDF value.
Like this ...
-----------------------------------------
CREATE TABLE MyLIB.MyMQT
(fldA, fldB, fldC)
AS
(
    SELECT
        T1.X as fldA,
        MyLib.MyUDF(T1.Y) as fldB,
        T1.Z as fldC
    FROM MyLib.Table01 T1
)
DATA INITIALLY IMMEDIATE
REFRESH DEFERRED
MAINTAINED BY USER
ENABLE QUERY OPTIMIZATION;
-----------------
    I get error ...
    SQL State: 428EC
    Vendor Code: -20058
    Message: [SQ20058] Subselect for materialized query table SALINDMQ0 not valid for reason code 6. Cause . . . . . :
        6 -- Refers to a function that has an external action or that is not deterministic
-----------------
Here's the function definition ....
-----------------
CREATE FUNCTION MyLIB.MyUDF (
     PARAM1 VARCHAR(12) )
     RETURNS NUMERIC(10, 2)  
     LANGUAGE SQL
     SPECIFIC MyLIB.MyUDF
     DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     DISALLOW PARALLEL
     BEGIN
     DECLARE RET NUMERIC ( 10 , 2 ) ;
     SELECT SUM ( TQ . MyVal )
          INTO RET
          FROM MyLib.TablQQQQ TQ
          WHERE TQ.LookUp = PARAM1 ;
     RETURN RET ;
END  ;
0
Comment
Question by:volking
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22656949
the udf has to be deterministic
when you create the udf use the determenistic keywork
deterministic means that every time you pass the same parameters the function will return the same answer
for example - substring is deterministic
function such as getdate() (which is not in db2 but you can guess what it does) is not deterministic

you can only use deterministic udf in your mqt definition
0
 
LVL 5

Author Comment

by:volking
ID: 22658953
@Momi
But I do declare UDF as Deterministic ... don't I?

CREATE FUNCTION MyLIB.MyUDF (
     PARAM1 VARCHAR(12) )
     RETURNS NUMERIC(10, 2)  
     LANGUAGE SQL
     SPECIFIC MyLIB.MyUDF
->     DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     DISALLOW PARALLEL
     BEGIN ...........
0
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 22664603
volking:

You do "declare" it as deterministic, but it isn't deterministic. It's perfectly possible for the result value to change every time the function is called with the same PARAM1 value. I.e., the PARAM1 value does NOT result in a deterministic value because the internal SELECT can return a different SUM ( TQ . MyVal ) as the rows in MyLib.TablQQQQ are changed.

Your declaration contradicts the coding in the UDF. Actually, I'm surprised you didn't get at least a warning about it when you created the UDF.

Tom
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
In a recent article here at Experts Exchange (http://www.experts-exchange.com/articles/18880/PaperPort-14-in-Windows-10-A-First-Look.html), I discussed my nine-month sandbox testing of the Windows 10 Technical Preview, specifically with respect to r…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now