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

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

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.

On Nov 21, 7:52*am, Tonkuma <tonk...@fiberbit.net> wrote:
> > 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.

Hi,

thanks for replying.

Yeah, u are true, It was necessary to use a xml2clob in order to get
the result on Db2 9.1 for LUW.

But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
TECPG01.TEST1111 on Db2 9.1 for z/OS I was able to get the same
result as in using XML2CLOB with above for Db2 LUW.

I went over the syntax of Replace. replace function accepts only
expressions as arguments in Db2 for LUW
but Replace functions accepts only string aruguments in db2 for z/OS.

Does this anyway is effecting what I wanted to achieve.

If yes, How can I convert an expression to a string argument.

> Yeah, u are true, It was necessary to use a xml2clob in order to get
> the result on Db2 9.1 for LUW.
>
> But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
> TECPG01.TEST1111 on Db2 9.1 for z/OS *I was able to get the same
> result as in using XML2CLOB with above for Db2 LUW.
I got same result by using Command Editor on DB2 LUW 9.1 and 9.5
without using XML2CLOB function.
But, the data type must be XML and I guessed that Command Editor
converted the result into displayable format.
Because, the statement "SELECT SUBSTR(XMLAGG(XMLELEMENT(NAME a, NAME)),
1,100) from Colors;", returned error message SQL0440N.
It is neccesary XML data to cast to string(I did it by using XML2CLOB)
to use the XML data as a string argument of DB2 functions like SUBSTR,
REPLACE, so on.

>
> I went over the syntax of Replace. replace function accepts only
> expressions as arguments in Db2 for LUW
> but Replace functions accepts only string aruguments in db2 for z/OS.
>
REPLACE on DB2 for LUW is almost same as REPLACE on DB2 for z/OS.

If you read the description of the function on SQL Reference of DB2
for LUW,
you can see:
The first argument can be of any built-in character string or binary
string type.
<snipped>
The type of the second and third arguments is identical to that of the
first argument.

> Does this anyway is effecting what I wanted to achieve.
>
> If yes, How can I convert an expression to a string argument.
As far as I know, there's no way to convert XML data to string on DB2
for z/OS by using bult-in functions.

On Nov 21, 9:21*pm, Tonkuma <tonk...@fiberbit.net> wrote:
> > Yeah, u are true, It was necessary to use a xml2clob in order to get
> > the result on Db2 9.1 for LUW.
>
> > But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
> > TECPG01.TEST1111 on Db2 9.1 for z/OS *I was able to get the same
> > result as in using XML2CLOB with above for Db2 LUW.
>
> I got same result by using Command Editor on DB2 LUW 9.1 and 9.5
> without using XML2CLOB function.
> But, the data type must be XML and I guessed that Command Editor
> converted the result into displayable format.
> Because, the statement "SELECT SUBSTR(XMLAGG(XMLELEMENT(NAME a, NAME)),
> 1,100) from Colors;", returned error message SQL0440N.
> It is neccesary XML data to cast to string(I did it by using XML2CLOB)
> to use the XML data as a string argument of DB2 functions like SUBSTR,
> REPLACE, so on.
>
>
>
> > I went over the syntax of Replace. replace function accepts only
> > expressions as arguments in Db2 for LUW
> > but Replace functions accepts only string aruguments in db2 for z/OS.
>
> REPLACE on DB2 for LUW is almost same as REPLACE on DB2 for z/OS.
>
> If you read the description of the function on SQL Reference of DB2
> for LUW,
> you can see:
> The first argument can be of any built-in character string or binary
> string type.
> <snipped>
> The type of the second and third arguments is identical to that of the
> first argument.
>
> > Does this anyway is effecting what I wanted to achieve.
>
> > If yes, How can I convert an expression to a string argument.
>
> As far as I know, there's no way to convert XML data to string on DB2
> for z/OS by using bult-in functions.

Hi Tonkuma,

Thanks so much for replying.

I am a DB2 AIX guy, so fairly new to z/os.

I used recursive SQL to produce what i wanted to.

Below is the SQL.



WITH temp1(NAME, all_words, cnt) as
(SELECT min(NAME), VARCHAR(min(NAME),2000),SMALLINT(1) FROM
TECPG01.TEST1111 a
UNION ALL
SELECT a.NAME, b.all_words||','||a.NAME,SMALLINT(b.cnt+1)
FROM TECPG01.TEST1111 a, temp1 b
WHERE a.NAME > b.NAME
AND a.NAME = (select min(c.NAME) from TECPG01.TEST1111 c where c.NAME
> b.NAME)
)
SELECT all_words FROM temp1 d
where cnt = (SELECT max(cnt) FROM temp1)

Now, I decided to write a function and ask users to call this function
to concatenate multiple rows to a string.

How do I write a function using Recursive SQL for this,

Does DB2 for z OS support Recursive SQL.

I would be very thankful to you if you get me this function for me to
get going.

