Functional Indexes?
Ok sports fans, here's a really fun problem. Suppose you have a table with the following columns: Table Foo: Column a: id (Integer) Column b: type (single character) Column c: sequence # Column d: description (varchar up to 128 characters wide) Now there are 3 types of records: "A", "B","C" Here's where the fun begins. I need to ensure that if I have a record of type "C", the pair (id, type) must be unique. For the other record types, I need to allow for multiple entries per id. So how do you do this? The answer is create a functional index that ensures uniqueness for records of type "C" per id. I have a really cool solution in Oracle. Now I'd like to see it in DB2 and IDS. So what's the syntax? -G _________________________________________________________________ Get in touch in an instant. Get Windows Live Messenger now. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_getintouch_042008
On 8 Apr, 17:04, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > Ok sports fans, here's a really fun problem. > > Suppose you have a table with the following columns: > > Table Foo: > Column a: id (Integer) > Column b: type (single character) > Column c: sequence # > Column d: description (varchar up to 128 characters wide) > > Now there are 3 types of records: "A", "B","C" > > Here's where the fun begins. > > I need to ensure that if I have a record of type "C", the pair (id, type) must be unique. > For the other record types, I need to allow for multiple entries per id. > > So how do you do this? > > The answer is create a functional index that ensures uniqueness for records of type "C" per id. > > I have a really cool solution in Oracle. > Now I'd like to see it in DB2 and IDS. > > So what's the syntax? > > -G > > _________________________________________________________________ > Get in touch in an instant. Get Windows Live Messenger now.http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_... You mean a unique index on some of the rows in a table? I would use VII the Virtual Index interface something far more flexible than Oracle 9i I stopped playing with Oracle >9 when the developer version of Oracle 10 took more than 768Mb of memory just to install on a PC (If the fucking installer requires more than 768Mb that was more memory than any of my clients servers had at the time...)
Ian Michael Gumby wrote: > Ok sports fans, here's a really fun problem. > > Suppose you have a table with the following columns: > > Table Foo: > Column a: id (Integer) > Column b: type (single character) > Column c: sequence # > Column d: description (varchar up to 128 characters wide) > > Now there are 3 types of records: "A", "B","C" > > Here's where the fun begins. > > I need to ensure that if I have a record of type "C", the pair (id, > type) must be unique. > For the other record types, I need to allow for multiple entries per id. > > So how do you do this? > > The answer is create a functional index that ensures uniqueness for > records of type "C" per id. > > > I have a really cool solution in Oracle. > Now I'd like to see it in DB2 and IDS. > > So what's the syntax? > > -G > > > ------------------------------------------------------------------------ > Get in touch in an instant. Get Windows Live Messenger now. > <http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_getintouch_042008>; The syntax: http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.sqls.doc/sqls213.htm#sii-02crin-91797 But the real value is in the function, that apparently you already have... -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
VII might be a way to go. What would be the syntax? I'm one who hates how Oracle does a lot of things. But I have to give credit to Oracle on this one. Sure it doesn't make up for all the other issues, but sometimes they get something right. > From: david@smooth1.co.uk> Subject: Re: Functional Indexes?> Date: Tue, 8 Apr 2008 14:28:43 -0700> To: informix-list@iiug.org> > On 8 Apr, 17:04, Ian Michael Gumby <im_gu...@hotmail.com> wrote:> > Ok sports fans, here's a really fun problem.> >> > Suppose you have a table with the following columns:> >> > Table Foo:> > Column a: id (Integer)> > Column b: type (single character)> > Column c: sequence #> > Column d: description (varchar up to 128 characters wide)> >> > Now there are 3 types of records: "A", "B","C"> >> > Here's where the fun begins.> >> > I need to ensure that if I have a record of type "C", the pair (id, type) must be unique.> > For the other record types, I need to allow for multiple entries per id.> >> > So how do you do this?> >> > The answer is create a functional index that ensures uniqueness for records of type "C" per id.> >> > I have a really cool solution in Oracle.> > Now I'd like to see it in DB2 and IDS.> >> > So what's the syntax?> >> > -G> >> > _________________________________________________________________> > Get in touch in an instant. Get Windows Live Messenger now.http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_...>; > You mean a unique index on some of the rows in a table?> > I would use VII the Virtual Index interface something far more> flexible than Oracle 9i> > I stopped playing with Oracle >9 when the developer version of Oracle> 10 took more than 768Mb of memory just to install on a PC> (If the fucking installer requires more than 768Mb that was more> memory than any of my clients servers had at the time...)> > _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Use video conversation to talk face-to-face with Windows Live Messenger. http://www.windowslive.com/messenger/connect_your_way.html?ocid=TXT_TAGLM_WL_Refresh_messenger_video_042008
> From: spam@onlinedomus.net> The syntax:> > http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.sqls.doc/sqls213.htm#sii-02crin-91797>; > But the real value is in the function, that apparently you already have...> > -- > Fernando Nunes> Portugal No I don't have the function. That's what I'm looking for. I have it in Oracle, but not IDS. > > http://informix-technology.blogspot.com>; My email works... but I don't check it frequently...> _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ More immediate than e-mail? Get instant access with Windows Live Messenger. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008