Combining fields in a 1 to many relationship



IBM's flagship relational database management system

Combining fields in a 1 to many relationship

Postby cattinc » Mon Aug 23, 2010 11:28 pm

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
Req_No     Customer     Job_Start     Job_End
000001     John Doe       10-13-2009     10-14-2009
000002     Jane Doe       03-23-2007     03-25-2007
000003     Al Monroe      06-17-2004     06-19-2004


Table2
Req_No       Line_No      Descrip
000001           1            REPLACED CUSTOMER'S HYD PUMP
000001           2            WITH NEW PUMP.  TESTING COMPLETE
000001           3            AS PER CONTRACT AGREEMENT
000002           1            TESTED METER. NO DEFECTS NOTED
000003           1            REMOVED AND REPLACED SAFETY
000003           2            SWITCH.  TESTED AND OPERATIONAL.


Results
Req_No     Customer     Job_Start     Job_End                Descrip
000001     John Doe       10-13-2009     10-14-2009         REPLACED CUSTOMER'S HYD PUMP
000001     John Doe       10-13-2009     10-14-2009         WITH NEW PUMP.  TESTING COMPLETE
000001     John Doe       10-13-2009     10-14-2009         AS PER CONTRACT AGREEMENT
000002     Jane Doe       03-23-2007     03-25-2007         TESTED METER. NO DEFECTS NOTED
000003     Al Monroe      06-17-2004     06-19-2004         REMOVED AND REPLACED SAFETY
000003     Al Monroe      06-17-2004     06-19-2004        SWITCH.  TESTED AND OPERATIONAL.



Desired results
Req_No     Customer     Job_Start     Job_End               Descrip
000001     John Doe       10-13-2009     10-14-2009       REPLACED CUSTOMER'S HYD PUM WITH NEW PUMP. TESTING COMPLETE AS PER CONTRACT AGREEMENT
000002     Jane Doe       03-23-2007     03-25-2007       TESTED METER.  NO DEFECTS NOTED
000003     Al Monroe      06-17-2004     06-19-2004       REMOVED AND REPLACED SAFETY SWITCH.  TESTED AND OPERATIONAL.


Any help would be greatly appreciated.
cattinc
 
Posts: 2
Joined: Mon Aug 23, 2010 11:06 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combining fields in a 1 to many relationship

 

Re: Combining fields in a 1 to many relationship

Postby swd » Tue Aug 24, 2010 1:44 pm

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?
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Combining fields in a 1 to many relationship

Postby cattinc » Tue Aug 24, 2010 5:52 pm

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.
cattinc
 
Posts: 2
Joined: Mon Aug 23, 2010 11:06 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combining fields in a 1 to many relationship

Postby GuyC » Mon Aug 30, 2010 2:09 pm

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
with Temp(Req_No,LongDesc,Line_no) as
(select Req_No, cast(Desc as varchar(10000))  from Table2 where Line_no = 1
union all
 Select Temp.Req_no, Temp.desc || ' ' || A.desc , A.Line_no
  from Temp, Table2 A
  where Temp.req_no = a.req_no and a.Line_no = (Temp.Line_no + 1)
)
select b0.* b1.LongDesc from Temp b1
                , Table1 b0
where b0.req_no = b1.req_no
 and   b1.Line_no = (select max(Line_no) from Temp B2 where b2.req_no = b1.req_no)


Using XML-functions :
select * from Table1,
(select req_no, replace(replace(xml2clob(xmlagg(xmlelement(NAME a, Desc))),'<A>',''),'</A>',';')
FROM (select Req_no,Desc from table2 order by Line_no) f 
GROUP BY Req_no) Temp
where Table1.req_no = Temp.Req_no
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2