Re: 428EC on current_date in MQT

Makes sense to me...

The idea behind a MQT is that the DB would be able to redirect a query to
the MQT automatically

For example, you find your users running a lots of a query like so
select division, sum(sales) as totSales
from myTable;

So you create an MQT...

Now whenever the users run the above query, the Db would use the MQT.

One can also query the MQT directly, but the point is your users don't have
to know about it or make changes to what they are doing.

Thus it makes sense to me that the DB doesn't allow the use of special
registers or global variables which could change in the MQT select
statement.

Honestly, I've found very little use for MQTs since Db2 for IBM i doesn't
support system maintained ones.
Everything I've considered them for was better served by a EVI with
calculated fields as those are maintained by the system.

HTH,
Charles


On Mon, Mar 4, 2024 at 8:13 AM Justin Taylor <jtaylor.0ab@xxxxxxxxx> wrote:

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.