ID CATEGORYID SEARCH_TERM
1 1 cat
2 1 mat
3 2 sat
...
I need to get the search terms concatenated - is there an easy way to
achieve this?
If I knew how many search terms there were, I could join the table to
itself:
SELECT a.search_term || b.search_term
FROM search_terms a, search_terms b
WHERE a.categoryid = b.categoryid
AND a.id < b.id
(and expand this for n tables).
But I don't know how many search terms there are. Is there a simple
way to do this? Or is it some kind of repeating function that I need
to program?
Thanks
JCSJF
B.
select xmlagg(xmlelement(NAME a, email_address)) FROM
advertising.recipients;
Gives me
Function not supported (Reason code = "58"). SQLSTATE=42997
:-(
Running DB2 Express 8.2.1 on SLES 8. Do I need to be installing XML
extenders or something?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
The main FUNCTION is XMLAGG(), however, it requires the XML data typeas
its input, and outputs it as its datatype too. To make text into XML,
XMLElement() both changes the text to be XML and changes the data type
to XML. So much for the input. For the output, (as Serge pointed out)
XML2CLOB() turns it into a viewable data type.
Then you need to strip the XML tags (try select
substr(xml2clob(xmlelement(NAME a, email_address)), 1, 50) FROM
advertising.recipients; to see it). Which is <A> and </A> ("A" is the
NAME you specified.) So, REPLACE does that.
Finally, if you don't actually want a clob, SUBSTR() takes care of
that.
B.