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 6 13484
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
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
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
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
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jay Chan |
last post by:
I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely....
|
by: SWN |
last post by:
hi.
Trying to concatenate two columns:
select uname+' '+uaddress as NameAdr from tblUser
I only get the first field, name!!??
The datatype is both nvarchar. 100 and 50 chars long.
If I...
|
by: Lars Tackmann |
last post by:
Hi - I need a function to concatenate a variable number of strings.
I have two ideas but cannot deside which way to go.
1) use the "stdarg" macros - if i use these macros it will be easy
to step...
|
by: jt |
last post by:
I can't seem to find a way to concatenate strings that have nulls within the
string.
I have a string that I need another string that has nulls in it and what to
append
the 2nd string, 3 string...
|
by: Diego Martins |
last post by:
Since C++ (and STL) have many ways to do string concatenation, I want
to hear (read) from you how you do to concatenate strings with other
strings and other types. The approaches I know are:
--...
|
by: Dave-o |
last post by:
New learner.
For the print command and concatenation, the period is difficult for
my afflicted eyes to see, but the "+" seems to work. Is this an
accepted PHP convention?
Thank you,
David
|
by: Markus |
last post by:
Hi, guys.
I'm receiving a dynamic amount of strings and from these strings I need to make a single string containing them all. However, I need to preserve the NULL characters to separate the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |