Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

select * from X where > PARAM

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

asked 29 Apr '11, 12:06

Martin's gravatar image

accept rate: 14%

edited 29 Apr '11, 12:07

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

select * from x where datediff(dd,, 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.)

(29 Apr '11, 18:50) Volker Barth

No, not at this time.

permanent link

answered 29 Apr '11, 13:34

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

Am I right that a (CREATE) VARIABLE won't do, either, as it is connection-specific by default, and that would be non-deterministic, too?

(I'm asking as using such a variable would be a common way to parametrize a normal view.)

(03 May '11, 03:29) Volker Barth

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.


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'
select * from X, paramtable
where > paramtable.A_PARAM and paramtable.PK = 1;
permanent link

answered 02 May '11, 17:16

Nica%20_SAP's gravatar image

Nica _SAP
accept rate: 3%

edited 03 May '11, 03:31

Volker%20Barth's gravatar image

Volker Barth

The workaround I use is an extra table just with the value of the parameter and then in the materialized view:

select * from X where > (select max(PARAM) from paramtable)

Not elegant but it works.

permanent link

answered 02 May '11, 02:37

Martin's gravatar image

accept rate: 14%

edited 02 May '11, 02:38

I'm assuming you are calling the MV explicitly in your queries (instead of relying on view matching)?

(02 May '11, 03:17) Volker Barth
Replies hidden

yes, I am not using the view for the optimizer

(02 May '11, 07:24) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Apr '11, 12:06

question was seen: 11,277 times

last updated: 01 Feb '13, 14:50