Concatenate returned fields in 1 to many situation

[DrSmyth]

I'm trying to create a single object that will be a concatenation of all the returned values in a one to many relationship.

Basically each person in the person table of my database can have any number of notes (from the notes table) allocated to them, when i include the person and there notes in a report i want the notes field to be a single field that contains all of that persons notes.

I've seen it done in excel but have no idea where to start.

Oh yes i'm currently on a db2 database.

 

[skrandel]

You can't do this with BO directly. What you would need to do is to build a stored function that does it for you. Then use that stored function in the select portion of an object.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

Quite true..

If you the situation that 'any number' of notes is still in a manageble range (like 10-100 notes) this may work:

If you are not on mainframe db2 will have rownumber() function available like:

rownumber() over (order by note_id).

Create this as an additional object (say: IND) and fetch it in the SQL.

Create a whole bunch of variables on the notes_id 's:

1: = If (IND=1) Then <Note>
2: = If (IND=2) Then <Note>

100: If (IND=100) Then <Note>

Create a string variable as:

String: = 1&2&3&4&5&6&.........................&99&100

Create a table with person & string

I tested this with freehand SQL from a DB2 database, but somehow I can only get it right if I fetch the 'note' as a measure.

I know this is just an approximate solution, so I will look into DB2 functions for a better one

T. Blom
Information analyst
tbl@shimano-eu.com