Re: multi-valued field?

Illustra and Informix Universal server have means of doing this. Ian Tony Vuong wrote: > > In article 537C@ibm.net, Steve Behman <sbehman@ibm.net> writes: > > Kevin Kane wrote: > > > > > > Hi. Is there such a thing in DB2 as a multi-valued field? By this I mean > > > that I have a column called, say, AUTOMOBILES. If a person has 2 > > > cars, then I would like the AUTOMOBILES field of his record to contain > > > 2 entries, one for each car. > > > > > > So, can DB2 handle this, or am I just going to have to cat the > > > individual values together into a character string with some kind of > > > delimiter? > > > > > > > Kevin, in fact you can do almost anything you want to do but it is often > > NOT wise to do it. > > > > At first blush it sounds like you have found a good case where it is > > unwise. > > > > If you can flesh out the DETAILS of your OBJECTIVE in constructing a > > 'multi valued field' perhaps we could help you find an ECONOMICAL way to > > get what you want and 'stay true' to the ideals of the relational > > database model... > > ... (stuffs deleted) ... > > Steve, > > I do have a application similar to that being used by Kevin. Just say that > the design step involving the choice of data modeling has already been performed > by both the design and the spec team, and the final choice is to implement > a multi-valued field as above. (In fact, a major influence of the decision > is performance, but let's not get into that topic in this discussion). > > Assuming now, that I have a column field that is multi-valued, one value > is separate from another by a delimiter. For example, my rows might look like > > Record Multi-Values > 1 a, b, c, d > 2 a, b > 3 d, e > 4 b, c > > During runtime, I would like to achieve a way of "breaking-up", or > "component-erizing" the Multi-Values into a dynamically created components > table so that operations involving sets and subsets can be performed on this > components table. One such components table might be: > > Id Single-Value > 1 a > 1 b > 1 c > 1 d > 2 a > 2 b > 3 d > 3 e > 4 b > 4 c > > The question is whether I can do the break-up with only SQL statements (without > procedural SQL like PL/SQL)? If not, then can I do it with procedural-SQL? > At present, I am restricted to using only SQL, so I am very much interested in > the first approach. However, if it is impossible to do it with SQL, then I'd > also like to get help on how that can be done with procedural-SQL. > > Thanks very much, > > Tony Vuong > tony.vuong@sun.com