hi,
try the following stmt, it will work.

db2 => CREATE TABLE summary2(colname) AS \
db2 (cont.) => ( \
db2 (cont.) => select count(vemail) from msg,reportng where
msg.ticket=reportng.ticket \
db2 (cont.) => ) \
db2 (cont.) => DATA INITIALLY DEFERRED REFRESH deferred \
db2 (cont.) => ENABLE QUERY OPTIMIZATION \
db2 (cont.) => MAINTAINED BY SYSTEM


regards,
saravana.




-----Original Message-----
From: SaiPrasad
[mailto:db2udbdba-ezmlmshield-x19608639.[Email address protected]
Sent: Friday, December 24, 2004 4:30 PM
To: LazyDBA Discussion
Subject: Summary table error


I tried to create summary table with the following Syntax, which resulted
in following error msg.

Also i tried to create summary table using DATA INITIALLY DEFERRED REFRESH
DEFERRED.
But still same error is thrown out.

db2 => CREATE TABLE summary2 AS \
db2 (cont.) => ( \
db2 (cont.) => select count(vemail) from msg,reportng where
msg.ticket=reportng.ticket \
db2 (cont.) => ) \
db2 (cont.) => DATA INITIALLY DEFERRED REFRESH deferred \
db2 (cont.) => ENABLE QUERY OPTIMIZATION \
db2 (cont.) => MAINTAINED BY SYSTEM

DB21034E The command was processed as an SQL statement because it was not
a
valid Command Line Processor command. During SQL processing it returned:
SQL20058N The fullselect specified for the materialized query table
"DEVAIX1.SUMMARY2" is not valid. SQLSTATE=428EC


Pls. help me to close this ASAP.

Thanks In Advance
saiprasad


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
**************************************************************************
This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or
distribution or forwarding of any or all of the contents in this message is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard
is appreciated.
**************************************************************************

DB2 & UDB email list listserv db2-l LazyDBA home page