Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: DB2 Really Complex query...

    ok guys buckle up .. here is the scenario...

    I have a table with three columns

    id, Company, Country , and project_number


    A company can have multiple projects in multiple country.....


    Now I have to create a view that shows all the projects of a company in a particular country... for example there copmany has id 1 and in country 2 it has 8 projects (1,2,3,4,5,6,7,8) then one row should return this...

    1| 2 |1;2;3;4;5;6;7;8


    how can I acheive this... I have tried the following code but it is can't handle the length it creates... The max length for the 3rd column will be 36789 and my varchar can't handle that much length ... any ideas???

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This type of question gets asked often and has been answered each time. Start here: http://www.dbforums.com/db2/1624731-...bles-case.html

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The max length for the 3rd column will be 36789 and my varchar can't handle that much length
    Use two LISTAGG, CAST to CLOB, then concatenate them.

  4. #4
    Join Date
    Jan 2012
    Posts
    82
    I am trying to do this

    REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(Name "d", company)) AS VARCHAR (20000)), '<d>', ' ')

    but getting this error

    [Error Code: -433, SQL State: 22001] Value "<d>HGGHGKJGK</d><d>JGHJGHJ</d><d>GFFGFJFG</d><d>" is too long.. SQLCODE=-433, SQLSTATE=22001, DRIVER=3.57.82


    I copied this code from somewhere... it is making a xml tree.... I just need ; between the numbers....

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about to replace "AS VARCHAR (20000)" with "AS CLOB(1M)"?

  6. #6
    Join Date
    Jan 2012
    Posts
    82
    Next I am trying to perform the following operation to get the desired result... won't clob make that fail....

    VARCHAR(REPLACE(REPLACE (REPLACE (REPLACE (REPLACE ( REPLACE(SUBSTR(company,1, LENGTH
    (company)-1 ),'</d>', ';'),'<d/>', ' ' ), '>', ' '),'<d/','') ,'</d',''),' ', '' )) AS
    CNTR_NUM...


    any suggestions???

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Apply XMLAGG(and REPLACE, so on) for each subset of company which are small enough(e.g. every 100 companies) to fit in VARCHAR, CAST to CLOB, then concatenate them,
    like ...
    Quote Originally Posted by tonkuma View Post
    Use two LISTAGG, CAST to CLOB, then concatenate them.

  8. #8
    Join Date
    Jan 2012
    Posts
    82
    can you write me a query. or psuedo code..... I can't make sense of this.... Will be really grateful.....

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    can you write me a query. or psuedo code.....
    OK. I think that I can do.

    Please publish DDL and INSERT statement to populate the table and expected result from them, to test queries.
    And, what DB2 version/release and platform OS are you using?

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    Apply XMLAGG(and REPLACE, so on) for each subset of company which are small enough(e.g. every 100 companies) to fit in VARCHAR, CAST to CLOB, then concatenate them,
    like ...
    Another way might be to use SYSFUN.REPLACE which accepts CLOB for the arguments.

    Please see Information Center
    REPLACE - IBM DB2 9.7 for Linux, UNIX, and Windows
    REPLACE scalar function

    ...

    The schema is SYSIBM. The SYSFUN version of the REPLACE function continues to be available but it is not sensitive to the database collation.

Posting Permissions

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