Re: multi-valued field?

Illustra and Informix Universal server have means of doing this.

Ian

Tony Vuong wrote:
>
> In article 537C_at_ibm.net, Steve Behman <sbehman_at_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_at_sun.com
Received on Sat Jan 11 1997 - 00:00:00 CST