How do I concatenate strings from a column into a single row in DB2

[mark83anthony]

How do I concatenate strings from a column into a single row?

Whats the logic to create the function in DB2.

Given is below.

Color
------
red
orange
blue
green

And return a resultset like this:

Colors
-------------------------
red,orange,blue,green

[mark83anthony]

On Nov 19, 2:28*pm, [e-mail] wrote:

How do I concatenate strings from a column into a single row?

Whats the logic to create the function in DB2.

Given is below.

Color
------
red
orange
blue
green

And return a resultset like this:

Colors
-------------------------
red,orange,blue,green

I will make it clear.

The table name is Colors
CREATE TABLE Colors
(
Color VARCHAR(32)
)

Values in the Table are
Color
------
red
orange
blue
green
My output should be when I do a Select * from Tabname, shd be

red,orange,blue,green
Waiting for your reply

Thanks in Advancd

[jefftyzzer]

On Nov 19, 12:49*pm, [e-mail] wrote:

On Nov 19, 2:28*pm, [e-mail] wrote:

How do I concatenate strings from a column into a single row?

Whats the logic to create the function in DB2.

Given is below.

Color
------
red
orange
blue
green

And return a resultset like this:

Colors
-------------------------
red,orange,blue,green

I will make it clear.

The table name is Colors
CREATE TABLE Colors
(
* * Color VARCHAR(32)
)

Values in the Table are
Color
------
red
orange
blue
green

My output should be when I do a Select * from Tabname, shd be

red,orange,blue,green

Waiting for your reply

Thanks in Advancd

Mark:

Have a look at a posting to this newsgroup titled "concatenating
historical records" from October '07. In it, I listed two ways I know
of/have learned to do this.

--Jeff

[mark83anthony]

On Nov 19, 3:53*pm, jefftyzzer <[e-mail]:

On Nov 19, 12:49*pm, [e-mail] wrote:

On Nov 19, 2:28*pm, [e-mail] wrote:

How do I concatenate strings from a column into a single row?

Whats the logic to create the function in DB2.

Given is below.

Color
------
red
orange
blue
green

And return a resultset like this:

Colors
-------------------------
red,orange,blue,green

I will make it clear.

The table name is Colors
CREATE TABLE Colors
(
* * Color VARCHAR(32)
)

Values in the Table are
Color
------
red
orange
blue
green

My output should be when I do a Select * from Tabname, shd be

red,orange,blue,green

Waiting for your reply

Thanks in Advancd

Mark:

Have a look at a posting to this newsgroup titled "concatenating
historical records" from October '07. In it, I listed two ways I know
of/have learned to do this.

--Jeff- Hide quoted text -

- Show quoted text -

Hi,

I tried doing this

SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
Color))),'<A>',''),'</A>',' ')
FROM Colors;

It works fine with my DB2 9.5 client on my machine but does not work
with Db2 Z OS 9.1.

It gives me the following error message

SQL0171N The data type, length or value of argument "1" of routine
"REPLACE"
is incorrect. SQLSTATE=42815

SQL0171N The data type, length or value of argument "1" of routine
"REPLACE" is incorrect.

Explanation:

The data type, length or value of argument "<n>" of routine "<name>"
is
incorrect.

The statement cannot be processed.

User response:

Ensure the arguments of the routine conform to the rules of the
routine.

sqlcode: -171

sqlstate: 42815

[jefftyzzer]

On Nov 19, 2:22*pm, [e-mail] wrote:

On Nov 19, 3:53*pm, jefftyzzer <[e-mail]:

On Nov 19, 12:49*pm, [e-mail] wrote:

On Nov 19, 2:28*pm, [e-mail] wrote:

How do I concatenate strings from a column into a single row?

Whats the logic to create the function in DB2.

Given is below.

Color
------
red
orange
blue
green

And return a resultset like this:

Colors
-------------------------
red,orange,blue,green

I will make it clear.

The table name is Colors
CREATE TABLE Colors
(
* * Color VARCHAR(32)
)

Values in the Table are
Color
------
red
orange
blue
green

My output should be when I do a Select * from Tabname, shd be

red,orange,blue,green

Waiting for your reply

Thanks in Advancd

Mark:

Have a look at a posting to this newsgroup titled "concatenating
historical records" from October '07. In it, I listed two ways I know
of/have learned to do this.

--Jeff- Hide quoted text -

- Show quoted text -

Hi,

I tried doing this

SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
Color))),'<A>',''),'</A>',' ')
FROM Colors;

It works fine with my DB2 9.5 client on my machine but does not work
with Db2 Z OS 9.1.

It gives me the following error message

SQL0171N *The data type, length or value of argument "1" of routine
"REPLACE"
is incorrect. *SQLSTATE=42815

SQL0171N *The data type, length or value of argument "1" of routine
"REPLACE" is incorrect.

Explanation:

The data type, length or value of argument "<n>" of routine "<name>"
is
incorrect.

The statement cannot be processed.

User response:

Ensure the arguments of the routine conform to the rules of the
routine.

*sqlcode: -171

*sqlstate: 42815

Well, notwithstanding a simple fix for the problem you're having with
the XML function, assuming that DB2 9.1 for Z/OS supports recursive
common table expressions (CTEs), you could try the other technique
demonstrated in the earlier posting I mentioned.

--Jeff

[Tonkuma]

DB2 for z/OS doesn't support casting XML data type to another data
type.
(See Table 13. Supported casts between built-in data types
on Page 80 of "DB2 Version 9.1 for z/OS SQL Reference".)
Also, DB2 for z/OS doesn't support XML2CLOB function.

[Tonkuma]

I have a Table called Colors both in Db2 9.5 for LUW and DB2 9.1 on Z/OS

SELECT replace(replace(xmlagg(xmlelement(NAME a,NAME)),'<A>',' ' ),'</A>',' ') FROM TECPG01.TEST1111

It works fine with db2 on LUW but generates an error for Db2 on z/OS
saying that argument 1 of the routine REPLACE is wrong.

It was neccesary to add an XML2CLOB function on DB2 9.1 for LUW, like
this:
SELECT replace(replace(XML2CLOB(xmlagg(xmlelement(NAME a,
color))),'<A>', ''), '</A>', ' ') FROM Colors

I got error message SQL0440N for
SELECT replace(replace(xmlagg(xmlelement(NAME a, color)),'<A>', ''),
'</A>', ' ') FROM Colors

SQL0440N No authorized routine named "REPLACE" of type "FUNCTION"
having compatible arguments was found.