nest and unnest operators

Dear there, Do nest and unnest operators available in Informix? For example, if I have SPEECHID SPEECH_SPEAKER 1 <SPEAKER>A</SPEAKER><SPEAKER>B</SPEAKER> ------------------- I want to have a result as follows which I think we then need an unnest operator SPEECHID SPEECH_SPEAKER 1 <SPEAKER>A</SPEAKER> 1 <SPEAKER>B</SPEAKER> -------------------- Thank you very much. -Kanda

Kanda Runapongsa wrote: > Do nest and unnest operators available in Informix? > > For example, if I have > SPEECHID SPEECH_SPEAKER > 1 <SPEAKER>A</SPEAKER><SPEAKER>B</SPEAKER> > > ------------------- > I want to have a result as follows which I think we then need an > unnest operator > > SPEECHID SPEECH_SPEAKER > 1 <SPEAKER>A</SPEAKER> > 1 <SPEAKER>B</SPEAKER> > -------------------- Which database do you normally use? UniVerse or UniData, perchance? On the face of it, you are storing a list of values in the SPEECH_SPEAKER column, and want the database to un-nest the list into a series of rows with a single value from the list in each row. If you are using a traditional Informix RDBMS product, then the first version of the table is not in proper 1NF and you'd have to write the code to do the unnesting, and it would be hard with the sample data. If you are using an Informix ORDBMS product, then the first version of the table might be using a LIST or SET or MULTISET for the SPEECH_SPEAKER column. I don't think there's a simple way to convert from the LIST, SET, MULTISET representation to the unnested form, though I don't doubt it could be done if some brain power was expended, possibly with some work from a UDR. If you're using one of the U2 products, then you should say so, because I can't help at all (assuming you accept that what I've said so far is actually 'help'). -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"