DB2 Function to Concatenate recordset from other tbl

[glschill]

I'm new to DB2 and I'm hoping someone can help me with a function or maybe suggest another way to accomplish the following:

We have a Perergrine Service center app with a DB2 back-end. Peregrine stores it's multiline text boxes as an array with each line of the text box as an array element. When implementing this in DB2, this array is stored in a separate table like the following and is linked to the main table using the ID:

ID RecordNumber Text
P100 1 This is line 1 of the text box
P100 2 This is line 2 of the text box
P100 3 This is line 3 of the text box
P110 1 Line 1 of another text box
P110 2 Line 2 of other text box

Reporting from this is a nightmare because you have to loop through the array table and concatenate your string back together. I can do this is something like VB or ASP, but I was wondering if a db2 function can be called to do this. And, if so, can someone give me an example of how to do this? I'm thinking this may be the way to go since I'm going to have many mulit-line text boxes. If someone has another solution, I would love to hear it!!!

 

[blom0344]

Hello GlSchill,

I am almost facing the same problem with data in DB2. I resolved it by using my ETL tool in a bit of an inappropiate way. There is no standard DB2 function that can do this, since these functions work on fields from the same record. You can not get around looping through the data , for which I opened some threads in the VBA forums.
See thread 705-340753.

An alternative solution I worked upon in Access was creating a crosstab query and defining a second query that concatenates the fields from the crosstab to a new string.
This works very well for a limited number of recordnumbér' s .
Accessing data on DB2 from Ms Access was done with linking the table through ODBC. The output from the Access procedings can then be easily uploaded to a new DB2 table.

If you want details , let me know. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com