Re: creating snapshot without constraints

On Dec 26, 10:40 am, amitabh.me..._at_gmail.com wrote:
> On Dec 21, 7:37 pm, vitalis..._at_gmail.com wrote:
>
>
>
> > On Dec 21, 12:28 pm, amitabh.me..._at_gmail.com wrote:
>
> > > Hi
> > > Consider a test table - mytable:
> > > id number pk
> > > name varchar2(20) not null,
> > > addr varchar2(100) not null default 'local'
>
> > > I populated this table with some 200 test records.
> > > when I am trying to create snapshot using the following:
>
> > > create snapshot mytable_s refresh force as
> > > select * from test table;
>
> > > all works well.
> > > But in my requirement, i have something like if addr = 'local' then it
> > > should be null in the snapshot.
>
> > > So i tried something like:
>
> > > create snapshot mytable_s refresh force as
> > > select
>
> > > id,
>
> > > name,
>
> > > decode(addr, 'local', null)
>
> > > from test table;
>
> > > this throws error: ORA-01723: zero-length columns are not allowed
>
> > Your "decode" does not handle all possible values for addr. Use
> > decode(addr, 'local', null,addr) or replace "decode" with an ad-hoc
> > case expression.
>
> Thanks for your responses. That missing part of decode did the thing :)

Hi

There is one more problem. When I am trying to use the decode in above mentioned way in create snapshot on a column that is part of primary key, i get error msg saying:
ORA-12016: materialized view does not include all primary key columns

Can this be done somehow? Received on Thu Dec 27 2007 - 00:34:55 CST