Re: 428EC on current_date in MQT

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.