Materialized view with Parameter?

[VolkerBarth]

Is it possible to create a materialized view which uses a parameter during manual refresh? Something like

CREATE MATERIALIZED VIEW "DBA"."MyView" in system as 
select * from X where X.date > PARAM

So that e.g. each week the materialized view can be refreshed to reflect the values of last week or so...

[VolkerBarth]

I guess you do not want to use an ordinary view (e.g. something like

select * from x where datediff(dd, x.date, current date) >= 7

for performance reasons. May a global shared by all temporary table be of help? (I'm aware that temporary tables do not get used by view matching.)

[VolkerBarth]

(That would better asked as a separate question...): No, materialized views were introduced in v10.

[Former Member]

If you maintain 'paramtable' with exactly one row representing the current parameter value, then your view can be declared immediate and also can be used for cost-based view matching by the SQL Anywhere Optimizer for queries which don't mention 'paramtable': to let the optimizer know that paramtable has exactly one row, add a primary key column. This is one solution. Other solutions exist as well.

Example:

create table paramtable (PK int not null primary key, A_PARAM int);

insert into paramtable (PK,A_PARAM) values (1, NULL);

update paramtable set A_PARAM=PARAM;

-- This materialized view can be declared immediate
-- and will be also used by the optimizer if the
-- query references only the table 'X'
CREATE MATERIALIZED VIEW "DBA"."MyView" in system as 
select * from X, paramtable
where X.date > paramtable.A_PARAM and paramtable.PK = 1;