Re: MULTISETS (and how to get data out of them)
seank@teepee.demon.co.uk (Sean Kelsey) wrote in message news:<a8d69e97.0309030702.694902b9@posting.google.com>...
> Afternoon all...
>
> IDS 9.30.TC4 on W2KAS
>
> I have a table containing a multiset of data in the following format
>
> ID1 Members
> 1 MULTISET{101,102,103,104,105,106}
> 2 MULTISET{201,202,203,204,205,206}
> 3 MULTISET{301,302,303,304,305,306}
> 4 MULTISET{401,402,403,404,405,406}
> etc...
>
> Because I'm fairly new to MULTISETs, what type of query (SELECT) would
> I need to perform in order to get the output in the following format:-
>
> ID1 Member
> 1 101
> 1 102
> 1 103
> 1 104
> ...
> 2 201
> 2 202
> 2 203
> 2 204
> ...
>
>
> All help, as ever, greatly appreciated
Sorry. You're well and truly buggered.
What you want to do is to 'flatten' the multiset's values into a
column. This can't be done with a simple query. The point of
the non-first normal form types in IDS is for use as an entirely
self-contained 'value', usually for small domain data (like shoe
size, or 'comes-in-colors' sorts of things.)
Normalize your schema, and use a more traditional SQL.
CREATE TABLE Foo ( Id INTEGER NOT NULL PRIMARY KEY );
CREATE TABLE Bar ( Foo INTEGER NOT NULL FOREIGN KEY REFERENCES BAR (Id),
Member INTEGER NOT NULL );
SELECT Foo, Member
FROM Bar;
KR
Pb