Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: select query involving data in extended tables

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to write a recursive query. There are several examples in this forum. You can also look here: DB2 SQL Cookbook

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •