On Jun 16, 6:17 pm, dean <deanbrow..._at_yahoo.com> wrote:
> On Jun 16, 1:54 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
>
>
>
>
> > "dean" <deanbrow..._at_yahoo.com> a écrit dans le message de news: 1181945032.687684.288..._at_m36g2000hse.googlegroups.com...
> > | Oracle 10g, 9.2i. Windows, Linux.
> > |
> > | This is a statement that creates a unique index on field F1 in table T
> > | for cases where field IS_ACTIVE is 'Y':
> > |
> > | create table T (IS_ACTIVE varchar2(1), F1 varchar2(10), F2
> > | varchar2(10));
> > |
> > | > Table created.
> > |
> > |
> > | create unique index UNIQ_ACTIVE_RECS
> > | on T ( case when IS_ACTIVE = 'Y' then F1 else NULL end);
> > |
> > | > Index created.
> > |
> > |
> > | Does anyone have an example of how to do this with 2 fields (say, F1,
> > | F2)? In my case its 2 varchar2 fields, so I can make up a
> > | concatenation such as this:
> > |
> > | create unique index UNIQ_ACTIVE_RECS2
> > | on T ( case when IS_ACTIVE = 'Y' then F1|| '____' || F2 else NULL
> > | end);
> > |
> > | > Index created.
> > |
> > |
> > | However, this is not an elegant solution and its open to errors in
> > | some (albeit unlikely) combinations of strings.
> > |
> > | Thanks!
> > |
> > | Dean
> > |
>
> > SQL> create unique index UNIQ_ACTIVE_RECS2
> > 2 on T ( case when IS_ACTIVE = 'Y' then F1 end,
> > 3 case when IS_ACTIVE = 'Y' then F2 end);
>
> > Index created.
>
> > Regards
> > Michel Cadot
>
> Thanks!
>
> I presume that makes them unique as a whole pair, rather than unique
> independently? I will test.
>
> Cheers,
>
> Dean (ps where did you get this from?)- Hide quoted text -
>
> - Show quoted text -
I tested it, and its perfect. Thanks.
Received on Sat Jun 16 2007 - 17:28:32 CDT
