Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Question Unanswered: AS400 DB2 syntax for concat string in table column?

    I have one table on AS400 DB2 database something like with three columns...

    FirstName LastName Department

    James Anderson Computer Science
    James Anderson Information Technology
    James Anderson Information System
    Robert McCafee Heath Care
    Robert McCafee Biology
    Suman Kapoor Compter Science
    Suman Kapoor Information Technology
    Luke Morety Computer Science
    ........
    ........
    ........



    Final table I am looking is look something like.....

    FirstName LastName Department

    James Anderson Computer Science, Information Technology, Information System
    Robert McCafee Health Care, Bilology
    Suman Kapoor Computer Science, Information Technology
    Luke Morety Computer Science
    ......
    ......
    .....


    Any idea with AS400 DB2 syntax pls? thanks....

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use a recursive query to concatenate character strings in multiple rows.

    Looking and comparing manuals,
    recursive [common] table expressions are supported from DB2 for iSeries V5R4.

    SQL Reference ---> Chapter 4. Queries ---> select-statement ---> common-table-expression:

    DB2 Universal Database for iSeries SQL Reference Version 5 Release 3:
    If a fullselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive table expression. Recursive common table expressions are not supported in DB2 UDB for iSeries.
    DB2 for i5/OS SQL Reference Version 5 Release 4
    If a fullselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive table expression. Queries using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    I don't know much about iSeries DB2. Pls any one can post iSeries (AS400) DB2 "SELECT" statament for my above query pls.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The recursive query is not unique in DB2 for iSeries. It's common functionality of DB2. And it's conform to SQL standard.
    The SQL Reference for Cross-Platform Development

    So, you can find many samples in this forum(and another forums) and in "The SQL Reference for Cross-Platform Development".

    Anyway, I'll give you an example for your convenience.
    Although, this was not tested on iSeries DB2,
    you may be able to debug easily, if there were some syntax errors on iSeries, because I tried to use common syntax as much as i could.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data(id, FirstName, LastName, Department) AS (
    SELECT 1, 'James',  'Anderson', 'Computer Science'       FROM sysibm.sysdummy1 UNION ALL
    SELECT 2, 'James',  'Anderson', 'Information Technology' FROM sysibm.sysdummy1 UNION ALL
    SELECT 3, 'James',  'Anderson', 'Information System'     FROM sysibm.sysdummy1 UNION ALL
    SELECT 4, 'Robert', 'McCafee',  'Heath Care'             FROM sysibm.sysdummy1 UNION ALL
    SELECT 5, 'Robert', 'McCafee',  'Biology'                FROM sysibm.sysdummy1 UNION ALL
    SELECT 6, 'Suman',  'Kapoor',   'Compter Science'        FROM sysibm.sysdummy1 UNION ALL
    SELECT 7, 'Suman',  'Kapoor',   'Information Technology' FROM sysibm.sysdummy1 UNION ALL
    SELECT 8, 'Luke',   'Morety',   'Computer Science'       FROM sysibm.sysdummy1
    )
    /*
     End of sample data
    */
    ,numbered_data(
      id, FirstName, LastName, Department
    , rn, row_cnt
    ) AS (
    SELECT sample_data.*
         , INTEGER(
           ROWNUMBER()
             OVER(PARTITION BY FirstName, LastName)
           )
         , COUNT(*)
             OVER(PARTITION BY FirstName, LastName)
      FROM sample_data
    )
    ,recursive_CTE(
      id, FirstName, LastName, Department
    , rn, row_cnt
    ) AS (
    SELECT id, FirstName, LastName
         , CAST(Department AS VARCHAR(100) )
         , rn, row_cnt
      FROM numbered_data
     WHERE rn = 1
    UNION ALL
    SELECT pre.id, pre.FirstName, pre.LastName
         , pre.Department || ', ' || new.Department
         , pre.rn + 1
         , pre.row_cnt
      FROM recursive_CTE pre
         , numbered_data new
     WHERE new.FirstName = pre.FirstName
       AND new.LastName  = pre.LastName
       AND new.rn = pre.rn + 1
       AND pre.rn < 1000000000
    )
    SELECT FirstName, LastName, Department
      FROM recursive_CTE
     WHERE rn = row_cnt
     ORDER BY
           id
    ;
    ------------------------------------------------------------------------------
    
    FIRSTNAME LASTNAME DEPARTMENT                                                                                          
    --------- -------- ----------------------------------------------------------------------------------------------------
    James     Anderson Information System, Information Technology, Computer Science                                        
    Robert    McCafee  Biology, Heath Care                                                                                 
    Suman     Kapoor   Information Technology, Compter Science                                                             
    Luke      Morety   Computer Science                                                                                    
    
      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
  •