Hi all,

is it possible to create a materialized view based on some base table with
computed columns based on a user-defined function?

I thought this should be possible as long as the computed columns are not
referred in the view's definition (i.e. are neither part of the select-list
nor referenced in the from/where/etc. clauses).
But in a short test I got an error message saying
"Syntax error near 'NameOfMyUserDefinedFunction' - materialized view
definition may not use the following construct: User defined or builtin
functions'."

That function is used in the COMPUTE() expression in the create table stmt
(and in a before trigger) of one of the base tables; however the column is
not referenced in the mat. view.

Is that by design?

If so, why -would it be too hard to find out if the underlying data has
changed? (If I understand correctly, this is one major task w.r.t.
materialized views. But would an unreferenced column be of any influence
here?).

TIA

Volker

P.S. Instead of mat. views, I "stored" my huge selection in a regular
table - not the ideal thing, but working;-)

P.P.S. The SA 10.0.1 docs state the following:

SQL Anywhere® Server - SQL Usage
Working with Database Objects
Working with views
Working with materialized views

Restrictions when managing materialized views

When creating a materialized view, the definition for the materialized view
cannot contain:
....
calls to stored procedures, user-defined functions, or external functions.
....

[Re-posting since the post from yesterday seems to have gone missing]

Volker,

I will need to take a look at the specifics before commenting . I will try
to make up a repro based on your description but if you have one handy and
don't mind posting it I'll appreciate it.

On the surface, I have to say that it seems like unintended behaviour based
on the description below.

-anil

"Volker Barth" <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de> wrote

I've been able to reproduce the problem and can say the current behaviour
was not intended.

We will look into fixing it. Our thanks for reporting it, Volker, and
regrets for any inconvenience.


-anil

"anil k goel" <anilgoel (AT) nowhere (DOT) com> wrote

Fixed in build 3605 of 10.0.1.

Note that the error should not be generated even if the materialized view
actually references the computed column.

-anil

Now, that IS quick!

Thanks
Volker
"anil k goel" <anilgoel (AT) nowhere (DOT) com> schrieb im Newsbeitrag
news:4746d309$1 (AT) forums-1-dub (DOT) ..