Need Elegant SQL query solution (DB2)

Hi Experts,
I have a DB2 table like this:

JOB | Sequence | TXT
123 |       1        | this is an ex
123 |       2        |ample to sho
123 |       3        |w my data.
124 |       1        |one line here
125 |       1        |another tes
125 |       2        |t

My goal is to retrieve a query with all of the text as one field so the example above would return 3 records.

123 | this is an example to show my data
124 | one line here
125 | another test

Any help is greatly appreciated.  Oh and I absolutely cannot change this table... and have no idea why it was done this way.

Thanks
-Jeremy
LVL 10
FamousMortimerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sathyaram_sCommented:
If you are on 9.7 FP4 you can use listagg

drop table rec ;

CREATE TABLE rec
(
  snum      INTEGER,        -- sentence number
  wordnum   INTEGER,        -- word number in the sentence
  word      VARCHAR(100)    -- word being saved
);

INSERT INTO rec
VALUES (1, 1, 'This'), (1, 2, 'is'), (1, 3, 'a'), (1, 4, 'fine'), (1, 5, 'example');


WITH rquery (snum, wordnum, sentence)
AS
(
  SELECT base.snum, base.wordnum, base.word
  FROM rec base
  WHERE wordnum = 1

  UNION ALL

  SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
  FROM rquery t0, rec t1
  WHERE t0. snum = t1. snum
    AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery
WHERE wordnum = (SELECT max(wordnum) FROM rquery);


 SELECT snum ,
LISTAGG(word,' ')       WITHIN GROUP(ORDER BY wordnum)
 FROM rec
      GROUP BY snum ;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

petr_hlucinCommented:
Since I don't know DB2 in depth I'm providing only 2 possible theoretical solutions:

1) Define custom aggregation function (e.g. CONCATE_AGGREGATE) which would concatenate the strings and run the following query:
SELECT CONCATENATE_AGGREGATE(TXT)
FROM  table
GROUP BY JOB
ORDER BY JOB, Sequence

I'm not sure whether DB2 supports it, PostgreSQL does.

2) Define a function with cursor returning such table, e.g. (pseudocode follows):
DEFINE CURSOR c1 FOR
SELECT * FROM table;
VARCHAR curr_txt;
INT curr_job;
BEGIN
OPEN c1;
WHILE FETCH(c1) INTO row DO
  IF row.JOB = curr_job THEN
    curr_txt := curr_txt + row.TXT;
  ELSE
    RETURN NEXT (curr_job, curr_txt);
    curr_txt := row.TXT;
    curr_job := row.JOB;
  END IF;
END WHILE
END;

Maybe somebody will complete this with DB2-specific stuff.
0
Dave FordSoftware Developer / Database AdministratorCommented:
In case you don't have the LISTAGG function, the following code works for me:

select *
  from rec

SNUM         WORDNUM   WORD   
   1               1   This   
   1               2   is     
   1               3   a      
   1               4   fine   
   1               5   example
   2               1   Yet    
   2               2   another
   2               3   example
   2               4   blah1  
   2               5   blah2  
   2               6   blah3  

WITH rquery (snum, wordnum, sentence) as (
 SELECT base.snum, base.wordnum, base.word
    FROM rec base
   WHERE wordnum = 1

  UNION ALL

  SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
    FROM rquery t0
    join rec t1
      on t0. snum = t1. snum
     AND t0.wordnum + 1 = t1.wordnum
)
select r1.SNUM,
       r1.SENTENCE
  from rquery r1             
 where r1.wordnum = (        
       select max(r2.wordnum)
       from rquery r2         
      where r2.snum = r1.snum)

SNUM   SENTENCE                             
   1   This is a fine example               
   2   Yet another example blah1 blah2 blah3

Open in new window


HTH,
DaveSlash
0
FamousMortimerAuthor Commented:
Thanks everyone for the quick responses.  I should have mentioned that i am a newbie at this and need a little more hand holding.  If someone would be so kind as to provide me a working example how to accomplish this using my example table that would be great.

My table is FILE110.DCTXT and the field names are JOB, SEQ, TXT

