Solved

Need Elegant SQL query solution (DB2)

Posted on 2012-03-12
13
833 Views
Last Modified: 2012-03-12
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
0
Comment
Question by:FamousMortimer
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 4

Expert Comment

by:sathyaram_s
ID: 37710225
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37710230
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37710236
0
 
LVL 4

Expert Comment

by:petr_hlucin
ID: 37710268
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
 
LVL 18

Expert Comment

by:daveslash
ID: 37710924
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
 
LVL 10

Author Comment

by:FamousMortimer
ID: 37711099
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 37711130
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
 
LVL 18

Expert Comment

by:daveslash
ID: 37711437
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
 
LVL 10

Author Comment

by:FamousMortimer
ID: 37711513
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
 
LVL 4

Expert Comment

by:sathyaram_s
ID: 37711530
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
 
LVL 10

Author Comment

by:FamousMortimer
ID: 37711834
I get the same result as my previous post... it only joins the first two in the sequence
0
 
LVL 4

Accepted Solution

by:
sathyaram_s earned 500 total points
ID: 37711899
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
 
LVL 10

Author Closing Comment

by:FamousMortimer
ID: 37711959
Beautiful... and elegant as requested
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now