Creating Table as select...

From: Jon <jsantarelli_at_molex.com>
Date: 6 Feb 2004 13:46:01 -0800
Message-ID: <10e66e3.0402061346.41f70bab@posting.google.com>

Hi All,
I'm trying to create a table with an 'as select' clause, but I would also like to specify a table constraint on the new table - a unique index. One of the versions that I have tried without success is below. Can this be done in one statment or do I have to do an alter table afterwords? If it can be done in one statement what would that statement be?

Thanks & Regards
Jon

create table obs_last_logon

       tablespace bv_data
CONSTRAINT obs_last_logon_unique UNIQUE (user_id) USING INDEX TABLESPACE bv_index
as
  select distinct a.user_id, c.user_alias, max(timestamp) "timestamp"     from obs1 a, obs_bv_user_profile b, obs_bv_user c    where not a.user_id in (1301,984,3408,1262)

     and a.user_id = c.user_id
     and c.user_id = b.user_id

   group by a.user_id, c.user_alias Received on Fri Feb 06 2004 - 15:46:01 CST