IBM Mainframe Forum

Hello,

This question may have been answered, and forgive me if it has. I am extracting data from an AS400 using DB2 SQL. I have two tables that I need information from. The first table is a straight file. When I reference the second table through a join, I get multiple lines with the exact same information except for the items in the second table. Tables are as follows...

Table1


Table2


Results



Desired results


Any help would be greatly appreciated.

Personally I would do one of two things

1) Write a program to read table 1, then for each row on table 1 open a cursor on table 2, and for each row from table 2 concatenate all the DESCRIP values into one field, giving your desired resultss.

or

2) Get rid of table 2 and add the DECSRIP column to table 1, perhaps as a VARCHAR, but give it a maximum length of enough bytes to fit your description text in.

If this doesn't help you then please supply more information. Are you expecting to achieve your desired results by SQL alone, or is there an option of writing a program to format the output as you desire it?

Thanks SWD for your prompt response. I apologize for not providing additional information. This database was created by, and belongs to another company and I can't modify the database structure. They, in their infinite wisdom, created it this way and it is my cross to bear whenever our clients want to extract information from it. Also, I am part of a mapping company. We extract data from several different databases (i.e. SQL Server, MS Access, Oracle, etc) and plot those on a map based on the address in the data. My example was taken from Work Request tables based from this particular AS400 DB2 database. Most of the extraction is done except for the DESCRIP issue giving me somewhat of a headache.

Normally you would concatenate the different rows inside a program, but if you want it in one SQL you probably need one of the following two :

This works on z/OS : Recursive SQL


Using XML-functions :