var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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???
This type of question gets asked often and has been answered each time. Start here:
Use two LISTAGG, CAST to CLOB, then concatenate them.
The max length for the 3rd column will be 36789 and my varchar can't handle that much length
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
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,
Originally Posted by
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.
can you write me a query. or psuedo code.....
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.
Originally Posted by
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.