There are restrictions!
Here an excerpt from the SQL Reference (CREATE TABLE Statement):
When a materialized query table is defined with ENABLE QUERY OPTIMIZATION,
the following additional select-statement restrictions apply:
? Must not include any special registers.
? Must not include any non-deterministic functions.
? The ORDER BY clause is allowed, but is only used by REFRESH. It may
improve locality of reference of data in the materialized query table.
? If the subselect references a view, the select-statement in the view
definition must satisfy the preceding restrictions.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin
Taylor
Sent: Monday, 4 March 2024 16:13
To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 428EC on current_date in MQT
I'm trying to create a materialized query table (MQT) that uses
current_date, but it's giving me an error. It works fine if I replace
current_date with a literal, so I know current_date is the problem.
SQLSTATE 428EC
8 -- Refers to a special register or global variable.
Am I missing something here? That seems like an odd limitation for MQTs.
TIA
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
