SQL concatenate rows into one field (DB2) With Filters

Question

I am using DB2 V7r1 (so no listagg unfortunatly). I need to be able to have all the descriptions for the item(ItemNum) be in one string comma separated. I have a Query I found that kind of works but I can not filter the result by item number. It will work fine with the first 100 rows of data but if I try to filter out an item that is maybe 100,000 rows down the table, it takes forever.

*EDIT, I should add that this table contains 1,460,072 records, and one Item/Operation may have up to 60 Description entries, so if any one knows of a way to maybe pre-filter the results or a more efficient way to do this i would appreciate it greatly

Here is my table: PARTS

ItemNum      OpSequence      DscNum    Description
A-123         10               2       Desc Line 1
A-123         10               4       Desc Line 2
A-123         10               6       Desc Line 3
A-123         20               2       Desc Line 1
A-123         20               4       Desc Line 2
Z-555         10               2       Desc Line 1
Z-555         10               4       Desc Line 2

Here is the result I need (Need to filter by ItemNum and OpSequence)

ItemNum     OpSequence     Description
A-123        10            Desc Line 1, Desc Line 2, Desc Line 3

Here is the Query I used

 with x (ItemNum, OpSequence, cnt, list, empno, len) as
 (select z.ItemNum, z.OpSequence,
   (select count(*) from PARTS y 
    where y.ItemNum=z.ItemNum  
    group by y.ItemNum),
  cast(z.Description as varchar(100)),
  rrn(z), 1  
  from PARTS z 
  where z.ItemNum = 'A-123'  (HERE IS WHERE I AM TRYING TO FILTER)
 union all  
  select x.ItemNum,
     x.OpSequence,
     x.cnt,
     strip(x.list) ||', '|| e.Description,
     rrn(e),
     x.len+1   
  from PARTS e, x 
  where e.ItemNum = x.ItemNum and rrn(e) > x.empno   
 )  
 select ItemNum,OpSequence, list
 from x 
 where len=cnt

What does the explain plan look like. Is the filter repeated on the second UNION ALL branch? If not can you add it into your code (with a second CTE)? What is the max value of DscNum? If it is reasonable, just `MAX()` a bunch of `CASE` statements — Paul Vernon, Aug 14 '18 at 20:25
the DscNum field does not have a max value. it is just a sequence number for the description field. I don't follow what you mean by explain plan — Mmbauer, Aug 15 '18 at 00:19

score 1 · Answer 1 · answered Aug 15 '18 at 05:22

1

Although you do not have the listagg functionality XML functions will solve your problem. Before listagg was made available XMLAGG (and XMLGROUP) had been used and these are availible in DB2 V7r1.

Check out

answered Aug 15 '18 at 05:22

MichaelTiefenbacher

3,805
2
11
17

The `XMLAGG` link above works for DB2 for IBM i at v7.1. But the `XMLGROUP` solution does not. The proposed `XMLGROUP` link depends on `XMLCAST` to remove XML tags, but at v7.1 you can only cast to an XML datatype, so it does not remove the tags. – jmarkmurphy Aug 15 '18 at 16:47

score 0 · Answer 2 · answered Aug 15 '18 at 09:25

If you have a limited number of Lines for any given item, this SQL would work for you.. you would need to extend it if you have more than 11 lines for example

SELECT ItemNum, OpSequence
,      MAX(CASE WHEN DscNum = 1 THEN         Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 2 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 3 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 4 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 5 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 6 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 7 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 8 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum = 9 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum =10 THEN ', ' || Description ELSE '' END)
    || MAX(CASE WHEN DscNum =11 THEN ', ' || Description ELSE '' END)
        AS Description
FROM
    PARTS
WHERE
    ItemNum = 'A-123'
GROUP BY
    ItemNum, OpSequence

score 0 · Answer 3 · answered Aug 17 '18 at 12:16

I've used XMLAGG on iSeries V7R1 for something similar, try the below to get started. Note the below doesn't account for your need to get the minimum OpSequence but does get you a comma delimited list of the description field.

SELECT    ItemNum,
          OpSequence,
          DscNum,
          TRIM(REPLACE(  
                REPLACE(  
                  REPLACE(  
     XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "x", TRIM(Description)) ) AS VARCHAR(1000))  
     , '</x><x>', ',')  
     , '<x>', '')  
     , '</x>', '')) AS Description
FROM x
GROUP BY ItemNum, OpSequence, DscNum

SQL concatenate rows into one field (DB2) With Filters

3 Answers3