"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:ca**********@hanover.torolab.ibm.com...
Serge Rielau wrote:
WITH NO DATA
instead of
DATA INITIALLY DEFERRED REFRESH DEFERRED
Could also be DEFINITION ONLY on an older release
WITH NO DATA is SQL Standard.
And here's me temporary creating views, so I can do CREATE table LIKE view.
You learn something new every day. I wonder why I missed it....
So, even though WITH NO DATA is part of the
<i>materialized-query-definition</i> section on the "CREATE TABLE" DDL, the
clause actually creates you a *plain table* with no entry in SYSCAT.VIEWS
and a TYPE of 'T' in SYSCAT.TABLES.
Interestingly, the manual states
"The query is used only to define the table. The table is not populated
using the results of query and the REFRESH TABLE statement cannot be used.
When the CREATE TABLE statement is completed, the table is no longer
considered a materialized query table."
So at the start (!) of the atomic CREATE TABLE statement, the table is
considered an MQT, but at the end of the atomic statement it is not an MQT.
Hummm.
Nit picking a bit more, the next sentance is
"The columns of the table are defined based on the definitions of the
columns that result from the fullselect. If the fullselect references a
single table in the FROM clause, select list items that are columns of that
table are defined using the column name, data type, and nullability
characteristic of the referenced table."
So if the full select references columns from single table, things are
exactly the same as if it references columns or expressions over many
tables. What is the point of that second sentence?
Regards
Paul Vernon
Business Intelligence, IBM Global Services
