concatenating historical records

[meter_man]

Is there a way to bring historical data into one field? I have activities
A1, A2, A3 ,etc in a history table. I need to check for the presence of
that data and bring it back if it exists for a record. I know I can do this
using (not tested):

With #temp as (

Select RecordID, Name from Table1 where . . . )

Select

RecordID

, Name

, substr((#A1.Activity || #A2.Activity || #A3.Activity),2) as History

from

#Temp T

left join (

Select ',' || Activity_CD as Activity from

#Temp T INNER JOIN History H on T.RecordID = H. RecordID

WHERE Activity_CD = 'A1') as #A1 on T.RecordID = #A1.RecordID

left join (

Select ',' || Activity_CD as Activity from

#Temp T INNER JOIN History H on T.RecordID = H. RecordID

WHERE Activity_CD = 'A2') as #A2 on T.RecordID = #A2.RecordID

left join (

Select ',' || Activity_CD as Activity from

#Temp T INNER JOIN History H on T.RecordID = H. RecordID

WHERE Activity_CD = 'A3') as #A3 on T.RecordID = #A3.RecordID

I don't have permissions to do a Stored Proc and I have to concatenate about
20 of these things. Is there an easier way so I can result in history only
reading like "A1, A3, A10" ?

TIA

candyman

[Knut Stolze]

meter_man wrote:

Is there a way to bring historical data into one field? I have activities
A1, A2, A3 ,etc in a history table. I need to check for the presence of
that data and bring it back if it exists for a record. I know I can do
this using (not tested):

With #temp as (
Select RecordID, Name from Table1 where . . . )

Select
RecordID
, Name
, substr((#A1.Activity || #A2.Activity || #A3.Activity),2) as History
from
#Temp T
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A1') as #A1 on T.RecordID = #A1.RecordID
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A2') as #A2 on T.RecordID = #A2.RecordID
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A3') as #A3 on T.RecordID = #A3.RecordID
I don't have permissions to do a Stored Proc and I have to concatenate
about
20 of these things. Is there an easier way so I can result in history
only reading like "A1, A3, A10" ?

Have a look at recursive queries and "Bill of materials" in the manual.
Alternatively, you can use the XMLAGGREGATE function to do the aggregation
from multiple rows into a single one.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[jefftyzzer]

Here's an example applying a recursive CTE, using an algorithm from
Molinaro's _SQL Cookbook_ (note: best viewed with fixed font):

WITH
PARTICIPANT_NAME
(
PRTCP_NM_ID,
PRTCP_ID,
FIRST_NM
)
AS
(VALUES (1,1,'JEFF'),
(2,1,'JEFFREY'),
(3,2,'EDDIE'),
(4,2,'EDWARD'),
(5,2,'TED'),
(6,3,'ELIZABETH')
),
PRTCP_NM
(
PRTCP_ID,
CNT,
LIST,
PRTCP_NM_ID,
LEN
)
AS
(
SELECT
PN1.PRTCP_ID,
COUNT(*) OVER (PARTITION BY PN1.PRTCP_ID),
CAST(PN1.FIRST_NM AS VARCHAR(4000)),
PN1.PRTCP_NM_ID,
1
FROM
PARTICIPANT_NAME PN1
UNION ALL
SELECT
PRTCP_NM.PRTCP_ID,
PRTCP_NM.CNT,
PRTCP_NM.LIST||', '||PN2.FIRST_NM,
PN2.PRTCP_NM_ID,
PRTCP_NM.LEN + 1
FROM
PARTICIPANT_NAME PN2,
PRTCP_NM
WHERE
PN2.PRTCP_ID = PRTCP_NM.PRTCP_ID
AND
PN2.PRTCP_NM_ID PRTCP_NM.PRTCP_NM_ID
)
SELECT
LIST
FROM
PRTCP_NM
WHERE
LEN = CNT

and here's a really cool example--from this forum, BTW--of using the
XMLAGG function:

CREATE TABLE EMPLOYEE(NAME VARCHAR(15), DEPT VARCHAR(15));

NAME DEPT
----- ------
MISO SOLUTIONS
JOHN DEVELOPMENT
SERGE SOLUTIONS
LEE L3
MARK ID
JACK L3
LILY QUALITY
BERNI SOLUTIONS
SELECT DEPT,
SUBSTR(NAMES, 1, LENGTH(NAMES) -1)
FROM (SELECT
DEPT,
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(NAME A, NAME)
ORDER BY NAME)
AS VARCHAR(60)), '<A>', ''), '</A>', ',') AS NAMES
FROM EMPLOYEE GROUP BY DEPT) AS X;
DEPT NAMES
----- --------------------
SOLUTIONS BERNI, MISO, SERGE
DEVELOPMENT JOHN
L3 JACK, LEE
ID MARK
QUALITY LILY

HTH,

--Jeff

Knut Stolze wrote:

