Sravanthi Lacky wrote:
> I have a comments table that has multiple comment records for the same Code.
>
> Is there a way to get the concatenated string of all comments for a
> particular code in query in DB2UDB ??
>
> Ex :
>
> Table wc_cmnts has a column called wc_cd (VARCHAR(30) and a column called
> seq_no (int) and a column called cmnts (VARCHAR(1000).
>
> There can be multiple records for the same wc_cd with different seq_no.
>
> I want a query to concatenate all the cmnts for a particular wc_cd in the
> order of the seq_no.
>
> Can it be done ??
Sure, but since you've got n-number of rows you'll want to use
recursion. Graeme Birchall's DB2 Cookbook has the best description of
recursion with db2 available. It's also free and the best db2 reference
out there:
http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM
Anyhow, here's a code snippet i'm using for the exact same reason.
Recursive queries can be complex, you'll definitely want to look at some
other examples to make sense out of the following.
----------------------------------------------------------------------------------
-- temp table: partnum
-- create temp table with one row / software
-- each row is numbered with the row_number() function
-- the numbering is done in such a way to order software alphabetically
-- it could also allow another column to be used - to order by priority
-- later on the grouping eliminates complete duplicates, though
-- near-dups remain.
----------------------------------------------------------------------------------
WITH partnum AS (
SELECT part ,
sw_type ,
sw_name ,
row_number() over(partition by part, sw_type
ORDER BY part, sw_type, sw_name) as rnum
FROM ref_part_software
GROUP BY part ,
sw_type ,
sw_name ),
----------------------------------------------------------------------------------
-- temp table: partserv
-- create view that concatenates services into the service-list string.
-- For any given part, each row will have one additional service
-- appended to this string. since this is a recursive query, it has to
-- name the output cols in the paranthesis after the table name.
----------------------------------------------------------------------------------
partserv (serv_part, serv_list, serv_cnt, serv_max) AS (
SELECT part ,
VARCHAR('', 2000) ,
0 ,
MAX(rnum)
FROM partnum
where sw_type = 'service'
GROUP BY part
UNION ALL
SELECT serv_part ,
CASE
WHEN LENGTH(RTRIM(serv_list)) > 0 THEN
serv_list || ', '
|| RTRIM(CHAR(partnum.sw_name))
ELSE serv_list || RTRIM(CHAR(partnum.sw_name))
END AS serv_list ,
serv_cnt + 1 ,
serv_max
FROM partnum,
partserv
WHERE part = serv_part
AND sw_type = 'service'
AND rnum = serv_cnt + 1
AND serv_cnt < serv_max ),
----------------------------------------------------------------------------------
-- temp table: part_list
-- create table that creates one row per part number
-- once again, it's using recursion to add one row at a time until it
-- gets to 10000
----------------------------------------------------------------------------------
part_list (part) AS (
VALUES INTEGER(0)
UNION ALL
SELECT part + 1
FROM part_list
WHERE part < 10000 )
----------------------------------------------------------------------------------
-- final query: uses part_list to create an output with 10,000 rows,
-- then adds whatever service_list exist to each row.
----------------------------------------------------------------------------------
SELECT part ,
'unknown' AS part_category,
RTRIM(CAST(serv_list AS VARCHAR(2000))) AS serv_list ,
COALESCE(serv_cnt,0) AS serv_cnt ,
COALESCE(LENGTH(serv_list),0) AS serv_list_len
FROM part_list pl
LEFT OUTER JOIN partserv ps
ON pl.part = serv_part
AND serv_cnt = serv_max
;
ken farmer
DB2 & UDB email list listserv db2-l LazyDBA home page