Thanks in advance
0
momi_sabagCommented:
WITH rquery (snum, wordnum, sentence)
AS
(
  SELECT base.job, base.seq, base.txt
  FROM FILE110.DCTXT base
  WHERE wordnum = 1

  UNION ALL

  SELECT t1.job, t1.seq, sentence || ' ' || t1.txt
  FROM rquery t0, FILE110.DCTXT t1
  WHERE t0. snum = t1. job
    AND t0.wordnum + 1 = t1.seq
)
SELECT *
FROM rquery
WHERE wordnum = (SELECT max(wordnum) FROM rquery);


 SELECT snum ,
LISTAGG(word,' ')       WITHIN GROUP(ORDER BY wordnum)
 FROM rec
      GROUP BY snum ;
0
Dave FordSoftware Developer / Database AdministratorCommented:
WITH rquery (job, seq, sentence) as (
 SELECT base.job, base.seq, base.text
    FROM FILE110.DCTXT base
   WHERE seq = 1

  UNION ALL

  SELECT t1.job, t1.seq, sentence || ' ' || t1.text
    FROM rquery t0
    join FILE110.DCTXT t1
      on t0. job = t1. job
     AND t0.seq + 1 = t1.seq
)
select r1.job,
       r1.SENTENCE
  from rquery r1             
 where r1.seq = (        
       select max(r2.seq)
       from rquery r2         
      where r2.job = r1.job)

Open in new window

0
FamousMortimerAuthor Commented:
OK, LISTAGG is not working for me, I do not know what version.

I was able to use and modify your code DaveSlash, however it is only returning the first and second in the sequence, any more than two does not concat.  Here is what i am using:

WITH rquery (snum, wordnum, sentence) as (
 SELECT base.TXJOB, base.TXSEQ, base.TXTXT
    FROM FILE110.DCTXT base
   WHERE TXSEQ = 1

  UNION ALL

  SELECT t1.TXJOB, t1.TXSEQ, sentence || ' ' || t1.TXTXT
    FROM rquery t0
    join FILE110.DCTXT t1
      on t0. snum = t1. TXJOB
     AND t0.wordnum + 1 = t1.TXSEQ
)
select r1.SNUM,
       r1.SENTENCE
  from rquery r1             
 where r1.wordnum = (        
       select max(r2.wordnum)
       from rquery r2         
      where r2.snum = r1.snum)

Open in new window

0
sathyaram_sCommented:
Can you see if this one works

WITH rquery (snum, wordnum, sentence)
AS
(
  SELECT base.snum, base.wordnum, base.word
  FROM rec base
  WHERE wordnum = 1

  UNION ALL

  SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
  FROM rquery t0, rec t1
  WHERE t0. snum = t1. snum
    AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery
WHERE wordnum = (SELECT max(wordnum) FROM rquery);

Open in new window

0
FamousMortimerAuthor Commented:
I get the same result as my previous post... it only joins the first two in the sequence
0
sathyaram_sCommented:
apologies for the error

--JOB | Sequence | TXT
--123 |       1        | this is an ex
--123 |       2        |ample to sho
--123 |       3        |w my data.
--124 |       1        |one line here
--125 |       1        |another tes
--125 |       2        |t
--
--My goal is to retrieve a query with all of the text as one field so the example above would return 3 records.
--
--123 | this is an example to show my data
--124 | one line here
--125 | another test
--

drop table mytest ;

create table mytest (job int,seq smallint,txt varchar(25))
;

insert into mytest values
(123,1,' this is an ex'
),
(123,2,'ample to sho'
),
(
123,3,'w my data.'
),
(124,1,'one line here'
),
(125,1,'another tes'
),
(125,2,'t'
) ;


WITH rquery (job, seq, txt)
AS
(
  SELECT base.job, base.seq, varchar(base.txt,255)
  FROM mytest base
  WHERE seq = 1

  UNION ALL

  SELECT t1.job, t1.seq, t0.txt|| '' || t1.txt
  FROM rquery t0, mytest t1
  WHERE t0.job = t1.job
    AND t0.seq+ 1 = t1.seq
)
SELECT *
FROM rquery
wHERE (job,seq) in (SELECT job,max(seq) FROM rquery group by job)

order by job
;

Open in new window


output is




----------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL0347W  The recursive common table expression "DB2INST1.RQUERY" may contain
an infinite loop.  SQLSTATE=01605

        123      3  this is an example to show my data.                                                                                                      
        124      1 one line here                                                                                                                            
        125      2 another test                                                                                                                              

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FamousMortimerAuthor Commented:
Beautiful... and elegant as requested
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.