select query involving data in extended tables

onida · #1 (**permalink**) 06-28-11, 12:25

Hi All,

I am in trouble and need help in building this query.
I have following 2 tables, data_main and data_ext(extended table) linked with dataky column. And I want to write select query on data column value of these 2 tables. data column value may and may not be present in extended table(data_ext). If data column value is present in extended table(data_ext) it could be in more than one rows of it ordered by seqnum column value.

HTML Code:

data_main
--------------------
dataky| data       |    
--------------------
1     |Life is prec|
2     |Japan is lan|
3     |I love my fa|
4     |No, I won't.|
--------------------

data_ext
---------------------------
dataky| seqnum|data       |    
---------------------------
1     |  1    |ious so dri|
1     |  2    |ve carefull|
1     |  3    |y.         | 
2     |  1    |d of sunris|
2     |  2    |e.         |
3     |  1    |mily and fr|
3     |  2    |iends.     |
---------------------------

Expected output -
Life is precious so drive carefully.
Japan is land of sunrise.
I love my family and friends.
No, I won't.

Thanks and Regards,
Onida

ARWinner · #2 (**permalink**) 06-28-11, 13:13

You need to write a recursive query. There are several examples in this forum. You can also look here: DB2 SQL Cookbook

Andy

tonkuma · #3 (**permalink**) 06-28-11, 13:20

Here is an example using LISTAGG.
(If you are not using DB2 9.7 Fixpack 4 for LUW, you can use XMLGROUP or XMLAGG instead of LISTAGG.)

Code:

SELECT m.data
       ||
       CAST(
          COALESCE(
             LISTAGG(e.data)
                WITHIN GROUP(ORDER BY e.seqnum)
           , ''
          )
          AS VARCHAR(50)
       ) AS " "
 FROM  data_main m
 LEFT OUTER JOIN
       data_ext  e
   ON  e.dataky = m.dataky
 GROUP BY
       m.dataky
     , m.data
 ORDER BY
       m.dataky
;
------------------------------------------------------------------------------

                                                              
--------------------------------------------------------------
Life is precious so drive carefully.                          
Japan is land of sunrise.                                     
I love my family and friends.                                 
No, I won't.                                                  

  4 record(s) selected.

tonkuma · #4 (**permalink**) 06-28-11, 14:32

An example using XMLAGG:

Code:

SELECT m.data
       ||
       REPLACE(REPLACE(REPLACE(
          XMLSERIALIZE(
             XMLAGG(
                XMLELEMENT(NAME d , e.data)
                ORDER BY e.seqnum
             )
             AS VARCHAR(50)
          )
        , '<D>' , '') , '</D>' , '') , '<D/>' , ''
       ) AS " "
 FROM  data_main m
 LEFT OUTER JOIN
       data_ext  e
   ON  e.dataky = m.dataky
 GROUP BY
       m.dataky
     , m.data
 ORDER BY
       m.dataky
;
------------------------------------------------------------------------------

                                                              
--------------------------------------------------------------
Life is precious so drive carefully.                          
Japan is land of sunrise.                                     
I love my family and friends.                                 
No, I won't.                                                  

  4 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech