Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: Aggregating string separated by comma in DB2Z/OSV10 without using XMLAGG and XMLGROUP

    I have one requirment like below.

    Slno batchno
    1 111
    1 222
    2 145
    2 235
    2 335
    3 777
    4 888
    4 999
    My desired output would be like this.

    1 111,222
    2 145,235,335
    3 777
    4 888,999

    I am using DB2 for Z/OS V10.I have checked few examples which are being used with XMLAGG and XMLGROUP functions which are not supported in DB2 V10.

    Can anyone please help me to find the desired output?
    Last edited by rkp; 08-28-12 at 11:20.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 10.1 for z/OS supports XMLAGG, depending on Information Center.
    DB2 10 - DB2 SQL - XMLAGG

    XMLAGG was suppored from DB2 V8 for z/OS.
    DB2 V8 - DB2 SQL - XMLAGG

  3. #3
    Join Date
    Aug 2012
    Posts
    24
    Thanks for the reply.

    Could you please more elaborate on "depending on Information Center".

    I am using IBM data studio 2.2.

    I have executed the below query.

    1) USING XMLAGG

    SELECT slno,
    replace(
    replace(
    replace(
    xmlserialize(XMLAGG(XMLELEMENT(NAME "x", batchno) ) as varchar(1000))
    , '</x><x>', ',')
    , '<x>', '')
    , '</x>', '') as batchno_T
    FROM Table1
    GROUP BY slno;

    ERROR:

    42601(-199)[IBM][CLI Driver][DB2] SQL0199N The use of the reserved word "VARCHAR" following "VARCHAR" is not valid. Expected tokens may include: "CLOB CHAR CHARACTER BLOB BINARY DBCLOB". SQLSTATE=42601

    2) USING XMLGROUP

    SELECT slno,SUBSRT(XMLCAST(XMLGROUP(',' || batchno ORDER BY batchno)
    AS VARCHAR(60)),2) AS batchno_TEMP
    FROM table1
    GROUP BY slno;

    ERROR:

    42884(-440)[IBM][CLI Driver][DB2] SQL0440N No authorized routine named "XMLGROUP" of type "XMLGROUP" having compatible arguments was found. SQLSTATE=42884

    Can anyone please look into the above two XML function uses and reply me?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ERROR:

    42601(-199)[IBM][CLI Driver][DB2] SQL0199N The use of the reserved word "VARCHAR" following "VARCHAR" is not valid. Expected tokens may include: "CLOB CHAR CHARACTER BLOB BINARY DBCLOB". SQLSTATE=42601
    Error message text suggested a solution, like...
    Expected tokens may include: "CLOB CHAR CHARACTER BLOB BINARY DBCLOB"
    Please see XMLSERIALIZE function in Information Center
    DB2 10 - DB2 SQL - XMLSERIALIZE

    data-type

    Code:
    >>-+-+-+-CHARACTER-+--LARGE OBJECT-+-+-------------------------->
       | | '-CHAR------'               | |   
       | '-CLOB------------------------' |   
       +-DBCLOB--------------------------+   
       '-+-BINARY LARGE OBJECT-+---------'   
         '-BLOB----------------'             
    
       .-(--1M--)-------------.   
    >--+----------------------+------------------------------------><
       '-(--integer--+---+--)-'   
                     +-K-+        
                     +-M-+        
                     '-G-'

  5. #5
    Join Date
    Aug 2012
    Posts
    24
    I am getting the desired XML output by executing below query.

    SELECT slno, XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "x", A.batchno) ) AS CLOB(100)) as batchno_T FROM Table1 GROUP BY slno;

    But while trying to replace the xml values by comma(,) in the below query getting error output.

    SELECT slno,
    replace(replace(replace(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "x", A.batchno) ) AS CLOB(100))
    , '</x><x>', ',') , '<x>', '') , '</x>', '') as batchno_T
    FROM Table1 GROUP BY slno;

    ERROR:

    42815(-171)[IBM][CLI Driver][DB2] SQL0171N The data type, length or value of the argument for the parameter in position "1" of routine "REPLACE" is incorrect. Parameter name: "REPLACE".

    Can you please help me on this?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see Manuals or Information Center, like
    DB2 10 - DB2 SQL - REPLACE

    The reason of the SQL0171N may be CLOB in position "1" of routine "REPLACE".


    Here is another example not using REPLACE.
    http://www.dbforums.com/db2/1680028-...e-comma-2.html
    #20 or #23

  7. #7
    Join Date
    Aug 2012
    Posts
    24
    Thanks for ur reply.

    After doing some modifications to my above query,now i am able to get the desired output by running the below query.

    FINAL QUERY:-

    SELECT slno,replace(replace(replace(cast(XMLSERIALIZE(CON TENT XMLAGG(XMLELEMENT(NAME "x", A.batchno) order by A.batchno) AS CLOB(100)) as varchar(1000)), '</x><x>', ',') , '<x>', '') , '</x>', '') as batchno_T
    FROM Table1 GROUP BY slno;

    OUTPUT:-

    slno batchno
    1 111,222
    2 145,235,335
    3 777
    4 888,999

    Thank you very much for ur help.

Posting Permissions

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