This will work if you use "refresh deferred". Rules for "refresh immediate" are more stringent. See SQL Reference.
An e.g. Say your claim_lags table has 2 columns: cl_col1, cl_col2. You can code your create stmt as follows: create summary table tmp as (select cl_col1, cl_col2, count(*) as mycount from claim_lags group by cl_col1, cl_col2) data initially deferred refresh imediate Excerpts from SQL reference, vol2: When REFRESH IMMEDIATE is specified: - the fullselect must be a subselect - a GROUP BY clause must be included in the subselect - The select list must contain a count(*) ..... HTH, Abhijit -----Original Message----- From: Sitaram Vijay kartik [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 5:47 PM To: [EMAIL PROTECTED] Subject: [DB2EUG] Summary Table question Hi all While trying to create a summary table, i get the following error message. db2 " create table tmp as ( select * from claim_lags ) data initially deferred refresh immediate " 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 summary table "DB2.TMP" is not valid. SQLSTATE=428EC Any kind of help is appreciated. Tks! VJ __________________________________________________ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** You are subscribed to [EMAIL PROTECTED] *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** You are subscribed to [EMAIL PROTECTED] *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
