String Aggregate Function

5 pts.
Tags:
AS/400
AS/400 DB2
DB2/400
SQL
I need a report that provides an aggregate of a string column - in this case a concatenation of the values in a comma separated string. There is no such DB2 aggregate function that does this. A stored procedure (or possibly a customer aggregate function?) seems the only likely way to accomplish this and my procedure writing skills are rather basic. Essentially, I need to loop through a cursor and concatenate the values and return the data. Given this data: 'abc123', 'one' 'abc123', 'two' 'def456', 'one' 'def456', 'two' I want to return it as: 'abc123', 'one,two' 'def456', 'one,two' Any suggestions would be much appreciated. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

If the comma delimited file will contain the same number of fields each time, create a file on the i5 that matches the coma delimited file. Use CPYTOIMPF to move the file from the IFS to the i5. Call it FILEA.

Then create a file with two fields, one to hold the field containing the ‘abc123′ as the key filed and the second field to hold the ‘one, two, three….’ data (DATA_FIELD). Call it FILEB

Then use the pseudo code below.

<pre>
READ FILEA

DOW not %EOF(FILEA);
Chain (key_field) FILEB;
IF %FOUND;
DATA_FIELD = %TRIM(DATA_FIELD) + ‘,’ + DATA_FIELD _FROM_FILEA;
WRITE FILEB;
ENDIF;

READ FILEA;

ENDDO;

</pre>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JLatos
    Yes, DB2 does have a function for aggregation through concatenation, but it's an XML function. This means you need to convert your strings to XML, perform the aggregations, then convert back to non-XML. It looks like this:
    varchar(replace(replace(
    xml2clob( xmlagg(xmlelement(NAME a, recordID)))
    ,'<A>', ''),'</A>', '; '))
    20 pointsBadges:
    report
  • TomLiotta
    Assuming that you have a current release of the OS (and reasonably current DB2 PTF group level), a basic recursive CTE could be used. Some general information can be found in Recursive query optimization. The examples in the Information Center illustrate summing the costs of a series of connecting flights between cities on a route to a destination. The process of summing would be replaced with concatenation if you wanted a series of strings to be put together. It doesn't require XML nor any extenders, so it's available on basic systems back to V5R4. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following