469,360 Members | 1,774 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

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

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
Nov 19 '08 #1
6 13019
On Nov 19, 2:28*pm, mark83anth...@gmail.com 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
Nov 19 '08 #2
On Nov 19, 12:49*pm, mark83anth...@gmail.com wrote:
On Nov 19, 2:28*pm, mark83anth...@gmail.com 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
Nov 19 '08 #3
On Nov 19, 3:53*pm, jefftyzzer <jefftyz...@sbcglobal.netwrote:
On Nov 19, 12:49*pm, mark83anth...@gmail.com wrote:


On Nov 19, 2:28*pm, mark83anth...@gmail.com 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
Nov 19 '08 #4
On Nov 19, 2:22*pm, mark83anth...@gmail.com wrote:
On Nov 19, 3:53*pm, jefftyzzer <jefftyz...@sbcglobal.netwrote:
On Nov 19, 12:49*pm, mark83anth...@gmail.com wrote:
On Nov 19, 2:28*pm, mark83anth...@gmail.com 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
Nov 19 '08 #5
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.
Nov 21 '08 #6
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.
Nov 21 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by SWN | last post: by
3 posts views Thread by Lars Tackmann | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.