Thanks in Advance again.

On Nov 24, 10:54*am, mark83anth...@gmail.com wrote:
> On Nov 21, 9:21*pm, Tonkuma <tonk...@fiberbit.net> wrote:
>
>
>
>
>
> > > Yeah, u are true, It was necessary to use a xml2clob in order to get
> > > the result on Db2 9.1 for LUW.
>
> > > But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
> > > TECPG01.TEST1111 on Db2 9.1 for z/OS *I was able to get the same
> > > result as in using XML2CLOB with above for Db2 LUW.
>
> > I got same result by using Command Editor on DB2 LUW 9.1 and 9.5
> > without using XML2CLOB function.
> > But, the data type must be XML and I guessed that Command Editor
> > converted the result into displayable format.
> > Because, the statement "SELECT SUBSTR(XMLAGG(XMLELEMENT(NAME a, NAME)),
> > 1,100) from Colors;", returned error message SQL0440N.
> > It is neccesary XML data to cast to string(I did it by using XML2CLOB)
> > to use the XML data as a string argument of DB2 functions like SUBSTR,
> > REPLACE, so on.
>
> > > I went over the syntax of Replace. replace function accepts only
> > > expressions as arguments in Db2 for LUW
> > > but Replace functions accepts only string aruguments in db2 for z/OS.
>
> > REPLACE on DB2 for LUW is almost same as REPLACE on DB2 for z/OS.
>
> > If you read the description of the function on SQL Reference of DB2
> > for LUW,
> > you can see:
> > The first argument can be of any built-in character string or binary
> > string type.
> > <snipped>
> > The type of the second and third arguments is identical to that of the
> > first argument.
>
> > > Does this anyway is effecting what I wanted to achieve.
>
> > > If yes, How can I convert an expression to a string argument.
>
> > As far as I know, there's no way to convert XML data to string on DB2
> > for z/OS by using bult-in functions.
>
> Hi Tonkuma,
>
> Thanks so much for replying.
>
> I am a DB2 AIX guy, so fairly new to z/os.
>
> I used recursive SQL to produce what i wanted to.
>
> Below is the SQL.
>
> WITH temp1(NAME, all_words, cnt) as
> (SELECT min(NAME), VARCHAR(min(NAME),2000),SMALLINT(1) FROM
> TECPG01.TEST1111 a
> UNION ALL
> SELECT a.NAME, b.all_words||','||a.NAME,SMALLINT(b.cnt+1)
> FROM TECPG01.TEST1111 a, temp1 b
> WHERE a.NAME > b.NAME
> AND a.NAME = (select min(c.NAME) from TECPG01.TEST1111 c where c.NAME> b.NAME)
>
> )
> SELECT all_words FROM temp1 d
> where cnt = (SELECT max(cnt) FROM temp1)
>
> Now, I decided to write a function and ask users to call this function
> to concatenate multiple rows to a string.
>
> How do I write a function using Recursive SQL for this,
>
> Does DB2 for z OS support Recursive SQL.
>
> I would be very thankful to you if you get me this function for me to
> get going.
>
> Thanks in Advance again.- Hide quoted text -
>
> - Show quoted text -

Or else,

Can i get an equivalent function/ stored procedure of the below
function in DB2 for z os.


CREATE FUNCTION get_str()
LANGUAGE SQL
RETURNS VARCHAR(1024)
BEGIN ATOMIC
DECLARE str VARCHAR(1024);
SET str = '';
loop1: FOR row AS (SELECT Name FROM TECPG01.TEST1111)
DO
IF row.Name IS NOT NULL THEN
SET str = str || row.Name || '';
END IF;
END FOR loop1;
RETURN str;
END@

On Thursday, November 20, 2008 12:58:48 AM UTC+5:30, (unknown) 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


CREATE TABLE COLOR
(
COLORS VARCHAR(MAX)
)



CREATE FUNCTION [UDF_COL_TO_SINGLE_ROW]
(
@VALUE AS VARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @colors NVARCHAR(50)

SELECT @colors = COALESCE(@colors + ',', '') + COLORS
FROM
COLOR

RETURN @colors
END

SELECT DBO.UDF_COL_TO_SINGLE_ROW ('DASD') AS COLOR_NAME


IT MUST WORKS.....ALL D BEST............

On Thursday, November 20, 2008 2:19:23 AM UTC+5:30, (unknown) 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





CREATE TABLE COLOR
(
COLORS VARCHAR(MAX)
)



CREATE FUNCTION [UDF_COL_TO_SINGLE_ROW]
(
@VALUE AS VARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @colors NVARCHAR(50)

SELECT @colors = COALESCE(@colors + ',', '') + COLORS
FROM
COLOR

RETURN @colors
END

SELECT DBO.UDF_COL_TO_SINGLE_ROW ('DASD') AS COLOR_NAME


IT MUST WORKS.....ALL D BEST............