Re: multi-valued field?

From: Jacob Salomon <jake_at_apparel.net>
Date: 1997/01/08
Message-ID: <E3pC49.FxL@nonexistent.com>#1/1

Tony Vuong wrote:
>
>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.

Tony,

this looks like a union call. ;-)

Let's assume you have a limited number of multi-values per row. For this database, it's best to use fixed length records. (I know varchars are available but they would not help the problem.) In this example, I will assume a max of 5 multiple components.

So your schema would look like:

create table yutz
( key_col integer,

  mval1   char(10),
  mval2   char(10),
  mval3   char(10),
  mval4   char(10),
  mval5   char(10)

);

(BTW, this violates first normal form. Few database designs inspire a preachiness in me as badly as this one.)

The query to treat each component as you specified would look like:

select key_col, 1 mval_seq, mval1 component_val from yutz  where mval1 is not null
UNION
select key_col, 2 mval_seq, mval2 component_val from yutz  where mval2 is not null
UNION
select key_col, 3 mval_seq, mval3 component_val from yutz  where mval3 is not null
UNION
select key_col, 4 mval_seq, mval4 component_val from yutz  where mval4 is not null
UNION
select key_col, 5 mval_seq, mval5 component_val from yutz  where mval5 is not null

 order by 1,2
;

Note some tricks involved here:

a. Use of the UNION operator
b. Selecting a literal value as a column and assigning it a display name
c. Use a display name for the mval# columnn each query of the union
d. The order by uses position numbers instead of column names.  This is

required

   when using the UNION operator.

It would be real neat if we could create a view on this union'ed query. The ability to create a view as a union'ed query was someplace on the burners at Informix some months ago. I think the capability is part of the SQL-92 standard. I do not know where Informix stands on this.

I invite polite corrections to any misconceptions about these wishlist items.

-- 
	-- Jake Salomon                   .                       .
              _..-'(                      )`-.._
           ./'. '||\\.       }\_/{       .//||` .`\.
        ./'.|'.'||||\\|..    )o o(    ..|//||||`.`|.`\.
     ./'..|'.|| |||||\`````` \'@'/ ''''''/||||| ||.`|..`\.
   ./'.||'.|||| ||||||||||||.  |  .|||||||||||| ||||.`||.`\.
  /'|||'.|||||| ||||||||||||{  |  }|||||||||||| ||||||.`|||`\
 '.|||'.||||||| ||||||||||||{  |  }|||||||||||| |||||||.`|||.`
'.||| ||||||||| |/'   ``\||``  |  ''||/''   `\| ||||||||| |||.`
|/' \./'     `\./         \!|\   /|!/         \./'     `\./ `\|
V    V         V          }' `\ /' `{          V         V    V
\    \         \               V               /         /    /
+-----------------------------------------------------------+
|    Diplomacy: The art of getting something off your       |
|                  chest without losing your shirt          |
+------------------------Alfred E. Neuman-------------------+

Received on Wed Jan 08 1997 - 00:00:00 CST