Re: multi-valued field?

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 Mon Jan 06 1997 - 00:00:00 CST