meter_man wrote:

Is there a way to bring historical data into one field? I have activities
A1, A2, A3 ,etc in a history table. I need to check for the presence of
that data and bring it back if it exists for a record. I know I can do
this using (not tested):

With #temp as (
Select RecordID, Name from Table1 where . . . )

Select
RecordID
, Name
, substr((#A1.Activity || #A2.Activity || #A3.Activity),2) as History
from
#Temp T
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A1') as #A1 on T.RecordID = #A1.RecordID
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A2') as #A2 on T.RecordID = #A2.RecordID
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A3') as #A3 on T.RecordID = #A3.RecordID
I don't have permissions to do a Stored Proc and I have to concatenate
about
20 of these things. Is there an easier way so I can result in history
only reading like "A1, A3, A10" ?

Have a look at recursive queries and "Bill of materials" in the manual.
Alternatively, you can use the XMLAGGREGATE function to do the aggregation
from multiple rows into a single one.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[meter_man]

Thanks you both so much. I have never even heard of recursive queries and
XMLAGGREGATE! I will be checking this out.
:)
"jefftyzzer" <je********@sbcglobal.netwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

Here's an example applying a recursive CTE, using an algorithm from
Molinaro's _SQL Cookbook_ (note: best viewed with fixed font):

WITH
PARTICIPANT_NAME
(
PRTCP_NM_ID,
PRTCP_ID,
FIRST_NM
)
AS
(VALUES (1,1,'JEFF'),
(2,1,'JEFFREY'),
(3,2,'EDDIE'),
(4,2,'EDWARD'),
(5,2,'TED'),
(6,3,'ELIZABETH')
),
PRTCP_NM
(
PRTCP_ID,
CNT,
LIST,
PRTCP_NM_ID,
LEN
)
AS
(
SELECT
PN1.PRTCP_ID,
COUNT(*) OVER (PARTITION BY PN1.PRTCP_ID),
CAST(PN1.FIRST_NM AS VARCHAR(4000)),
PN1.PRTCP_NM_ID,
1
FROM
PARTICIPANT_NAME PN1
UNION ALL
SELECT
PRTCP_NM.PRTCP_ID,
PRTCP_NM.CNT,
PRTCP_NM.LIST||', '||PN2.FIRST_NM,
PN2.PRTCP_NM_ID,
PRTCP_NM.LEN + 1
FROM
PARTICIPANT_NAME PN2,
PRTCP_NM
WHERE
PN2.PRTCP_ID = PRTCP_NM.PRTCP_ID
AND
PN2.PRTCP_NM_ID PRTCP_NM.PRTCP_NM_ID
)
SELECT
LIST
FROM
PRTCP_NM
WHERE
LEN = CNT

and here's a really cool example--from this forum, BTW--of using the
XMLAGG function:

CREATE TABLE EMPLOYEE(NAME VARCHAR(15), DEPT VARCHAR(15));

NAME DEPT
----- ------
MISO SOLUTIONS
JOHN DEVELOPMENT
SERGE SOLUTIONS
LEE L3
MARK ID
JACK L3
LILY QUALITY
BERNI SOLUTIONS
SELECT DEPT,
SUBSTR(NAMES, 1, LENGTH(NAMES) -1)
FROM (SELECT
DEPT,
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(NAME A, NAME)
ORDER BY NAME)
AS VARCHAR(60)), '<A>', ''), '</A>', ',') AS NAMES
FROM EMPLOYEE GROUP BY DEPT) AS X;
DEPT NAMES
----- --------------------
SOLUTIONS BERNI, MISO, SERGE
DEVELOPMENT JOHN
L3 JACK, LEE
ID MARK
QUALITY LILY

HTH,

--Jeff

Knut Stolze wrote:

>meter_man wrote:

Is there a way to bring historical data into one field? I have
activities
A1, A2, A3 ,etc in a history table. I need to check for the presence
of
that data and bring it back if it exists for a record. I know I can do
this using (not tested):

With #temp as (
Select RecordID, Name from Table1 where . . . )

Select
RecordID
, Name
, substr((#A1.Activity || #A2.Activity || #A3.Activity),2) as History
from
#Temp T
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A1') as #A1 on T.RecordID = #A1.RecordID
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A2') as #A2 on T.RecordID = #A2.RecordID
left join (
Select ',' || Activity_CD as Activity from
#Temp T INNER JOIN History H on T.RecordID = H. RecordID
WHERE Activity_CD = 'A3') as #A3 on T.RecordID = #A3.RecordID
I don't have permissions to do a Stored Proc and I have to concatenate
about
20 of these things. Is there an easier way so I can result in history
only reading like "A1, A3, A10" ?

Have a look at recursive queries and "Bill of materials" in the manual.
Alternatively, you can use the XMLAGGREGATE function to do the
aggregation
from multiple rows into a single one.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany