Select and merge multiple columns in DB2

Question

here is my table:

ID	SEQ	DATE_FROM	DATE_TO	NOTES
ELEM1	1	2022-04-01	2023-03-31	First part
ELEM1	2	2022-04-01	2023-03-31	Second part
ELEM1	3	2022-04-01	2023-03-31	Third part
ELEM2	1	2018-11-12	2099-12-31	First part
ELEM2	2	2018-11-12	2099-12-31	Second part
ELEM2	3	2018-11-12	2099-12-31	Third part
ELEM2	4	2018-11-12	2099-12-31	Forth part

I'd like to return each ID by merging and concatenating SEQ and NOTES, so that I select only ID, DATE_FROM, DATE_TO, NOTES like that:

ID	DATE_FROM	DATE_TO	NOTES
ELEM1	2022-04-01	2023-03-31	First part Second part Third part
ELEM2	2018-11-12	2099-12-31	First part Second part Third part Forth part

Then, I'll insert that result to another table!

I tried to use GROUP BY, but I'm not good at SQL queries and I don't know how to use it:

SELECT ID, DATE_FROM, DATE_TO, NOTES
FROM MYTABLE
GROUP BY ID, NOTES

Thanks

Does this answer your question? [DB2 Comma Separated Output by Groups](https://stackoverflow.com/questions/7188542/db2-comma-separated-output-by-groups). You can adjust it to not use a comma as a separator but it should give you the correct idea. — Mushroomator, Mar 16 '22 at 10:29
I tried to use `LISTAGG()`, but I get `ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION IS ACTIVATE`. Maybe I use it wrongly — Briston12, Mar 16 '22 at 10:36

nfgl · Answer 1 · 2022-03-16T14:19:08.343

If LISTAGG is not available maybe recursive CTE are. The following query relies on

if SEQ > 1 exists then SEQ - 1 also exists
SEQ is unique

query:

with MYTABLE (ID, SEQ, DATE_FROM, DATE_TO, NOTES) as (
  values
  ('ELEM1', 1, date '2022-04-01', date '2023-03-31', 'First part'),
  ('ELEM1', 2, date '2022-04-01', date '2023-03-31', 'Second part'),
  ('ELEM1', 3, date '2022-04-01', date '2023-03-31', 'Third part'),
  ('ELEM2', 1, date '2018-11-12', date '2099-12-31', 'First part'),
  ('ELEM2', 2, date '2018-11-12', date '2099-12-31', 'Second part'),
  ('ELEM2', 3, date '2018-11-12', date '2099-12-31', 'Third part'),
  ('ELEM2', 4, date '2018-11-12', date '2099-12-31', 'Forth part')
),
allnotes (ID, seq, ALLNOTES) as (
  select ID, seq, varchar(notes, 4000) from MYTABLE where seq = 1
  union all
  select mytable.id, mytable.seq, allnotes.allnotes || ' ' || mytable.notes from allnotes, mytable where (mytable.id, mytable.seq) = (allnotes.id, allnotes.seq + 1)
),
groups as (
  select id, min(date_from) date_from, max(date_to) date_to, max(seq) seq from mytable group by id
)
select groups.id, date_from, date_to, allnotes from groups inner join allnotes on (allnotes.id, allnotes.seq) = (groups.id, groups.seq)

DB<>Fiddle

VBoka · Answer 2 · 2022-03-16T10:57:27.053

1

AS it was already written in comments you need to use LISTAGG FUNCTION. I am not sure how did you used it but it should work for you.

Here is the demo

And here is the code from that demo:

select id, min(dat_from), max(dat_to), listagg(notes, ',')
from test
group by id

Watch out which columns you use in group by. You can not group by column Notes and use it in listagg function.

If your title is correct "Select and merge multiple columns in DB2" then you can concatenate two columns like this:

select id, min(dat_from), max(dat_to), listagg(notes|| ' ' || id, ',')
from test
group by id

But you have shown us your desired result where we can assume you do not want this...

edited Mar 16 '22 at 10:57

answered Mar 16 '22 at 10:52

VBoka

8,995
3
16
24

Thanks for that, but unfortunately `listagg` does not work for me, not sure about the reason! Is there any alternative to it? – Briston12 Mar 16 '22 at 11:00
What does it mean "does not work for me"? Edit your question with your exact SELECT statement and provide exact error code and message text you get. – Mark Barinstein Mar 16 '22 at 11:34
I used the exact first query @VBoka provided and the result is `SQL ERROR [56038]: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION IS ACTIVATED. SQLCODE=-4700, SQLSTATE=56038, DRIVER=4.26.14` – Briston12 Mar 16 '22 at 12:12
Hi @Briston12, Please can you check this link : https://www.idug.org/blogs/daniel-luksetich1/2020/11/04/ibm-data-server-driver-and-db2-12-for-zos-for-jdbc-type-4-connections-using-application-compatibility SEARCH FOR THE PART ABOUT LISTAGG. I am not sure if this helps... – VBoka Mar 16 '22 at 12:40

score 1 · Answer 3 · answered Mar 16 '22 at 15:35

SQLCODE = -4700:

Explanation

An attempt was made to use a new capability before the required function level is activated. Capabilities that are introduced in a Db2 function level cannot be used before that function level is activated.

Seems, that you use Db2 for Z/OS 12 (and not Db2 for LUW 10.5.0.9 as you mentioned), where LISTAGG is available with FL501 which is not activated in your system.

Try XMLAGG as alternative. This should work as is, if you uncomment the commented out block.

/*
WITH MYTABLE (ID, SEQ, DATE_FROM, DATE_TO, NOTES) AS
(
          SELECT 'ELEM1', 1, '2022-04-01', '2023-03-31', 'First part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM1', 2, '2022-04-01', '2023-03-31', 'Second part' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM1', 3, '2022-04-01', '2023-03-31', 'Third part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 1, '2018-11-12', '2099-12-31', 'First part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 2, '2018-11-12', '2099-12-31', 'Second part' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 3, '2018-11-12', '2099-12-31', 'Third part'  FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ELEM2', 4, '2018-11-12', '2099-12-31', 'Forth part'  FROM SYSIBM.SYSDUMMY1
)
*/
SELECT 
  ID
, MIN (DATE_FROM) AS DATE_FROM
, MAX (DATE_TO)   AS DATE_TO
, SUBSTR (XMLSERIALIZE (XMLAGG (XMLTEXT (' ' || NOTES) ORDER BY SEQ) AS VARCHAR (1000)), 2) AS NOTES
FROM MYTABLE
GROUP BY ID

Select and merge multiple columns in DB2

3 Answers3