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???

This type of question gets asked often and has been answered each time. Start here: how to concatenate rows without using table variabls and temp tables for this case

Andy

Use two LISTAGG, CAST to CLOB, then concatenate them.

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....

How about to replace "AS VARCHAR (20000)" with "AS CLOB(1M)"?

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???

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 ...

can you write me a query. or psuedo code..... I can't make sense of this.... Will be really grateful.....

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?

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