Re: multi-valued field?

In message <5ari90$o1q_at_engnews2.Eng.Sun.COM> - tvuong_at_Eng.Sun.COM (Tony Vuong)6 Jan 1997 19:02:56 GMT writes:
>
>
>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
>
>

Are you using a DB2 Common Server version of DB2? If you are, what should work is create your table using column, say, MULTI_VAL, and then define functions such as FIRST_VAL, SECOND_VAL, etc., which return the first value if it exists, or else NULL, etc. You would have to write a C program to do the parsing of MULTI_VAL string.

Your query might then look something like:

select id, FIRST_VAL(MULTI_VAL)
  from table
 where FIRST_VAL(MULTI_VAL) IS NOT NULL
union
select id, SECOND_VAL(MULTI_VAL)
  from table
 where SECOND_VAL(MULTI_VAL) IS NOT NULL

You might then hide this complexity in a view or a WITH expression.

I did not test this - personally, I would prefer a normalized design.

Best regards,

Peter Boot Received on Tue Jan 07 1997 - 00:00:00 CST