Re: creating snapshot without constraints

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 :) Received on Tue Dec 25 2007 - 23:40:58 CST