Re: Re: Re: Sql to concatenate multiple rows to a single row /
Amazing ...
I didn't knew that there were a multiset to lvarchar cast.
J.
-----Original Message-----
From: <curtiscrowson@bellsouth.net>
To: Jean Sagi <jeansagi@myrealbox.com>,bozon <curtis@crowson1.com>
Date: Thu, 17 Nov 2005 9:09:06 -0500
Subject: Re: Re: Sql to concatenate multiple rows to a single row / column
The original poster of this SQL very cleverly cast the multiset to an lvarchar, note the ::lvarchar. I would never have thought of that. But once you do you have the string:
MULTISET{"item1","item2","item3"}. You just need to strip out the extra characters to get exactly the data that you want.
Try the example SQL I included which creates tables and inserts data. Then run the sql below to see what happens. You can then see for yourself what it does.
>
> From: Jean Sagi <jeansagi@myrealbox.com>
> Date: 2005/11/16 Wed PM 06:09:18 EST
> To: bozon <curtis@crowson1.com>
> CC: informix-list@iiug.org
> Subject: Re: Sql to concatenate multiple rows to a single row / column
>
> What this exactly do ?
>
> Select patid, patname,> replace(replace(substr(multiset (select item allergy from allergy where
> allergy.patid =
> patient.patid)::lvarchar,11),"'",""),"}","")
> from patient ;
>
> substr from a multiset ?
>
>
> J.
>
> bozon escribis:
> > Very cool. I was doing it the old fangled way. Nice to know that I can
> > always learn something.
> >
> > 8.5 is the Extended Parallel Server which is great for data warehouses
> > but hasn't really caught on.
> >
> > One quick note since you know that the string "MULTISET{" occurs at the
> > begining of the lvarchar there is no sense in searching the whole
> > multiset for it you can just use the substr function to remove it. (It
> > could also be wrong because one of your patients could be allergic to
> > "MULTISET{" ;-) )
> >
> > Select patid, patname,> > replace(replace(substr(multiset (select item allergy from allergy where
> > allergy.patid =
> > patient.patid)::lvarchar,11),"'",""),"}","")
> > from patient ;
> >
> >
>
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
sending to informix-list